select * from ta a where id in (select top 100 id from ta where datediff(d,a.rq,rq) = 0) order by rq)
select * from ta a where id in (select top 100 id from ta where datediff(d,a.rq,rq) = 0) order by 销售额 desc )
select top 100 * from tbl order by 销售额
楼上的不是这意思,我是想查出每一天都有销售前2位的数据。 也就是说 2012-01-01 日 有2条 2012-01-02日 有2条。 马上给出测试数据: 表#tab:CREATE TABLE #tab(ID VARCHAR(3),rq VARCHAR(10),goods VARCHAR(10),shl dec(5,2),xshe dec(5,2),danjia dec(5,2)) INSERT INTO #tab SELECT '001','2012-01-01','AAA',5,100,20 UNION ALL SELECT '002','2012-01-01','BBB',3,45,15 UNION ALL SELECT '003','2012-01-01','CCC',4,100,25 UNION ALL SELECT '004','2012-01-02','DDD',6,30,5 UNION ALL SELECT '005','2012-01-02','AAA',1,28,28 UNION ALL SELECT '006','2012-01-02','AAA',3,66,22 UNION ALL SELECT '007','2012-01-03','AAA',6,90,15 UNION ALL SELECT '008','2012-01-03','DDD',8,24,3 UNION ALL SELECT '009','2012-01-03','AAA',5,40,8 UNION ALL SELECT '010','2012-01-04','EEE',4,40,10 UNION ALL SELECT '011','2012-01-04','AAA',6,30,5 UNION ALL SELECT '012','2012-01-04','DDD',6,90,15 --select * from #tab
SELECT * FROM #Tab AS T WHERE ID IN(SELECT TOP 2 ID FROM #Tab WHERE rq=T.rq ORDER BY xshe DESC) 有错么?
select * from #tab a where checksum(*) in (select top 2 checksum(*) from #tab where rq=a.rq order by rq)
select top 100 * from table1 order by 销售额
CREATE TABLE #tab(ID VARCHAR(3),rq VARCHAR(10),goods VARCHAR(10),shl dec(5,2),xshe dec(5,2),danjia dec(5,2)) INSERT INTO #tab SELECT '001','2012-01-01','AAA',5,100,20 UNION ALL SELECT '002','2012-01-01','BBB',3,45,15 UNION ALL SELECT '003','2012-01-01','CCC',4,100,25 UNION ALL SELECT '004','2012-01-02','DDD',6,30,5 UNION ALL SELECT '005','2012-01-02','AAA',1,28,28 UNION ALL SELECT '006','2012-01-02','AAA',3,66,22 UNION ALL SELECT '007','2012-01-03','AAA',6,90,15 UNION ALL SELECT '008','2012-01-03','DDD',8,24,3 UNION ALL SELECT '009','2012-01-03','AAA',5,40,8 UNION ALL SELECT '010','2012-01-04','EEE',4,40,10 UNION ALL SELECT '011','2012-01-04','AAA',6,30,5 UNION ALL SELECT '012','2012-01-04','DDD',6,90,15 select * from #tab a where id in (select top 2 id from #tab where datediff(d,a.rq,rq) = 0 order by xshe desc )drop table #tab ID rq goods shl xshe danjia ---- ---------- ---------- ------- ------- ------- 001 2012-01-01 AAA 5.00 100.00 20.00 003 2012-01-01 CCC 4.00 100.00 25.00 004 2012-01-02 DDD 6.00 30.00 5.00 006 2012-01-02 AAA 3.00 66.00 22.00 007 2012-01-03 AAA 6.00 90.00 15.00 009 2012-01-03 AAA 5.00 40.00 8.00 010 2012-01-04 EEE 4.00 40.00 10.00 012 2012-01-04 DDD 6.00 90.00 15.00(所影响的行数为 8 行)
from ta a
where id in (select top 100 id from ta where datediff(d,a.rq,rq) = 0) order by rq)
from ta a
where id in (select top 100 id from ta where datediff(d,a.rq,rq) = 0) order by 销售额 desc )
也就是说 2012-01-01 日 有2条
2012-01-02日 有2条。
马上给出测试数据:
表#tab:CREATE TABLE #tab(ID VARCHAR(3),rq VARCHAR(10),goods VARCHAR(10),shl dec(5,2),xshe dec(5,2),danjia dec(5,2))
INSERT INTO #tab
SELECT '001','2012-01-01','AAA',5,100,20 UNION ALL
SELECT '002','2012-01-01','BBB',3,45,15 UNION ALL
SELECT '003','2012-01-01','CCC',4,100,25 UNION ALL
SELECT '004','2012-01-02','DDD',6,30,5 UNION ALL
SELECT '005','2012-01-02','AAA',1,28,28 UNION ALL
SELECT '006','2012-01-02','AAA',3,66,22 UNION ALL
SELECT '007','2012-01-03','AAA',6,90,15 UNION ALL
SELECT '008','2012-01-03','DDD',8,24,3 UNION ALL
SELECT '009','2012-01-03','AAA',5,40,8 UNION ALL
SELECT '010','2012-01-04','EEE',4,40,10 UNION ALL
SELECT '011','2012-01-04','AAA',6,30,5 UNION ALL
SELECT '012','2012-01-04','DDD',6,90,15
--select * from #tab
有错么?
INSERT INTO #tab
SELECT '001','2012-01-01','AAA',5,100,20 UNION ALL
SELECT '002','2012-01-01','BBB',3,45,15 UNION ALL
SELECT '003','2012-01-01','CCC',4,100,25 UNION ALL
SELECT '004','2012-01-02','DDD',6,30,5 UNION ALL
SELECT '005','2012-01-02','AAA',1,28,28 UNION ALL
SELECT '006','2012-01-02','AAA',3,66,22 UNION ALL
SELECT '007','2012-01-03','AAA',6,90,15 UNION ALL
SELECT '008','2012-01-03','DDD',8,24,3 UNION ALL
SELECT '009','2012-01-03','AAA',5,40,8 UNION ALL
SELECT '010','2012-01-04','EEE',4,40,10 UNION ALL
SELECT '011','2012-01-04','AAA',6,30,5 UNION ALL
SELECT '012','2012-01-04','DDD',6,90,15 select *
from #tab a
where id in (select top 2 id from #tab where datediff(d,a.rq,rq) = 0 order by xshe desc )drop table #tab
ID rq goods shl xshe danjia
---- ---------- ---------- ------- ------- -------
001 2012-01-01 AAA 5.00 100.00 20.00
003 2012-01-01 CCC 4.00 100.00 25.00
004 2012-01-02 DDD 6.00 30.00 5.00
006 2012-01-02 AAA 3.00 66.00 22.00
007 2012-01-03 AAA 6.00 90.00 15.00
009 2012-01-03 AAA 5.00 40.00 8.00
010 2012-01-04 EEE 4.00 40.00 10.00
012 2012-01-04 DDD 6.00 90.00 15.00(所影响的行数为 8 行)