|
锁定老帖子 主题：考考你的SQL水平
精华帖 (0) :: 良好帖 (0) :: 新手帖 (13) :: 隐藏帖 (1)
|
|||||||||
|---|---|---|---|---|---|---|---|---|---|
| 作者 | 正文 | ||||||||
|
发表时间：2012-05-12
还是这张表，我们换一种问法：
求这样一个表，有2个字段： 1，某同时被某个人喜欢的两种hobby；2同时喜欢这两个hobby的人数 结果是： basketball,badminton,2 basketball,football,1 basketball,table tennis,1 badminton,table tennis,1 求SQL写法： 欢迎讨论 |
||||||||
| 返回顶楼 | |||||||||
|
发表时间：2012-05-12
对不起，这种写法效率可不好啊，自关联大才小用了。
可以在关联之前每个表做下筛选啊。 可以看下我新发的一个问法： 求频繁二项集 我是囧囧虾 写道 select a.name
from (test a inner join test b on a.name = b.name) where a.hobby = 'basketball' and b.hobby = 'badminton' 我的postgreSQL |
||||||||
| 返回顶楼 | |||||||||
|
发表时间：2012-05-12
sorry 结果错误，没有测试吧。
temptation 写道
--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-13
SELECT * from test where name in ( select name from test where hobby in ('basketball','badminton') group by NAME HAVING COUNT(name)>1 ) and hobby in ('basketball','badminton');
结果 "Cyper","basketball" "Cyper","badminton" "David","basketball" "David","badminton" |
||||||||
| 返回顶楼 | |||||||||
|
发表时间：2012-05-13
kidding87 写道 我觉得这个题目只用求出来name就可以了，因为hobby已经确定了= 'basketball' and 'badminton'
select name
from test
where t.hobby in ('basketball', 'badminton')
group by name
having count(name) = 2
我也是这么认为 |
||||||||
| 返回顶楼 | |||||||||
|
发表时间：2012-05-14
最后修改：2012-05-14
liuzhiqiangruc 写道 还是这张表，我们换一种问法：
求这样一个表，有2个字段： 1，某同时被某个人喜欢的两种hobby；2同时喜欢这两个hobby的人数 结果是： basketball,badminton,2 basketball,football,1 basketball,table tennis,1 badminton,table tennis,1 求SQL写法： 欢迎讨论 select count(distinct name), TO_CHAR(WMSYS.WM_CONCAT(CASE WHEN hobby is not null THEN hobby ELSE ''END)) from test group by name; 理解错啦，只能求到这里 |
||||||||
| 返回顶楼 | |||||||||
|
发表时间：2012-05-15
试试这个sql：
select CONCAT(t1.name,t1.HOBBY) from test t1, ( select t.name, sum(case when t.HOBBY in ( 'basketball' ,'badminton') then 1 else 0 end) total from test t group by t.name ) t2 WHERE t1.name = t2.name and t2.total > 1 |
||||||||
| 返回顶楼 | |||||||||
|
发表时间：2012-05-15
select t1.name from (select * from test where hobby='basketball') as t1 join (select * from test where hobby='badminton') as t2 on t1.name=t2.name;
|
||||||||
| 返回顶楼 | |||||||||
|
发表时间：2012-05-16
最后修改：2012-05-16
SELECT test.* FROM test, (SELECT `name`, SUM(hobby='basketball' OR hobby='badminton') AS size FROM test GROUP BY `name` HAVING size > 1) t1 WHERE test.`NAME` = t1.name AND test.`HOBBY` = 'backetball' OR test.`HOBBY` = 'badminton' MySql下测试通过 |
||||||||
| 返回顶楼 | |||||||||
|
发表时间：2012-05-16
In MySQL ,I try a test.
SQL Code 1:
select * from test t1 where t1.name in ( select t.name from test t where hobby = 'basketball' or hobby = 'badminton' group by name HAVING COUNT(NAME) = 2 ) and hobby = 'basketball' or hobby = 'badminton'
SQL Code 2:
select * from test c where c.name in ( select a1.name from ( select a.name from test a where a.hobby = 'basketball' ) a1,( select b.name from test b where b.hobby = 'badminton' ) a2 where a1.name = a2.name ) and (c.hobby = 'basketball' or c.hobby = 'badminton' )
Show the Result :
|
||||||||
| 返回顶楼 | |||||||||
