SQL 查询指定记录条数

[ymgxc049]

请各位大侠帮忙了
现有表A,存在记录条数若干,Column1中保存的是类型,Column2保存的是其它一些内容


Column1 Column2
110000
110000
110000
120000
120000
130000
130000
.
.
.


现在需要每种类型取3条记录,用一条SQL语句如何写

返回内容

Column1 Column2
110000
110000
110000
120000
120000
120000
130000
130000
130000
...

[昵称被占用了]

一般表应该有主键

select Column1,Column2   
from tab a
where keycolumn in (
  select top 3 keycolumn
  from tab
  where Column1 = a.Column1
  order by keycolumn
  )

[GoAwayZ]

select * from 
(select row_number() over(partition by Column1 order by getdate()) as num,* from tb) a 
where num<4

[lqqgzg]

楼上的这样写实现不了 ，建议分开来写...多次操作

[叶子]

declare @table table (Column1 int,Column2 INT)
insert into @table
select 110000,null union all
select 110000,null union all
select 110000,null union all
select 120000,null union all
select 120000,null union all
select 130000,null union all
select 130000,null
/*
Column1     Column2
----------- -----------
110000      NULL
110000      NULL
110000      NULL
120000      NULL
120000      NULL
130000      NULL
130000      NULL
*/
SELECT * FROM (
SELECT Column1 FROM @table GROUP BY Column1 UNION ALL
SELECT Column1 FROM @table GROUP BY Column1 UNION ALL
SELECT Column1 FROM @table GROUP BY Column1
)a ORDER BY a.Column1
/*
Column1
-----------
110000
110000
110000
120000
120000
120000
130000
130000
130000
*/

[Shawn]

不足3条记录的type也要取3条吗？下面的语句只是每个type取前3条:

CREATE TABLE #temp
(
	[type] VARCHAR(100),
	other_field INT
)
INSERT #temp
SELECT '110000', 1 UNION ALL
SELECT '110000', 2 UNION ALL
SELECT '110000', 3 UNION ALL
SELECT '120000', 4 UNION ALL
SELECT '120000', 5 UNION ALL
SELECT '130000', 6 UNION ALL
SELECT '130000', 7 UNION ALL
SELECT '130000', 8

SELECT b.* FROM
(SELECT DISTINCT [type] FROM #temp) a
CROSS APPLY
(SELECT TOP(3) * FROM #temp WHERE [type] = a.[type]) b

[dawugui]

select t.* from tb t where Column2 in
(select top 3 Column2 from tb where Column1 = Column1)