select 姓名 from table where 地区 = '北京' and 姓名 in (select 姓名 from table where 地区 = '上海')
select distinct a.姓名 from 表名 a where exists(select 1 from 表名 where 姓名=a.姓名 and 地区='北京') and exists(select 1 from 表名 where 姓名=a.姓名 and 地区='上海')
declare @t table(姓名 varchar(10),地区 varchar(10)) insert into @t select '张三','北京' union all select '张三','上海' union all select '李四','北京' union all select '王五','北京'select distinct 姓名 from @t a where exists(select * from @t where 姓名=a.姓名 and 地区<>a.地区)
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(distinct a2 )>1
select 姓名 from (select 姓名 from table where 地区 = '北京') a join (select 姓名 from table where 地区 = '上海') b on a.姓名 = b.姓名
declare @t table(姓名 varchar(10),地区 varchar(10)) insert into @t select '张三','北京' union all select '张三','上海' union all select '李四','北京' union all select '王五','北京'select * from @t a where exists(select * from @t where 姓名=a.姓名 and 地区='上海') and 地区='北京'
select distinct a.姓名 from 表名 a where exists(select 1 from 表名 where 姓名=a.姓名 and 地区='北京') and exists(select 1 from 表名 where 姓名=a.姓名 and 地区='上海') 吃了个饭就晚了。
declare @t table(姓名 varchar(10),地区 varchar(10)) insert into @t select '张三','北京' union all select '张三','上海' union all select '李四','北京' union all select '王五','北京'select 姓名 from @t where 地区 >= all(select '上海' union select '北京')
SELECT A.[NAME],COUNT(A.ADDRESS) AS B FROM (SELECT DISTINCT NAME,ADDRESS FROM TABLE1) A GROUP BY A.[NAME] HAVING COUNT(A.ADDRESS)>=2方法简单
where 姓名='张三'
from table
where 地区 = '北京'
and 姓名 in (select 姓名
from table
where 地区 = '上海')
from 表名 a
where
exists(select 1 from 表名 where 姓名=a.姓名 and 地区='北京')
and
exists(select 1 from 表名 where 姓名=a.姓名 and 地区='上海')
insert into @t select '张三','北京'
union all select '张三','上海'
union all select '李四','北京'
union all select '王五','北京'select distinct 姓名 from @t a where exists(select * from @t where 姓名=a.姓名 and 地区<>a.地区)
insert into @t
select '张三','北京' union all
select '张三','上海' union all
select '李四','北京' union all
select '王五','北京' select a1
from @t
group by a1
having count(distinct a2 )>1
from (select 姓名
from table
where 地区 = '北京') a
join (select 姓名
from table
where 地区 = '上海') b
on a.姓名 = b.姓名
insert into @t select '张三','北京'
union all select '张三','上海'
union all select '李四','北京'
union all select '王五','北京'select * from @t a where exists(select * from @t where 姓名=a.姓名 and 地区='上海') and 地区='北京'
from 表名 a
where
exists(select 1 from 表名 where 姓名=a.姓名 and 地区='北京')
and
exists(select 1 from 表名 where 姓名=a.姓名 and 地区='上海')
吃了个饭就晚了。
insert into @t select '张三','北京'
union all select '张三','上海'
union all select '李四','北京'
union all select '王五','北京'select 姓名 from @t where 地区 >= all(select '上海' union select '北京')
GROUP BY A.[NAME]
HAVING COUNT(A.ADDRESS)>=2方法简单