有这样一张表
+------+---------+
| name | subject |
+------+---------+
| 111 | aaa |
| 111 | bbb |
| 111 | ccc |
| 222 | aaa |
| 222 | bbb |
| 222 | ccc |
| 333 | ccc |
| 333 | bbb |
| 333 | aaa |
| 333 | ddd |
| 333 | eee |
| 444 | aaa |
| 444 | bbb |
| 444 | eee |
| 444 | fff |
+------+---------+要取出subject字段下仅含aaa,bbb,ccc的数据出来
结果显示为
+------+---------+
| name | subject |
+------+---------+
| 111 | aaa |
| 111 | bbb |
| 111 | ccc |
| 222 | aaa |
| 222 | bbb |
| 222 | ccc |
+------+---------+
要取出subject字段下含有aaa,bbb,ccc的数据出来
结果显示为
+------+---------+
| name | subject |
+------+---------+
| 111 | aaa |
| 111 | bbb |
| 111 | ccc |
| 222 | aaa |
| 222 | bbb |
| 222 | ccc |
| 333 | ccc |
| 333 | bbb |
| 333 | aaa |
| 333 | ddd |
| 333 | eee |
+------+---------+
请问sql语句怎么写。
+------+---------+
| name | subject |
+------+---------+
| 111 | aaa |
| 111 | bbb |
| 111 | ccc |
| 222 | aaa |
| 222 | bbb |
| 222 | ccc |
| 333 | ccc |
| 333 | bbb |
| 333 | aaa |
| 333 | ddd |
| 333 | eee |
| 444 | aaa |
| 444 | bbb |
| 444 | eee |
| 444 | fff |
+------+---------+要取出subject字段下仅含aaa,bbb,ccc的数据出来
结果显示为
+------+---------+
| name | subject |
+------+---------+
| 111 | aaa |
| 111 | bbb |
| 111 | ccc |
| 222 | aaa |
| 222 | bbb |
| 222 | ccc |
+------+---------+
要取出subject字段下含有aaa,bbb,ccc的数据出来
结果显示为
+------+---------+
| name | subject |
+------+---------+
| 111 | aaa |
| 111 | bbb |
| 111 | ccc |
| 222 | aaa |
| 222 | bbb |
| 222 | ccc |
| 333 | ccc |
| 333 | bbb |
| 333 | aaa |
| 333 | ddd |
| 333 | eee |
+------+---------+
请问sql语句怎么写。
select name,subject
from tb
where name in(select name
from tb
where subject in('aaa','bbb','ccc')
group by name
having count(*)=3)
select 111 name,'aaa' subject from dual union all
select 111,'bbb' from dual union all
select 111,'ccc' from dual union all
select 222,'aaa' from dual union all
select 222,'bbb' from dual union all
select 222,'ccc' from dual union all
select 333,'ccc' from dual union all
select 333,'bbb' from dual union all
select 333,'aaa' from dual union all
select 333,'ddd' from dual union all
select 333,'eee' from dual union all
select 444,'aaa' from dual union all
select 444,'bbb' from dual union all
select 444,'eee' from dual union all
select 444,'fff' from dual
)
--1、取出subject字段下仅含aaa,bbb,ccc的数据
select a.* from tab a where exists(
select 1 from (
select name ,replace(max(wm_subject),',','') wm_subject from(
select name,
wm_concat(subject)over(partition by name order by subject ) wm_subject
from tab
)
group by name
) b
where a.name=b.name and replace(b.wm_subject,'aaa'||'bbb'||'ccc') is null
)
--2、取出subject字段下含有aaa,bbb,ccc的数据
select a.* from tab a where exists(
select 1 from (
select name ,replace(max(wm_subject),',','') wm_subject from(
select name,
wm_concat(subject)over(partition by name order by subject ) wm_subject
from tab
)
group by name
) b
where a.name=b.name and instr(b.wm_subject,'aaa'||'bbb'||'ccc')>0
)