sql 求教，两个相同的ID取另一张表，只显示相同的数据

[qq_30890773]

表一
id
1
2
4
表二
fid name
1 脏三
2 李四
1 李四
2 一包
1 王毅
2 呼呼
4 一包
4 李四

这样取值，只需要相同的那条，也就是只要 李四，其余的不显示 这种情况下SQL怎么写？

[二月十六]

没太看明白规则，试试这样

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int)
Insert #T1
select 1 union all
select 2 union all
select 4
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([fid] int,[name] nvarchar(22))
Insert #T2
select 1,N'脏三' union all
select 2,N'李四' union all
select 1,N'李四' union all
select 2,N'一包' union all
select 1,N'王毅' union all
select 2,N'呼呼' union all
select 4,N'一包' union all
select 4,N'李四'
Go
--测试数据结束
SELECT  name
FROM    #T2
        JOIN #T1 ON id = fid
GROUP BY name
HAVING  COUNT(DISTINCT fid) = ( SELECT  COUNT(DISTINCT id)
                                FROM    #T1
                              )

[zjcxc]

with t1 as (
    select 1 as id
    union all
    select 2
    union all
    select 4
),t2 as (
    select 1 as fid,'张三' as name
    union all
    select 2,'李四'
    union all
    select 1,'李四'
    union all
    select 2,'一包'
    union all
    select 1,'王毅'
    union all
    select 2,'呼呼'
    union all
    select 4,'一包'
    union all
    select 4,'李四'
)
SELECT * FROM T2 b1
WHERE NOT EXISTS(
		SELECT * FROM t1 a
		WHERE NOT EXISTS(
				SELECT * FROM T2 b2
				WHERE b2.name = b1.name
					AND a.id = b2.fid
				)
	)
ORDER BY name, fid

[文盲老顾]

with t1 as (
	select 1 as id
	union all
	select 2
	union all
	select 4
),t2 as (
	select 1 as fid,'张三' as name
	union all
	select 2,'李四'
	union all
	select 1,'李四'
	union all
	select 2,'一包'
	union all
	select 1,'王毅'
	union all
	select 2,'呼呼'
	union all
	select 4,'一包'
	union all
	select 4,'李四'
)
select * from (select name from t2 group by name) a
--cross apply(select (select convert(varchar(10),fid)+';' from t2 where name=a.name order by fid for xml path('')) as ids) b
where (select convert(varchar(10),fid)+';' from t2 where name=a.name order by fid for xml path(''))=(select convert(varchar(10),id)+';' from t1 order by id for xml path(''))

[文盲老顾]

with t1 as (
	select 1 as id
	union all
	select 2
	union all
	select 4
),t2 as (
	select 1 as fid,'张三' as name
	union all
	select 2,'李四'
	union all
	select 1,'李四'
	union all
	select 2,'一包'
	union all
	select 1,'王毅'
	union all
	select 2,'呼呼'
	union all
	select 4,'一包'
	union all
	select 4,'李四'
)
select * from (select name from t2 group by name) a
--cross apply(select (select convert(varchar(10),fid)+';' from t2 where name=a.name order by fid for xml path('')) as ids) b
--where (select convert(varchar(10),fid)+';' from t2 where name=a.name order by fid for xml path(''))=(select convert(varchar(10),id)+';' from t1 order by id for xml path(''))
where not exists(select top 1 1 from t1 b where not exists(select top 1 1 from t2 where name=a.name and fid=b.id))
and not exists(select top 1 1 from t2 b where not exists(select top 1 1 from t1 where id=b.fid) and name=a.name)