统计排行榜的有关问题

select giftname,userid,sum(giftcount) as giftcountfrom table1 group by giftname,useridorder by sum(giftcount) desc,min(addtime)
------解决方案--------------------
SQL code
--tryselect giftname,userid,giftcount from tb a where not exists(select 1 from tb where giftname=a.giftname and giftcount>a.giftcount or (giftcount=a.giftcount and addtime<a.addtime))
------解决方案--------------------
你确定吗？确定吗？确定吗？确定吗？确定吗？确定吗？确定吗？
确定吗？确定吗？确定吗？确...定...吗...？
探讨

引用:

SQL code

--try
select giftname,userid,giftcount from tb a where not exists(
select 1 from tb where giftname=a.giftname and giftcount>a.giftcount or (giftcount=a.giftcount……

------解决方案--------------------
SQL code
select giftname,userid,giftcount from tb t where not exists(select 1 from tb where giftname=t.giftname and giftcount>t.giftcount or(giftcount=t.giftcount and addtime<t.addtime))
------解决方案--------------------
没道理啊！你放10行或者20行数据上来，复制上来，别上图
探讨

引用:

你确定吗？确定吗？确定吗？确定吗？确定吗？确定吗？确定吗？
确定吗？确定吗？确定吗？确...定...吗...？引用:
引用:

SQL code

--try
select giftname,userid,giftcount from tb a where not e……

------解决方案--------------------
探讨


引用:

SQL code

select giftname,userid,sum(giftcount) as giftcount
from table1
group by giftname,userid
order by sum(giftcount) desc,min(addtime)


统计出来的礼物有重复

------解决方案--------------------
這樣?

SQL code
use Tempdbgo--> -->  if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([ID] int,[giftname] nvarchar(3),[userid] int,[giftcount] int,[addtime] Datetime,[sendid] nvarchar(5),[price] int,[price2] nvarchar(8),[aaa] nvarchar(9))Insert #Tselect 68,N'香水',3,1,'2011-10-19 16:25:05.483',N'1 100',100,null,null union allselect 69,N'香水',3,1,'2011-10-19 16:25:42.547',N'1 100',100,null,null union allselect 70,N'金条',2,1,'2011-10-21 10:01:29.733',N'1 10',10,N'car',null union allselect 71,N'雪茄',2,1,'2011-10-21 10:01:48.390',N'1 100',100,null,null union allselect 72,N'飞机',1,1,'2011-10-21 10:10:08.110',N'2',100,N'100',null union allselect 73,N'猪头',1,1,'2011-10-21 10:11:11.093',N'2',100,N'100',N'washboard' union allselect 74,N'巧克力',1,1,'2011-10-21 10:11:53.437',N'1 10',10,null,null union allselect 75,N'口红',2,1,'2011-10-21 10:14:39.813',N'1 100',100,N'yacht',null union allselect 76,N'棒棒粮',2,1,'2011-10-21 10:15:22.627',N'1 10',10,N'rose',null union allselect 77,N'奥迪',4,1,'2011-10-21 10:16:38.797',N'1 100',100,null,null union allselect 78,N'航母',4,1,'2011-10-21 10:16:51.030',N'1 100',100,null,null union allselect 79,N'爱你',4,1,'2011-10-21 10:17:17.767',N'1 10',10,N'lollipop',null union allselect 80,N'XO',5,1,'2011-10-21 10:18:06.360',N'1 100',100,N'perfume',null union allselect 81,N'谢谢',5,1,'2011-10-21 10:19:44.407',N'2',100,N'100',N'chocolate' union allselect 82,N'飞机',2,1,'2011-10-21 10:20:18.187',N'2',100,N'100',null union allselect 83,N'包',2,1,'2011-10-21 10:21:07.110',N'3',100,N'100',N'xo' union allselect 84,N'砖',5,1,'2011-10-21 10:21:27.187',N'3',100,N'100',N'yiqunrenC' union allselect 85,N'鼓掌',5,1,'2011-10-21 10:21:45.313',N'3',10,N'10',N'cake' union allselect 86,N'爱你',2,1,'2011-10-21 10:22:12.327',N'3',10,N'10',N'lollipop' union allselect 87,N'飞吻',5,1,'2011-10-21 10:22:14.360',N'3',10,N'10',N'zhuantou' union allselect 88,N'爱你',5,1,'2011-10-21 10:22:37.047',N'3',10,N'10',N'lollipop' union allselect 89,N'飞吻',2,1,'2011-10-21 10:22:50.673',N'3',10,N'10',N'zhuantou' union allselect 90,N'航母',9,1,'2011-10-21 10:28:25.593',N'3',100,N'100',null union allselect 91,N'奥迪',9,1,'2011-10-21 10:28:35.280',N'3',100,N'100',null union allselect 92,N'猪头',9,1,'2011-10-21 10:28:46.687',N'3',100,N'100',N'washboard' union allselect 93,N'包',9,1,'2011-10-21 10:28:53.203',N'3',100,N'100',N'xo' union allselect 94,N'巧克力',9,1,'2011-10-21 10:29:00.657',N'3',10,N'10',null union allselect 95,N'雪茄',9,1,'2011-10-21 10:29:11.327',N'3',100,N'100',null union allselect 96,N'航母',6,1,'2011-10-21 10:30:01.530',N'3',100,N'100',null union allselect 97,N'棒棒粮',6,1,'2011-10-21 10:30:16.593',N'3',10,N'10',N'rose' union allselect 98,N'香水',9,1,'2011-10-21 10:30:42.280',N'3',100,N'100',null union allselect 99,N'猪头',9,1,'2011-10-21 10:31:59.673',N'3',100,N'100',N'washboard'GoSELECT giftname,userid,giftcount FROM (select giftname, userid, sum(giftcount) as giftcount, min(addtime) AS addtime, row=ROW_NUMBER()OVER(partition BY giftname ORDER BY sum(giftcount) DESC, min(addtime)) from #T group by giftname,userid )T WHERE row=1 ORDER BY giftcount DESC/*giftname    userid    giftcount香水    3    2猪头    9    2砖    5    1谢谢    5    1飞机    1    1飞吻    5    1航母    4    1雪茄    2    1棒棒粮    2    1鼓掌    5    1奥迪    4    1爱你    4    1XO    5    1口红    2    1包    2    1巧克力    1    1金条    2    1*/