select * from user where name in (select name from user group by name having count(1)>1) order by name
SELECT boyCount=sum(case sex when 1 then 1 else 0 end) ,girlCount=sum(case sex when 0 then 1 else 0 end) FROM 表 GROUP BY name HAVING COUNT(*)>1
select * from user as a where name in (select name from user group by name having count(1)>1) and exists(select 1 from user where name=a.name and sex<>a.sex) order by name
select count(t.id) from [user] t where exists(select 1 from [user] where name=t.name and sex!=t.sex)
---表 create table T(id int identity(1,1), name char(8),pass char(8),sex int,tright int)--测试数据 insert t select '张三','1',1,1 insert t select '李四','1',1,1 insert t select '张三','1',0,1 insert t select '王五','1',0,1 insert t select '马六','1',1,1 insert t select '李四','1',1,1 insert t select '马六','1',0,1--语句select name as aa,count(id) as 重名个数 from t group by name having count(name)>=2 and name not in(select name from t group by name,sex having count(name)>=2)
select name,count(id) as mnum from t a where exists(select 1 from t where name=a.name and sex!=a.sex) group by name
select count(distinct t.name) as 组 from [user] t where exists(select 1 from [user] where name=t.name and sex!=t.sex)
select count(t.id) from [user] t where exists(select 1 from [user] where name=t.name and sex!=t.sex)
子陌红尘的这句果然写的功力深厚.自叹不如. select count(t.id) from [user] t where exists(select 1 from [user] where name=t.name and sex!=t.sex)
where name in (select name from user
group by name having count(1)>1)
order by name
,girlCount=sum(case sex when 0 then 1 else 0 end)
FROM 表
GROUP BY name HAVING COUNT(*)>1
where name in (select name from user
group by name having count(1)>1)
and exists(select 1 from user
where name=a.name and sex<>a.sex)
order by name
count(t.id)
from
[user] t
where
exists(select 1 from [user] where name=t.name and sex!=t.sex)
create table T(id int identity(1,1), name char(8),pass char(8),sex int,tright int)--测试数据
insert t select '张三','1',1,1
insert t select '李四','1',1,1
insert t select '张三','1',0,1
insert t select '王五','1',0,1
insert t select '马六','1',1,1
insert t select '李四','1',1,1
insert t select '马六','1',0,1--语句select name as aa,count(id) as 重名个数
from
t group by name
having count(name)>=2 and name not in(select name from t group by name,sex having count(name)>=2)
from
t a
where
exists(select 1 from t where name=a.name and sex!=a.sex)
group by name
count(distinct t.name) as 组
from
[user] t
where
exists(select 1 from [user] where name=t.name and sex!=t.sex)
count(t.id)
from
[user] t
where
exists(select 1 from [user] where name=t.name and sex!=t.sex)
select
count(t.id)
from
[user] t
where
exists(select 1 from [user] where name=t.name and sex!=t.sex)