SELECT * FROM TB T WHERE 单位名称 in(select top 2 单位名称 from tb where left(单位编号 ,6)=left(t.单位编号 ,6) order by newid())
sql 2005可以 2000不知道怎么搞--测试环境 if OBJECT_ID('tb') is not null drop table tb; go create table tb ( RowID int identity primary key, 单位编号 varchar(20), 单位名称 varchar(20) ); go insert into tb select '41130215001','卧龙区飞扬网吧' union all select '41130215002','卧龙区八达网吧' union all select '41130215003','卧龙区精灵网吧' union all select '41130215004','卧龙区轻鸟网吧' union all select '41130315010','宛城区凯达网吧' union all select '41130315011','宛城区娟子网吧' union all select '41130315013','宛城区挚友网吧' union all select '41130315014','宛城区精英网吧' union all select '41130315015','宛城区心情网吧' union all select '41132110001','南召县金利网吧' union all select '41132110004','南召县连通网吧' union all select '41132110005','南召县四海网吧' union all select '41132210001','方城县心乐网吧' union all select '41132210002','方城县开心网吧' union all select '41132210004','方城县帅友网吧' union all select '41132210005','方城县环宇网吧' union all select '41132310001','西峡县时代网吧' union all select '41132310002','西峡县心雨网吧' union all select '41132310003','西峡县飞天网吧' union all select '41132310004','西峡县龙人网吧' union all select '41132310005','西峡县心约网吧' union all select '41132410001','镇平县心语网吧' union all select '41132410003','镇平县鸿运网吧' union all select '41132410004','镇平县豆豆轩网吧' union all select '41132410005','镇平县清心网吧' go --查询select * from ( select *,rn=row_number()over(partition by left(单位编号,6) order by newid()) from tb ) t where rn<3--结果 /* RowID 单位编号 单位名称 rn ----------- -------------------- -------------------- -------------------- 1 41130215001 卧龙区飞扬网吧 1 4 41130215004 卧龙区轻鸟网吧 2 9 41130315015 宛城区心情网吧 1 6 41130315011 宛城区娟子网吧 2 11 41132110004 南召县连通网吧 1 12 41132110005 南召县四海网吧 2 14 41132210002 方城县开心网吧 1 16 41132210005 方城县环宇网吧 2 17 41132310001 西峡县时代网吧 1 18 41132310002 西峡县心雨网吧 2 25 41132410005 镇平县清心网吧 1 22 41132410001 镇平县心语网吧 2(12 行受影响) */
--1.字段单位编号 唯一时: SELECT * FROM #T AS T WHERE 单位编号 IN ( SELECT TOP 2 单位编号 FROM #T WHERE 单位名称=T.单位名称 ORDER BY NEWID() )
--SQL Server 2005--1.使用ROW_NUMBER()进行排位分组 SELECT ID,GID,Author,Title,Date FROM ( SELECT rid=ROW_NUMBER() OVER(PARTITION BY 单位名称 ORDER BY NEWID()),* FROM #T ) AS T WHERE rid<=2--2.使用APPLY SELECT DISTINCT b.* FROM #T AS a CROSS APPLY ( SELECT TOP(2) * FROM #T WHERE a.单位名称 =单位名称 ORDER BY NEWID() ) AS b
select t.* from tb t where 单位编号 in (select top 2 单位编号 from tb where left(单位编号,6) = left(t.单位编号,6) order by 单位编号)select t.* from tb t where 单位编号 in (select top 2 单位编号 from tb where left(单位编号,6) = left(t.单位编号,6) order by 单位编号 desc)
单位名称 in(select top 2 单位名称 from tb where left(单位编号 ,6)=left(t.单位编号 ,6) order by newid())
if OBJECT_ID('tb') is not null drop table tb;
go
create table tb
(
RowID int identity primary key,
单位编号 varchar(20),
单位名称 varchar(20)
);
go
insert into tb select '41130215001','卧龙区飞扬网吧'
union all select '41130215002','卧龙区八达网吧'
union all select '41130215003','卧龙区精灵网吧'
union all select '41130215004','卧龙区轻鸟网吧'
union all select '41130315010','宛城区凯达网吧'
union all select '41130315011','宛城区娟子网吧'
union all select '41130315013','宛城区挚友网吧'
union all select '41130315014','宛城区精英网吧'
union all select '41130315015','宛城区心情网吧'
union all select '41132110001','南召县金利网吧'
union all select '41132110004','南召县连通网吧'
union all select '41132110005','南召县四海网吧'
union all select '41132210001','方城县心乐网吧'
union all select '41132210002','方城县开心网吧'
union all select '41132210004','方城县帅友网吧'
union all select '41132210005','方城县环宇网吧'
union all select '41132310001','西峡县时代网吧'
union all select '41132310002','西峡县心雨网吧'
union all select '41132310003','西峡县飞天网吧'
union all select '41132310004','西峡县龙人网吧'
union all select '41132310005','西峡县心约网吧'
union all select '41132410001','镇平县心语网吧'
union all select '41132410003','镇平县鸿运网吧'
union all select '41132410004','镇平县豆豆轩网吧'
union all select '41132410005','镇平县清心网吧'
go
--查询select * from (
select *,rn=row_number()over(partition by left(单位编号,6) order by newid()) from tb
) t where rn<3--结果
/*
RowID 单位编号 单位名称 rn
----------- -------------------- -------------------- --------------------
1 41130215001 卧龙区飞扬网吧 1
4 41130215004 卧龙区轻鸟网吧 2
9 41130315015 宛城区心情网吧 1
6 41130315011 宛城区娟子网吧 2
11 41132110004 南召县连通网吧 1
12 41132110005 南召县四海网吧 2
14 41132210002 方城县开心网吧 1
16 41132210005 方城县环宇网吧 2
17 41132310001 西峡县时代网吧 1
18 41132310002 西峡县心雨网吧 2
25 41132410005 镇平县清心网吧 1
22 41132410001 镇平县心语网吧 2(12 行受影响)
*/
--1.字段单位编号 唯一时:
SELECT * FROM #T AS T WHERE 单位编号
IN
(
SELECT TOP 2 单位编号 FROM #T WHERE 单位名称=T.单位名称
ORDER BY NEWID()
)
--SQL Server 2005--1.使用ROW_NUMBER()进行排位分组
SELECT ID,GID,Author,Title,Date
FROM
(
SELECT rid=ROW_NUMBER() OVER(PARTITION BY 单位名称 ORDER BY NEWID()),*
FROM #T
) AS T
WHERE rid<=2--2.使用APPLY
SELECT DISTINCT b.*
FROM #T AS a
CROSS APPLY
(
SELECT TOP(2) * FROM #T WHERE a.单位名称 =单位名称 ORDER BY NEWID()
) AS b