|
锁定老帖子 主题：考考你的SQL水平
精华帖 (0) :: 良好帖 (0) :: 新手帖 (13) :: 隐藏帖 (1)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2012-05-04
这是多对多关系，表不应该这样设计的，不过也可以，Oracle有UNION查询：
beibeixiaomo 写道 select t.* from test t,
(select 'basketball' name from dual union all select 'badminton' from dual) x where t.hobby=x.name |
| 返回顶楼 | |
|
发表时间：2012-05-06
最后修改：2012-05-06
select distinct t1.* from TEST t1,TEST t2 where
t1.NAME=t2.NAME and ((t1.HOBBY='basketball' and t2.HOBBY='badminton' ) or (t1.HOBBY='badminton' and t2.HOBBY='basketball' )) lz 第三种写法可以这样 |
| 返回顶楼 | |
|
发表时间：2012-05-06
select * from test where name in (select name from test a where a.hobby = 'basketball') and name in (select name from test a where a.hobby = 'badminton') |
| 返回顶楼 | |
|
发表时间：2012-05-06
wangchengyong 写道
select *
from test t1
where t1.HOBBY in ('basketball', 'badminton')
and t1.name in (select t2.name
from test t2
where t2.HOBBY in ('basketball', 'badminton')
group by t2.name
having count(distinct t2.HOBBY) = 2)
这个和我的答案结果应该是最接近的，不过这个更简洁，从来没想到过having还有这种用法，学习了。 楼上有很多用两张表关联查的话，数据量大时会非常费时。 这种方法，如果在HOBBY、name字段有索引的话，速度会很快，而且这种SQL思路非常清晰。 |
| 返回顶楼 | |
|
发表时间：2012-05-07
在oracle中自己的一个写法（前提是name和hobby要组成联合主键不然和出现同一个爱好在一个人名下出现两次的可能性，自己把表改成了test4字段和数据内容一样）
select t1.rowno from (select rownum rowno , name, hobby from test4) t1 where t1.name in(select name from test4 t where t.hobby='basketball' or t.hobby='badminton' group by name having count(name) > 1) and t1.hobby='basketball' or t1.hobby='badminton' order by t1.rowno 结果： 4 5 6 7 |
| 返回顶楼 | |
|
发表时间：2012-05-07
sundysea 写道 既然已经知道basketball和badminton了，就只需要列出Name就可以了,然后在程序中对应做name - hobby。
select name from test where hobby in ('basketball','badminton') group by name having count(hobby)=2; hobby 重复了呢？ |
| 返回顶楼 | |
|
发表时间：2012-05-08
select distinct a.name, a.hobby hobby1, b.hobby hobby2
from TEST a
inner join TEST b
on a.name = b.name
where a.hobby = 'basketball'
and b.hobby = 'badminton';
|
| 返回顶楼 | |
|
发表时间：2012-05-08
最后修改：2012-05-08
in mysql: 返回5 6 7 8，因为数据太少，也无法比较下面两种方法哪个效率高。。。。
select *
from test
where hobby in ('basketball','badminton')
and name in (
select name
from test
where hobby in ('basketball','badminton')
group by name
having count(name)=2
)
或者是： select test.*
from test,(
select name
from test
where hobby in ('basketball','badminton')
group by name
having count(name)=2
) as t
where test.name = t.name and hobby in ('basketball','badminton')
|
| 返回顶楼 | |
|
发表时间：2012-05-08
--SQL Server 2005
use tempdb;
/*
create table test (NAME varchar(20) not null, HOBBY varchar(20) not null);
insert into test values('Adam','basketball');
insert into test values('Bill','basketball');
insert into test values('Bill','football');
insert into test values('Cyper','basketball');
insert into test values('Cyper','badminton');
insert into test values('David','basketball');
insert into test values('David','badminton');
insert into test values('David','table tennis');
*/
--使用SQL语句查出哪些人即会玩basketball又会玩badminton，找出这样的name-hobby组合
select [name],hobby
from test
where name in
(
select [name] from test
where hobby = 'basketball'
intersect
select [name] from test
where hobby = 'badminton'
);
|
| 返回顶楼 | |
|
发表时间：2012-05-09
还真的是写不出来呢~
|
| 返回顶楼 | |
