select * from a t where zl=0 and (select count(1) from a where sfzh=t.sfzh)>=2
SELECT * FROM ta AS A WHERE sfzh IN(SELECT sfzh FROM ta GROUP BY sfzh HAVING COUNT(*)>1) AND zl=0
select * from 表 where zl=0 group by sfzh,zl having count(*)>=2
select * from a join (select sfzh from a where zh=0 group by sfzh having count(1)>1) b on a.sfzh=b.sfzh and a.zl=0
create table sf (sfzh nvarchar(21),zl int)insert into sf select '111111',1 union all select '222222',0 union all select '222222',0 union all select '333333',0select sfzh from sf where zl =0 group by sfzh having count(*)>=2sfzh --------------------- 222222(1 row(s) affected)
select sfzh,sum(case when zl=0 then 1 else 0 end) zl0sum from a group by sfzh having sum(case when zl=0 then 1 else 0 end)>1没测试数据,看看这样试下.
select a.* from sf a inner join ( select sfzh from sf where zl =0 group by sfzh having count(*)>=2 ) b on a.sfzh = b.sfzh
select a.* from a where ZL=0 group by SFZH having count(*)>=2
create table sf (sfzh nvarchar(21),zl int)insert into sf select '111111',1 union all select '222222',0 union all select '222222',0 union all select '333333',0select sfzh,sum(case when zl=0 then 1 else 0 end) zl0sum from sf group by sfzh having sum(case when zl=0 then 1 else 0 end)>1 /* sfzh zl0sum --------------------- ----------- 222222 2(1 行受影响) */借用五楼的数据.
修正一下: select * from a t where zl=0 and (select count(1) from a where zl=0 and sfzh=t.sfzh)>=2
表a 有字段sfzh(身份证号),zl(种类) 现要查询表a中sfzh有两条或以上的且zl=0的数据 怎样写这个查询语句? select sfzh from a where zl=0 group by sfzh haveing count(*)>=2
*
from
a t
where
zl=0
and
(select count(1) from a where sfzh=t.sfzh)>=2
FROM ta AS A
WHERE sfzh IN(SELECT sfzh FROM ta GROUP BY sfzh HAVING COUNT(*)>1)
AND zl=0
create table sf
(sfzh nvarchar(21),zl int)insert into sf
select '111111',1
union all
select '222222',0
union all
select '222222',0
union all
select '333333',0select sfzh from sf where zl =0 group by sfzh having count(*)>=2sfzh
---------------------
222222(1 row(s) affected)
group by sfzh
having sum(case when zl=0 then 1 else 0 end)>1没测试数据,看看这样试下.
select a.* from sf a
inner join
(
select sfzh from sf where zl =0 group by sfzh having count(*)>=2
) b on a.sfzh = b.sfzh
where ZL=0
group by SFZH
having count(*)>=2
(sfzh nvarchar(21),zl int)insert into sf
select '111111',1
union all
select '222222',0
union all
select '222222',0
union all
select '333333',0select sfzh,sum(case when zl=0 then 1 else 0 end) zl0sum from sf
group by sfzh
having sum(case when zl=0 then 1 else 0 end)>1
/*
sfzh zl0sum
--------------------- -----------
222222 2(1 行受影响)
*/借用五楼的数据.
select
*
from
a t
where
zl=0
and
(select count(1) from a where zl=0 and sfzh=t.sfzh)>=2
现要查询表a中sfzh有两条或以上的且zl=0的数据
怎样写这个查询语句? select sfzh from a where zl=0 group by sfzh haveing count(*)>=2