这样的功能怎么实现:
name company
张 同兴
王 同兴
李 安达
赵 同兴
王 星宇
张 安达
李 同兴
我该怎样把既属于同兴公司,又属于安达公司的“张”和既属于同兴公司又属于星宇公司的“王”查出来呢?
---------------------------------------------------------------
select name, company from 表 where company='同兴' and company='安达' and name='张'
select name, company from 表 where company='同兴' and company='星宇' and name='王'
---------------------------------------------------------------
SELECT distinct t1.name
FROM yourtable t1, yourtable t2
WHERE t1.name = t2.name
AND t1.company <> t2.company
---------------------------------------------------------------
this one is simpler:
SELECT name
FROM yourtable
GROUP BY name
Having COUNT(*) > 1
---------------------------------------------------------------
SELECT name
FROM yourtable
GROUP BY name
Having COUNT(*) > 1
---------------------------------------------------------------
select a.name,a.company,b.name,b.company
from table1 a , table1 b
where a.name = b.name
and a.company = 某公司
and b.company <> 某公司
group by a.name,a.company,b.name,b.company
---------------------------------------------------------------
select name,count(*) as count into#1 from 表
select * from 表 where name in(select distinct name from #1 where count>1)
---------------------------------------------------------------
select name, company
from tablename
where (name='张' and (company='同兴' or company='安达'))
or (name='王' and (company='同兴' or company='星宇'))
---------------------------------------------------------------
select name from csdn1 where (company = '同兴') and (name in (select name from csdn1 where company = '安达' ))