分类统计

Question

我有一个表
ID(唯一) 用户ID   投票数   投票时间(格式'2008-09-22')的字符型 (用户ID,与投票时间唯一)
1         a       2      2008-09-22    第三(*)
2         b       3      2008-09-22    第二(**)
3         c       1      2008-09-22    没有星
4         d       9      2008-09-22    最大(***)
5         a       1      2008-09-23   (三名以后 0 颗星)
6         b       5      2008-09-23   (最大***)
7         c       4      2008-09-23   (第二**)
8         d       2      2008-09-23   (第三*)
我要的结果为用户 名次 总投票数 星数
b     1   8      5
d     2   11     4
c     3   5      2
a     4   3      1  现在就是第天每人得票最多的为3颗星,第二名为2颗星,第三名为1颗星,三名以后的为0颗星,
然后统按人分类统计出来,并按星数最多的降序,相同再按总投票数降序的结果,不保证我的结果统计完全正确.

Answer 1

SQL2005:
DECLARE @t TABLE(
ID INT,UserID varchar(10),Val INT,Dt DATETIME
)
INSERT INTO @t 
SELECT 
1      ,  'a'    ,  2  ,    '2008-09-22'    UNION ALL SELECT 
2      ,  'b'    ,  3  ,    '2008-09-22'  UNION ALL SELECT 
3      ,  'c'    ,  1  ,    '2008-09-22'  UNION ALL SELECT 
4      ,  'd'    ,  9  ,    '2008-09-22'  UNION ALL SELECT 
5      ,  'a'    ,  1  ,    '2008-09-23'  UNION ALL SELECT 
6      ,  'b'    ,  5  ,    '2008-09-23'  UNION ALL SELECT 
7      ,  'c'    ,  4  ,    '2008-09-23'  UNION ALL SELECT 
8      ,  'd'    ,  2  ,    '2008-09-23'
;
WITH CTE
AS
(
SELECT *,ROW=ROW_NUMBER() OVER (PARTITION BY Dt ORDER BY Val DESC)
FROM @t
),
CTF
AS
(
SELECT UserID,SUM(Val) as TotalV,SUM(CASE WHEN Row=1 THEN 3
WHEN Row=2 THEN 2
WHEN Row=3 THEN 1
ELSE 0
END) as R
FROM CTE
GROUP BY UserID
 )
SELECT UserID as 用户,ROW_NUMBER() OVER(ORDER BY R DESC) as 名次,
TotalV as 总投票数,
R as 星数
FROM CTF
/*
用户         名次                   总投票数        星数
---------- -------------------- ----------- -----------
b          1                    8           5
d          2                    11          4
c          3                    5           2
a          4                    3           1(4 行受影响)
*/

Answer 2

select 用户ID,名次=ROW_NUMBER() over(order by 星数,总投票数),总投票数,星数 from
(select 用户ID,总投票数=sum(投票数),星数=sum(星数) from
(select 用户ID,投票数,星数=(case rn when 1 then 3 when 2 then 2 when 3 then 1 else 0 end) from
(select 用户ID,投票数,rn=ROW_NUMBER() over(partition by 投票时间 order by 投票数) from tb) t) t1) t2

Answer 3

if object_id('tb') is not null
drop table tb
go
create table tb(id int,userid varchar(20),ballotnum int,ballottime varchar(10))
insert into tb select 1,'a',2,'2008-09-22'
insert into tb select 2,'b',3,'2008-09-22'
insert into tb select 3,'c',1,'2008-09-22'
insert into tb select 4,'d',9,'2008-09-22'
insert into tb select 5,'a',1,'2008-09-23'
insert into tb select 6,'b',5,'2008-09-23'
insert into tb select 7,'c',4,'2008-09-23'
insert into tb select 8,'d',2,'2008-09-23'    
select userid as 用户,row_number() over(order by sum(case when px=1 then 3 when px=2 then 2 when px=3 then 1 else 0 end) desc) as 名次,
sum(ballotnum) as 总投票数,
sum(case when px=1 then 3 when px=2 then 2 when px=3 then 1 else 0 end) as 星数 
from (select *,row_number() over(partition by ballottime order by ballotnum desc) as px
from tb)t
group by userid用户 名次 总投票数 星数
b 1 8 5
d 2 11 4
c 3 5 2
a 4 3 1

Answer 4

select 用户ID,名次=ROW_NUMBER() over(order by 星数 desc,总投票数 desc),总投票数,星数 from
(select 用户ID,总投票数=sum(投票数),星数=sum(星数) from
(select 用户ID,投票数,星数=(case rn when 1 then 3 when 2 then 2 when 3 then 1 else 0 end) from
(select 用户ID,投票数,rn=ROW_NUMBER() over(partition by 投票时间 order by 投票数 desc) from tb) t) t1) t2
order by 名次

