|
Тема |
sybase (and ms sql) solution [re: Topбaлaн] |
|
Автор | baj L\bo (Нерегистриран) | |
Публикувано | 07.02.03 19:57 |
|
|
Абе така като гледам искаш да класираш снимките. Долния пример го тествах на сайбейз и трябва да работи и на мс сял сервер (с незначителни промени).
За май сял дано поне идеята ти свърши работа:
create table #votes (
user_id int
, first char(10)
, second char (10)
, third char (10)
)
go
insert into #votes values (1,'a','b','c')
insert into #votes values (1,'b','c','a')
insert into #votes values (1,'b','c','d')
insert into #votes values (1,'e','d','c')
go
select * from #votes
go
-- naprawi niakak si spisak na snimkite
-- az sha go napravia "na raka"
create table #pictures (name char(10))
go
insert into #pictures values ('a')
insert into #pictures values ('b')
insert into #pictures values ('c')
insert into #pictures values ('d')
insert into #pictures values ('e')
-- f i g da rechem ne sa poluchili nikakwi glasove
insert into #pictures values ('f')
insert into #pictures values ('g')
select * from #pictures
go
-- sega procto broime za wsqka snimka kolko piti e bila
-- pyrwa, wtora i treta
-- Zabeleji she stawa s CASE operatora. Ako nqmash CASE v mysql,
-- vij "characteristic functions", koeto e ekvivalentno, no
-- adski nechitaemo
select
name
, first =sum (case v.first when p.name then 1 else 0 end )
, second=sum (case v.second when p.name then 1 else 0 end )
, third =sum (case v.third when p.name then 1 else 0 end )
from #votes v, #pictures p
group by p.name
go
-- ako reshish da davash da rechem po
-- 3 tochki za 1-vo miasto,
-- 2 tochki za 1-vo miasto i
-- 1 tochki za 1-vo miasto
--i da smiatah total, poslednia select stava:
select
name
, first =sum (case v.first when p.name then 1 else 0 end )
, second=sum (case v.second when p.name then 1 else 0 end )
, third =sum (case v.third when p.name then 1 else 0 end )
, total =sum (case v.first when p.name then 3 else 0 end )
+sum (case v.second when p.name then 2 else 0 end )
+sum (case v.third when p.name then 1 else 0 end )
from #votes v, #pictures p
group by p.name order by total desc
go
и това като се изпълни дава:
----------- ---------- ---------- ----------
1 a b c
1 b c a
1 b c d
1 e d c
name
----------
a
b
c
d
e
f
g
(7 rows affected)
name first second third
---------- ----------- ----------- -----------
a 1 0 1
b 2 1 0
c 0 2 2
d 0 1 1
e 1 0 0
f 0 0 0
g 0 0 0
(7 rows affected)
name first second third total
---------- ----------- ----------- ----------- -----------
b 2 1 0 8
c 0 2 2 6
a 1 0 1 4
d 0 1 1 3
e 1 0 0 3
f 0 0 0 0
g 0 0 0 0
|
| |
|
|
|