declare @t table(a1 varchar(10),a2 varchar(10)) insert into @t select '张三','北京' union all select '张三','上海' union all select '李四','北京' union all select '王五','北京' select a1 from @t group by a1 having count(a1)>1
table A name no sex 1 2 1 1 3 2 2 1 2 3 5 3 3 6 5 4 5 5 想得到 name 1 3
create table A(name int,no int,sex int) insert into A(name,no,sex) select 1,2,1 union all select 1,3,2 union all select 2,1,2 union all select 3,5,3 union all select 3,6,5 union all select 4,5,5select name from a group by name having count(name) > 1drop table A
declare @t table(name int,no int,sex int) insert into @t select 1,2,1 union all select 1,3,2 union all select 2,1,2 union all select 3,5,3 union all select 3,6,5 union all select 4,5,5select distinct name from @t a where exists(select * from @t where name=a.name and no<>a.no and sex<>a.sex)
SELECT FIELDA,COUNT(FIELDA) AS A FROM AI GROUP BY DEVICEID HAVING COUNT(FIELDA) > 2 ORDER BY A
insert into @t
select '张三','北京' union all
select '张三','上海' union all
select '李四','北京' union all
select '王五','北京'
select a1
from @t
group by a1
having count(a1)>1
name no sex
1 2 1
1 3 2
2 1 2
3 5 3
3 6 5
4 5 5
想得到
name
1
3
insert into A(name,no,sex)
select 1,2,1 union all
select 1,3,2 union all
select 2,1,2 union all
select 3,5,3 union all
select 3,6,5 union all
select 4,5,5select name from a group by name having count(name) > 1drop table A
insert into @t select 1,2,1
union all select 1,3,2
union all select 2,1,2
union all select 3,5,3
union all select 3,6,5
union all select 4,5,5select distinct name from @t a where exists(select * from @t where name=a.name and no<>a.no and sex<>a.sex)
GROUP BY DEVICEID
HAVING COUNT(FIELDA) > 2
ORDER BY A