select * from tablename group by zh having count(*) =1 order by kyrq
--下面是数据测试--测试数据 declare @t table(ZH int,ZS varchar(2),KYRQ varchar(2),QYMC varchar(2)) insert into @t select 1,'t1','a1','b1' union all select 2,'t2','a2','b2' union all select 2,'t2','a2','b2' union all select 3,'t3','a3','b3' union all select 4,'t4','a4','b4' union all select 4,'t4','a4','b4' union all select 5,'t5','t5','b5'--查询 select * from @t where zh in(select zh from @t group by zh having count(*)=1) order by kyrq/*--结果 ZH ZS KYRQ QYMC ----------- ---- ---- ---- 1 t1 a1 b1 3 t3 a3 b3 5 t5 t5 b5(所影响的行数为 3 行) --*/
ZH ZS KYRQ QYMC 1 t1 a1 b1 2 t2 a2 b2 2 t2 a2 b2 3 t3 a3 b3 4 t4 a4 b4 4 t4 a4 b4 5 t5 t5 b5select * from 表 aa where (select Count(*) from 表 bb where aa.zh=bb.zh)=1
declare @t table(ZH int,ZS varchar(2),KYRQ varchar(2),QYMC varchar(2))
insert into @t
select 1,'t1','a1','b1'
union all select 2,'t2','a2','b2'
union all select 2,'t2','a2','b2'
union all select 3,'t3','a3','b3'
union all select 4,'t4','a4','b4'
union all select 4,'t4','a4','b4'
union all select 5,'t5','t5','b5'--查询
select * from @t
where zh in(select zh from @t group by zh having count(*)=1)
order by kyrq/*--结果
ZH ZS KYRQ QYMC
----------- ---- ---- ----
1 t1 a1 b1
3 t3 a3 b3
5 t5 t5 b5(所影响的行数为 3 行)
--*/
1 t1 a1 b1
2 t2 a2 b2
2 t2 a2 b2
3 t3 a3 b3
4 t4 a4 b4
4 t4 a4 b4
5 t5 t5 b5select * from 表 aa where (select Count(*) from 表 bb where aa.zh=bb.zh)=1
----------- ---- ---- ----
1 t1 a1 b1
2 t2 a2 b2
3 t3 a3 b3
4 t4 a4 b4
5 t5 t5 b5
各位高手在帮忙看看?