关于重复记录的查询!!!十万火急!

这样的功能怎么实现:

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 = '安达' ))

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus