34,594
社区成员
发帖
与我相关
我的任务
分享
/**NAME区最大**/
CREATE TABLE #A (code VARCHAR(10),s_no VARCHAR(10),s_name VARCHAR(10))
INSERT INTO #A
SELECT '01','000001','A' UNION ALL
SELECT '01','000002','A' UNION ALL
SELECT '01','000001','B' UNION ALL
SELECT '01','000003','B' UNION ALL
SELECT '01','000001','C' UNION ALL
SELECT '01','000002','C'
SELECT * FROM #A A
WHERE (SELECT COUNT(*) FROM #A WHERE CODE=A.CODE AND S_NO=A.S_NO AND S_NAME<A.S_NAME)<2
DROP TABLE #A
/**
01 000001 A
01 000002 A
01 000001 B
01 000003 B
01 000002 C
select top 2 * from tb where col3='A'
union all
select top 2 * from tb where col3='B'
union all
select top 2 * from tb where col3='C'
--2000
if object_id('tempdb..#')is not null drop table #
go
create TABLE # (col1 VARCHAR(2),col2 VARCHAR(6),col3 VARCHAR(1))
INSERT INTO #
SELECT '01','000001','A' UNION ALL
SELECT '01','000002','A' UNION ALL
SELECT '01','000001','B' UNION ALL
SELECT '01','000003','B' UNION ALL
SELECT '01','000001','C' UNION ALL
SELECT '01','000002','C'
alter table #
add id int identity(1,1)
go
select col1,col2,col3 from (
select cnt=(select count(*)+1 from # where col2=t.col2 and id<t.id),* from # t
)t
where cnt<=2 order by col1,col3,col2
/*col1 col2 col3
---- ------ ----
01 000001 A
01 000002 A
01 000001 B
01 000003 B
01 000002 C
(所影响的行数为 5 行)*/
---------------------------------
-- Author: liangCK 小梁
-- Date : 2008-11-10 20:52:38
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (col1 VARCHAR(2),col2 VARCHAR(6),col3 VARCHAR(1))
INSERT INTO @T
SELECT '01','000001','A' UNION ALL
SELECT '01','000002','A' UNION ALL
SELECT '01','000001','B' UNION ALL
SELECT '01','000003','B' UNION ALL
SELECT '01','000001','C' UNION ALL
SELECT '01','000002','C'
--SQL查询如下:
SELECT col1,col2,col3
FROM
(
SELECT *,
rid=ROW_NUMBER()
OVER(PARTITION BY col2 ORDER BY col1)
FROM @T
) AS T
WHERE rid<=2
ORDER BY col1,col3,col2
/*
col1 col2 col3
---- ------ ----
01 000001 A
01 000002 A
01 000001 B
01 000003 B
01 000002 C
(5 行受影响)
*/