表结果为:
id smallclassid smallclassname thumbgallery isindex hit
1 343 测试 xxx 1 100
2 343 测试 xxx 0 200
3 200 test xxx 1 300
4 200 test xxx 0 300 要求计算出hit总和排在前6位的分类值(也就是必须按照smallclassid分),并按照hit总和的降序排列且isidnex的值为1,页面中需要用到smallclasid,smallclassname,isindex,thumbgallery这四个字段
id smallclassid smallclassname thumbgallery isindex hit
1 343 测试 xxx 1 100
2 343 测试 xxx 0 200
3 200 test xxx 1 300
4 200 test xxx 0 300 要求计算出hit总和排在前6位的分类值(也就是必须按照smallclassid分),并按照hit总和的降序排列且isidnex的值为1,页面中需要用到smallclasid,smallclassname,isindex,thumbgallery这四个字段
from ta
where isindex = 1
group by smallclassid ,smallclassname,isindex,thumbgallery
order by sum(hit) desc,isindex
isindex int, hit int)
insert ta select
1 , 343 , '测试' , 'xxx', 1, 100 union select
2, 343 , '测试' , 'xxx' , 0 , 200 union select
3 , 200 , 'test' , 'xxx' , 1 , 300 union select
4 , 200 , 'test', 'xxx' , 0 , 300
go
select top 6 smallclassid,smallclassname,isindex,thumbgallery
from ta
where isindex = 1
group by smallclassid,smallclassname,isindex,thumbgallery
order by sum(hit) desc,isindexdrop table ta/*
smallclassid smallclassname isindex thumbgallery
------------ -------------- ----------- --------------------
200 test 1 xxx
343 测试 1 xxx(所影响的行数为 2 行)
*/
INSERT @TB
SELECT 1 , 343 , '测试', 'xxx', 1 , 100 UNION ALL
SELECT 2 , 343 , '测试', 'xxx', 1 , 700 UNION ALL
SELECT 3 , 343 , '测试', 'xxx', 1 , 100 UNION ALL
SELECT 4 , 343 , '测试', 'xxx', 0 , 200 UNION ALL
SELECT 5 , 200 , 'test', 'xxx', 1 , 300 UNION ALL
SELECT 6 , 200 , 'test', 'xxx', 0 , 300 UNION ALL
SELECT 7 , 200 , 'test', 'xxx', 1 , 500 UNION ALL
SELECT 8 , 200 , 'test', 'xxx', 1 , 300
--SELECT * FROM @TBSELECT TOP 6 A.smallclassid,A.smallclassname,A.thumbgallery,A.isindex,SUM(HIT) HIT
FROM @TB A WHERE isindex=1
GROUP BY A.smallclassid,A.smallclassname,A.thumbgallery,A.isindex
ORDER BY SUM(HIT) DESC--结果/*
smallclassid smallclassname thumbgallery isindex hit
------------------------------------------------------------------
200 test xxx 1 1100
343 测试 xxx 1 900
*/
isindex int, hit int)
insert ta select
1 , 343 , '测试' , 'xxx', 1, 100 union select
2, 343 , '测试' , 'xxx' , 0 , 200 union select
3 , 200 , 'test' , 'xxx' , 1 , 300 union select
4 , 200 , 'test', 'xxx' , 0 , 300
go
select top 6 smallclassid,smallclassname,isindex,thumbgallery,sum(hit) as hits
from ta
where isindex = 1
group by smallclassid,smallclassname,isindex,thumbgallery
order by sum(hit) desc,isindexdrop table ta/*
smallclassid smallclassname isindex thumbgallery hits
------------ -------------- ----------- -------------------- -----------
200 test 1 xxx 300
343 测试 1 xxx 100(所影响的行数为 2 行)
*/
就是说每一个类里只有一行的IsIndex的值为1,其他均为0
------------ -------------- ----------- -------------------- -----------
200 test 1 xxx 300
343 测试 1 xxx 600
isindex int, hit int)
insert ta select
1 , 343 , '测试' , 'xxx', 1, 100 union select
2, 343 , '测试' , 'xxx' , 0 , 200 union select
3 , 200 , 'test' , 'xxx' , 1 , 300 union select
4 , 200 , 'test', 'xxx' , 0 , 300
goselect a.smallclassid,smallclassname,isindex,thumbgallery,hits
from ta a,(
select smallclassid ,sum(hit) as hits
from ta
group by smallclassid ) b
where a.smallclassid = b.smallclassid
and isindex = 1
order by hits desc
drop table ta/*(所影响的行数为 4 行)smallclassid smallclassname isindex thumbgallery hits
------------ -------------- ----------- -------------------- -----------
200 test 1 xxx 600
343 测试 1 xxx 300(所影响的行数为 2 行)
*/
恩,手误了一下..谢谢了..