SQL²éѯ¡¡¸ßÊÖ¿´Ò»ÏÂÄÚÈÝ - MS-SQL Server / »ù´¡Àà

Êý¾Ý¿âÀïÓÐ uid (Éí·ÝÖ¤ºÅ) xmname (ÏîÄ¿Ãû³Æ) Á½¸ö×ֶΠ
¿âÄÚÈÝÈçÏ£º

uid xmname

1 xxx
1 YYY
1 ZZZ
2 AAA
2 BBB
3 CCC
4 DDD

ÎÒÏë²éѯ Ïàͬ¡¡uid µÄ¡¡xmname ͬʱÓÐxxx ºÍyyy µÄÈËÓÐÄÄЩ£¬£¬Ôõô²é°¡£¬£¬¡¡¼Ç¼³öÀ´±ØÐëΨһ£¡£¡£¡

---

select uid 
from tb t
where xname='xxx' or xname='yyy'
having count(*)>2

---

SQL code:
select uid
from tb t
where xname='xxx' or xname='yyy'
having count(*)>2
group by uid

---

select distinct uid
from tb k  
where exists(select * from tb where k.uid=uid and xmname='XXX')
and exists(select * from tb where k.uid=uid and xmname='YYY')

---

SQL code:


IF object_id('tb')IS NOT NULL DROP TABLE tb
CREATE TABLE tb(uid INT,xmname VARCHAR(5))
INSERT INTO tb
SELECT 1, 'xxx'UNION ALL
SELECT 1 ,'YYY' UNION ALL
SELECT 1 ,'ZZZ'UNION ALL
SELECT 2 ,'AAA'UNION ALL
SELECT 2 ,'BBB'UNION ALL
SELECT 3 ,'CCC'UNION ALL
SELECT 4 ,'DDD'



SELECT * from (
SELECT DISTINCT uid,xmname=stuff((SELECT ','+xmname from tb WHERE uid=a.uid FOR xml path('') ),1,1,'')
from tb a
)c
WHERE charindex('xxx,YYY',xmname)>0

---

ÒýÓÃ<