Answer 5

DECLARE @t TABLE(
    ID INT,UserID varchar(10),Val INT,Dt DATETIME    
    )
INSERT INTO @t 
SELECT 
1      ,  'a'    ,  2  ,    '2008-09-22'    UNION ALL SELECT 
2      ,  'b'    ,  3  ,    '2008-09-22'  UNION ALL SELECT 
3      ,  'c'    ,  1  ,    '2008-09-22'  UNION ALL SELECT 
4      ,  'd'    ,  9  ,    '2008-09-22'  UNION ALL SELECT 
5      ,  'a'    ,  1  ,    '2008-09-23'  UNION ALL SELECT 
6      ,  'b'    ,  5  ,    '2008-09-23'  UNION ALL SELECT 
7      ,  'c'    ,  4  ,    '2008-09-23'  UNION ALL SELECT 
8      ,  'd'    ,  2  ,    '2008-09-23'SELECT a.UserID 用户,sv 总票数, ISNULL(b.si,0) 星数
FROM
(SELECT UserID,SUM(Val) sv
FROM @t
GROUP BY UserID
) a
LEFT JOIN
(SELECT UserID,SUM(idx) si FROM
(
SELECT *,idx = 4 - (DENSE_RANK() OVER (PARTITION BY Dt order by Val DESC)) FROM @t
)x  
WHERE idx>0
GROUP BY UserID
) b
ON a.userid= b.userid
ORDER BY 星数 DESC/*
用户         总票数         星数
---------- ----------- --------------------
b          8           5
d          11          4
c          5           2
a          3           1
*/

Answer 6

上面语句写复杂了，其实可以这样
DECLARE @t TABLE(
    ID INT,UserID varchar(10),Val INT,Dt DATETIME    
    )
INSERT INTO @t 
SELECT 
1      ,  'a'    ,  2  ,    '2008-09-22'    UNION ALL SELECT 
2      ,  'b'    ,  3  ,    '2008-09-22'  UNION ALL SELECT 
3      ,  'c'    ,  1  ,    '2008-09-22'  UNION ALL SELECT 
4      ,  'd'    ,  9  ,    '2008-09-22'  UNION ALL SELECT 
5      ,  'a'    ,  1  ,    '2008-09-23'  UNION ALL SELECT 
6      ,  'b'    ,  5  ,    '2008-09-23'  UNION ALL SELECT 
7      ,  'c'    ,  4  ,    '2008-09-23'  UNION ALL SELECT 
8      ,  'd'    ,  2  ,    '2008-09-23'SELECT UserID,SUM(Val) 总票数,SUM(CASE WHEN idx>0 THEN idx ELSE 0 END) 星数 FROM
(
SELECT *,idx = 4 - (DENSE_RANK() OVER (PARTITION BY Dt order by Val DESC)) FROM @t
)x  
GROUP BY UserID
ORDER BY 星数 DESC

Answer 7

--> --> (Roy)生成測試數據
 
