|
锁定老帖子 主题：考考你的SQL水平
精华帖 (0) :: 良好帖 (0) :: 新手帖 (13) :: 隐藏帖 (1)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2012-05-16
最后修改：2012-05-16
针对第一个问题，在数据量大时，高效的SQL语句：
select * from test t1,test t2 where t1.name=t2.name and (t1.hobby='basketball' and t2.hobby='badminton') union all select * from test t1,test t2 where t1.name=t2.name and (t1.hobby='badminton' and t2.hobby='basketball'); 如果大数据量SQL查询时，一般避免使用Having进行过滤，或者嵌入过多子嵌套语句. |
| 返回顶楼 | |
|
发表时间：2012-05-17
lpjias 写道 IN ORACLE
--hobby即包含basketball,又包含badminton的name select * from test t where t.HOBBY = 'basketball' and exists (select 1 from test t1 where t.name=t1.name and t1.HOBBY = 'badminton')； --需要扩展性的写法 select name from ( select t.name as name,t.hobby as hoby from test t group b t.name,t.hobby ) res where res.hobby in ('basketball','badminton') GROUP BY name HAVING COUNT(res.name) >= 2 附带： LZ第（1）条是“只包含basketball和badminton”，而非“即包含basketball,又包含badminton”; LZ第（2）条是个人无想法； LZ第（3）条，个人无想法； having这个东西很有用... 多谢指教.. |
| 返回顶楼 | |
|
发表时间：2012-05-17
select a.name,a.hobby as hobby1,b.hobby as hobby2 from test a,test b
where a.name =b.name and a.hobby='badminton' and b.hobby='basketball' |
| 返回顶楼 | |
|
发表时间：2012-05-17
考虑到扩展性：
将其进行行列转换。 a，1 a，2 b，1 b，2 b，3 c，1 c，2 c，3 c，4 转换为临时表 a,1,2，null，null b,1,2,3，null c,1,2,3,4 从临时表中 添加过滤条件。如 LZ所说的badminton，name-hobby |
| 返回顶楼 | |
|
发表时间：2012-05-18
select name,hobby from test where hobby = 'basketball' and name in ( select name from test where hobby = 'badminton'）；
测试环境sqlserver 2005 |
| 返回顶楼 | |
|
发表时间：2012-05-21
select NAME from test t,
(select NAME from test where HOBBY='basketball' group by NAME) t1
where t.HOBBY='badminton' and t1.NAME=t.NAME
|
| 返回顶楼 | |
|
发表时间：2012-05-21
SELECT * FROM TEST WHERE NAME IN(SELECT A,NAME FROM TEST A,TEST B WHERE A.NAME=B.NAME AND A.HOBBY='basketball' AND B. HOBBY = 'badminton')
无关联子查询，没有可塑性的方法 |
| 返回顶楼 | |
|
发表时间：2012-05-21
select * from
(select name ,max(case when hobby='basketball' then 1 else 0 end) as basketball ,max(case when hobby='football' then 1 else 0 end) as football ,max(case when hobby='badminton' then 1 else 0 end) as badminton ,max(case when hobby='table tennis' then 1 else 0 end) as `table tennis` from test group by name) aa where aa.basketball=1 and aa.badminton=1 |
| 返回顶楼 | |
|
发表时间：2012-05-22
楼主你这个说法是不是又问题啊？你要求的的是即会basketball又会badminton的人，为什么最后一个david的资料不让显示呢？第9项完全满足你的要求啊？
以下是我的代码： select * from test where name in( select name from test where hobby = 'badminton' and name in( select name from test where hobby = 'basketball' ) ); |
| 返回顶楼 | |
|
发表时间：2012-05-23
select a.* from test a
join (select * from test where hobby='basketball') b on a.name=b.name join (select * from test where hobby='badminton') b2 on a.name=b2.name where a.hobby='badminton' or a.hobby='basketball'; |
| 返回顶楼 | |
