TOP 查询(200)

一张表,用SELECT DM1, SPDM, SUM(JE) AS JEFROM VW_LSXHMX WHERE (RQ > '2009-04-23')GROUP BY DM1, SPDM, RQORDER BY DM1, SUM(JE) DESC查询得到下列数据:商店 货品 金额10 9c22007 34310 9n11017 29410 9q22007 20410 9T82001 20310 9x11002 19610 9t22016 17710 9c12003 17710 9t02003 14710 9t02004 14710 9T72001 14511 9n11001 29411 9n11011 29411 9t12019 20412 9T22030 27212 9X11002 19612 9X11005 19612 9n11017 19612 8T73002 17414 9n11011 29414 9C12002 27314 9t72002 26614 9N11020 19614 9T12017 19614 9T72001 17414 9C22007 14714 9C12007 13614 9t12019 13614 9T22804 13314 9T22006 117如何才能得到下面的数据:商店 货品 金额 商店 货品 金额 商店 货品 金额 商店 货品 金额10 9c22007 343 11 9n11001 294 12 9T22030 272 14 9n11011 29410 9n11017 294 11 9n11011 294 12 9X11002 196 14 9C12002 27310 9q22007 204 11 9t12019 204 12 9X11005 196 14 9t72002 26610 9T82001 203 12 9n11017 196 14 9N11020 19610 9x11002 196 12 8T73002 174 14 9T12017 196每个商店商品金额的前5名,横向排列显示分不够可加,恳请高手帮忙.

sql server数据库？用存储过程去实现吧

是 sql server 数据库 楼上 能否提示一下?

直接写查询语句是查不出来的，或者说是比较难查出来。这个应该会用到中间表，写个存储过程吧！存储过程就很容易实现了！

麻烦楼上写一个? 分不够可加分

最难应该是横向排列,请高手帮忙呀

case应该能弄出来吧，没有仔细想。

case 不能同行

在查询出下面格式数据的基础上：查询得到下列数据M1 SPDM JE商店 货品 金额10 9c22007 34310 9n11017 29410 9q22007 20410 9T82001 20310 9x11002 19610 9t22016 17710 9c12003 17710 9t02003 14710 9t02004 14710 9T72001 14511 9n11001 29411 9n11011 29411 9t12019 20412 9T22030 272......................接下来就可以一句sql搞定~测试已通过select * from (select * ,(select count(*) from (select top 5 * from test where dm1=10 order by je desc) a where a.je>=e.je) as rowid from (select top 5 * from test where dm1=10 order by je desc) e ) bfull outer join (select * ,(select count(*) from (select top 5 * from test where dm1=11 order by je desc) a where a.je>=e.je) as rowid from (select top 5 * from test where dm1=11 order by je desc) e ) c on b.rowid=c.rowidfull outer join (select * ,(select count(*) from (select top 5 * from test where dm1=12 order by je desc) a where a.je>=e.je) as rowid from (select top 5 * from test where dm1=12 order by je desc) e ) d on c.rowid=d.rowidfull outer join (select * ,(select count(*) from (select top 5 * from test where dm1=14 order by je desc) a where a.je>=e.je) as rowid from (select top 5 * from test where dm1=14 order by je desc) e ) f on d.rowid=f.rowid

SELECT * FROM #temptablegoselect * from (select * ,(select count(*) from (select top 5 * from #temptable where dm1=10 order by je desc) a where a.je>=e.je) as rowid from (select top 5 * from #temptable where dm1=10 order by je desc) e ) bfull outer join (select * ,(select count(*) from (select top 5 * from #temptable where dm1=11 order by je desc) a where a.je>=e.je) as rowid from (select top 5 * from #temptable where dm1=11 order by je desc) e ) c on b.rowid=c.rowidfull outer join (select * ,(select count(*) from (select top 5 * from #temptable where dm1=12 order by je desc) a where a.je>=e.je) as rowid from (select top 5 * from #temptable where dm1=12 order by je desc) e ) d on c.rowid=d.rowidfull outer join (select * ,(select count(*) from (select top 5 * from #temptable where dm1=14 order by je desc) a where a.je>=e.je) as rowid from (select top 5 * from #temptable where dm1=14 order by je desc) e ) f on d.rowid=f.rowid go应该不行,我将需要的数据入到临时表,用上面二个语句进行查询,第一个有数据,第二个语句没有数据!

我那段是一整个sql语句啊，你可以不用临时表的把这句：SELECT DM1, SPDM, SUM(JE) AS JEFROM VW_LSXHMX WHERE (RQ > '2009-04-23')GROUP BY DM1, SPDM, RQORDER BY DM1, SUM(JE) DESC当成一个表来使用就避免使用临时表了

10 9n11026 640 1 11 9C52002 612 1 12 9n11017 294 1 14 9T72001 348 110 9t02003 392 5 11 9c22007 392 5 12 8n16006 196 5 14 9C12007 204 510 9t02003 392 5 11 9t12017 392 5 12 8n16006 196 5 14 9C12007 204 510 9t02003 392 5 11 9Q22016 392 5 12 8n16006 196 5 14 9C12007 204 510 9t02003 392 5 11 9N11011 392 5 12 8n16006 196 5 14 9C12007 204 510 9T72001 435 4 10 9c22007 490 3 10 9T82001 580 2 12 8T83001 203 4 14 9T82001 261 4 12 9T72002 266 3 14 9n11011 294 3 12 8T73002 290 2 14 9T72002 342 2排名还是有问题,不是最高到底,还有,怎么有的列中间有空行?

排序不是问题，按rowid排一下就是了，空行你要好好看看了，11商店的记录取出前5个的话应该有5条记录的，不知你这个查出来的怎么没有2、3、4的。重复的数据是因为11商店金额为392的不止一种商品，用这个连接查询的话就会显示成那个样子...再仔细看看并调试下吧,基本思路就是查出降序排列取出每个商店的前五条记录并配上行号，用连接查询实现你要求的那种效果

谢谢大家,放分!