set nocount on;
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Col1] int,[Col2] nvarchar(1),[Col3] int,[Col4] Datetime)
Insert #T
select 1,N'a',2,'2008-09-22' union all
select 2,N'b',3,'2008-09-22' union all
select 3,N'c',1,'2008-09-22' union all
select 4,N'd',9,'2008-09-22' union all
select 5,N'a',1,'2008-09-23' union all
select 6,N'b',5,'2008-09-23' union all
select 7,N'c',4,'2008-09-23' union all
select 8,N'd',2,'2008-09-23'
Go
with Cte
as
(
select 
[Col2],sum([Col3])总投票数,sum(3-con)星数
from 
(Select *,dense_rank()over(partition by [Col4] order by [Col3] desc)-1 as con from #T)t1
where con<=2 group by [Col2])
select 
[Col2],row_number()over(order by 星数 desc)名次,总投票数,星数
from 
Cte
Col2 名次                   总投票数        星数
---- -------------------- ----------- --------------------
b    1                    8           5
d    2                    11          4
c    3                    4           2
a    4                    2           1

Answer 8

开始我也加了where,后来去掉的。如果一个的记录里没一天能拿星星，加了where就没这个人记录了。

Answer 9

把并列处理为一条记录：--> --> (Roy)生成測試數據
 
set nocount on;
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Col1] int,[Col2] nvarchar(1),[Col3] int,[Col4] Datetime)
Insert #T
select 1,N'a',2,'2008-09-22' union all
select 2,N'b',3,'2008-09-22' union all
select 3,N'c',1,'2008-09-22' union all
select 4,N'd',9,'2008-09-22' union all
select 5,N'a',1,'2008-09-23' union all
select 6,N'b',5,'2008-09-23' union all
select 7,N'c',4,'2008-09-23' union all
select 8,N'd',2,'2008-09-23'
Go
with Cte
as
(
select 
[Col2],sum([Col3])总投票数,sum(3-con)星数
from 
(Select *,dense_rank()over(partition by [Col4] order by [Col3] desc)-1 as con from #T)t1
where con<=2 group by [Col2])
select 
[Col2],dense_rank()over(order by 星数 desc)名次,总投票数,星数
from 
Cte--2000方法，最好用临时表处理
select 
a.[Col2] 用户,count(distinct b.con)名次,a.[Col3] 总投票数,a.con  星数
from 
(select  [Col2],sum([Col3])[Col3],sum(3-con)con
from (Select *,(select count(distinct [Col3]) from #T where [Col4]=a.[Col4] and [Col3]>a.[Col3]) as con from #T a)t1
where con<=2 group by [Col2])a
cross join
(select  [Col2],sum([Col3])[Col3],sum(3-con)con
from (Select *,(select count(distinct [Col3]) from #T where [Col4]=a.[Col4] and [Col3]>a.[Col3]) as con from #T a)t1
where con<=2 group by [Col2])b
where
a.con<=b.con
group by a.[Col2],a.[Col3],a.con
order by 名次
Col2 名次                   总投票数        星数
---- -------------------- ----------- --------------------
b    1                    8           5
d    2                    11          4
c    3                    4           2
a    4                    2           1用户   名次          总投票数        星数
---- ----------- ----------- -----------
b    1           8           5
d    2           11          4
c    3           4           2
a    4           2           1

Answer 10

create table tb(ID INT,UserID varchar(10),Val INT,Dt DATETIME)
INSERT INTO tb SELECT 
1 , 'a' , 2 , '2008-09-22' UNION ALL SELECT 
2 , 'b' , 3 , '2008-09-22' UNION ALL SELECT 
3 , 'c' , 1 , '2008-09-22' UNION ALL SELECT 
4 , 'd' , 9 , '2008-09-22' UNION ALL SELECT 
5 , 'a' , 1 , '2008-09-23' UNION ALL SELECT 
6 , 'b' , 5 , '2008-09-23' UNION ALL SELECT 
7 , 'c' , 4 , '2008-09-23' UNION ALL SELECT 
8 , 'd' , 2 , '2008-09-23'select t1.userid , mc = (select count(1) from
(
  select userid , sum(val) val , sum(xs) xs from
  (select * , case px when 1 then 3 when 2 then 2 when 3 then 1 else 0 end xs from
  (select * , px = (select count(1) from tb where dt = t.dt and val > t.val) + 1 from tb t) m) n
  group by userid
) t2 where xs > t1.xs 
) + 1 , val , xs
from 
(
  select userid , sum(val) val , sum(xs) xs from
  (select * , case px when 1 then 3 when 2 then 2 when 3 then 1 else 0 end xs from
  (select * , px = (select count(1) from tb where dt = t.dt and val > t.val) + 1 from tb t) m) n
  group by userid
) t1order by xs descdrop table tb/*
userid     mc          val         xs          
---------- ----------- ----------- ----------- 
b          1           8           5
d          2           11          4
c          3           5           2
a          4           3           1（所影响的行数为 4 行）
*/

Answer 11

我想补充一个问题,怎么2005里面的LEFT JOIN 和inner join一样了啊.左边有用条记录,右边只有一条记录,最后用left join 是一条记录

Answer 12

--> --> (Andy)生成测试数据 2008-09-23
Set Nocount On
declare @1 table([ID] int,[用户ID] nvarchar(1),[投票数] int,[投票时间] Datetime)
Insert @1
select 1,N'a',2,'2008-09-22' union all
select 2,N'b',3,'2008-09-22' union all
select 3,N'c',1,'2008-09-22' union all
select 4,N'd',9,'2008-09-22' union all
select 5,N'a',1,'2008-09-23' union all
select 6,N'b',5,'2008-09-23' union all
select 7,N'c',4,'2008-09-23' union all
select 8,N'd',2,'2008-09-23';With T As
(Select [用户ID],[投票数],[星数]=
Case Row_number() Over(Partition By [投票时间] Order By [投票数] Desc)  
When 1 Then 3 
When 2 Then 2 
When 3 Then 1 
Else 0 
End
From @1
)
Select [用户ID],[名次]=Row_number() Over(Order By Sum([星数]) Desc),总投票数=Sum([投票数]),[星数]=Sum([星数])
From t 
Group By [用户ID]/*
用户ID 名次         总投票数        星数
---- -------------------- ----------- -----------
b    1                    8           5
d    2                    11          4
c    3                    5           2
a    4                    3           1*/

Answer 13

我想补充一个问题,怎么2005里面的LEFT JOIN 和inner join一样了啊.左边有用条记录,右边只有三条记录,最后用left join 是一条记录

Answer 14

是我搞错了,不好意思,LEFT JOIN是好的