日期:2014-05-18 浏览次数:20532 次
(select top 3 * from 表A a inner join 表B b on a.id=b.aid where a.cid in (10000) order by b.id desc) union all (select top 3 * from 表A a inner join 表B b on a.id=b.aid where a.cid in (10001) order by b.id desc) union all (select top 3 * from 表A a inner join 表B b on a.id=b.aid where a.cid in (10002) order by b.id desc)
------解决方案--------------------
--得到每组前几条数据 --假設每組Col1中, Col3不會重復 --建立測試環境 Create Table TEST (Col1 Varchar(10), Col2 Varchar(10), Col3 Int) --插入數據 Insert TEST Select 'BD1V','Label', 4 Union All Select 'BD1V', 'BATT', 2 Union All Select 'BD1V', 'ODD', 3 Union All Select 'BD1V', 'HDD', 5 Union All Select 'BD1V', 'LCD', 1 Union All Select 'BD1W','HDD', 3 Union All Select 'BD1W','RAM', 8 Union All Select 'BD1W','TP CABLE', 5 Union All Select 'BD1W','LCD', 6 Union All Select 'BD1W','Label', 2 Union All Select 'BL3', 'LCD CABLE', 7 Union All Select 'BL3', 'LABEL', 6 Union All Select 'BL3', 'LCD', 5 Union All Select 'BL3', 'RAM', 1 Union All Select 'BL3D', 'Label', 4 GO --測試 --方法一: Select Col1, Col2, Col3 From TEST A Where (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3) < 3 Order By Col1, Col3 Desc --方法二: Select Col1, Col2, Col3 From TEST A Where Exists (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3 Having Count(*) < 3) Order By Col1, Col3 Desc --方法三: Select Col1, Col2, Col3 From TEST A Where Col3 In (Select TOP 3 Col3 From TEST Where Col1 = A.Col1 Order By Col3 Desc) Order By Col1, Col3 Desc GO --刪除測試環境 Drop Table TEST --結果 /* Col1 Col2 Col3 BD1V HDD 5 BD1V Label 4 BD1V ODD 3 BD1W RAM 8 BD1W LCD 6 BD1W TP CABLE 5 BL3 LCD CABLE 7 BL3 LABEL 6 BL3 LCD 5 BL3D Label 4 */
------解决方案--------------------
--如果是查询: select a.* , t.* from a , b t where a.id = t.aid and t.id in (select top 3 id from b where aid = t.aid order by id) select a.* , t.* from a , b t where a.id = t.aid and t.id in (select top 3 id from b where aid = t.aid order by id desc)