表a
name value
1 'a'
1 'b'
1 'c'
表b
id value
1 'a'
2 'b'
3 'd'
4 'e'
我希望如果表a中value的全部数值在表b的value中都存在就显示表a的name,否则显示无查询结果!
请问这sql应该怎么写啊?谢谢
name value
1 'a'
1 'b'
1 'c'
表b
id value
1 'a'
2 'b'
3 'd'
4 'e'
我希望如果表a中value的全部数值在表b的value中都存在就显示表a的name,否则显示无查询结果!
请问这sql应该怎么写啊?谢谢
WHERE NOT EXISTS(
SELECT 1 FROM 表A
WHERE value NOT IN(
SELECT value FROM 表b
)
)
select T1.[name] from
(select [name],count(value) as co from a group by [name]) T1
inner join
(select [name],count(value) as co from a where value in (select value from b) group by [name] ) T2
on T1.[name]=T2.[name] and T1.co=T2.co