考考你的SQL水平

select t.name from test t,test t1 where t.name = t1.name and t1.hobby = 'basketball' and t.hobby = 'badminton';

in oracle

select t.name from zz_test0523 t where t.hobby = 'basketball'
intersect
select t.name from zz_test0523 t where t.hobby = 'badminton'

group by 搭配having

这些SQL写的真好 学习了

环境：Oacle10g以上
思路：主要考虑扩展性。

        1. 将“哪些人即会玩basketball又会玩badminton”单独创建一张配置表，这样，如果查询条件发生改变，
         只需要修改配置表即可；
         2. 将每个人对应的hobby拼接成串，配置表中的hobby也拼接成串，用两个串相等来判定需要的结果，
         （注意拼接串时候需要hobby排序）

--原表
create table lxw_1 (NAME varchar(20) not null, HOBBY varchar(20) not null);

insert into lxw_1 values('Adam','basketball');  
insert into lxw_1 values('Bill','basketball');  
insert into lxw_1 values('Bill','football');  
insert into lxw_1 values('Cyper','basketball');  
insert into lxw_1 values('Cyper','badminton');  
insert into lxw_1 values('David','basketball');  
insert into lxw_1 values('David','badminton');  
insert into lxw_1 values('David','table tennis');
commit;

--参数配置表
create table lxw_2 (HOBBYSTR varchar2(200));

insert into lxw_2 values('basketball');
insert into lxw_2 values('badminton');
commit;

 

 

   查询代码：

 

select c.name,
       c.hobby
from 
     (select name,
      hobby,
      to_char(wmsys.wm_concat(hobby) over(partition by name order by hobby)) as concat_hobby
      from lxw_1
      ) a,
     (select to_char(wmsys.wm_concat(hobbystr)) as hobbystr
      from (select * from lxw_2 order by hobbystr)
      ) b,
      lxw_1 c 
where a.concat_hobby = b.hobbystr
and   a.name = c.name

 

   结果：

 

 

NAME                 HOBBY
-------------------- --------------------
Cyper                basketball
Cyper                badminton
David                basketball
David                badminton
David                table tennis

 

('Adam','basketball')  
('Bill','basketball')
('Bill','basketball')                       //有重复数据出现
('Bill','football') 
('Cyper','basketball') 
('Cyper','badminton') 
('David','basketball') 
('David','basketball')                     //有重复数据出现
('David','badminton')  
('David','table tennis')
如果表中有重复行数据出现..你们怎么办....
请初级选手们不要在吹牛B,耽误别人学习真正的SQL知识了
select t.name from test t
group by t.name
having (sum(decode(t.HOBBY,'basketball',1))+sum(decode(t.HOBBY,'badminton',1)))>1

SQL文不是比谁写的长.写的长你就赢了...要写的巧...
好好研究研究哥哥的SQL文吧.... 好好来一场头脑风暴吧.