|
锁定老帖子 主题：考考你的SQL水平
精华帖 (0) :: 良好帖 (0) :: 新手帖 (13) :: 隐藏帖 (1)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2012-04-24
select * from test t1 where
exists (select 1 from test t2 where t2.name=t1.name and t2.HOBBY in('basketball','badminton') group by t2.name having count(t2.name)>=2 ); |
| 返回顶楼 | |
|
发表时间：2012-04-24
oracle中查询：
select name from test where hobby in ('basketball','badminton') group by name having count(name)=2; |
| 返回顶楼 | |
|
发表时间：2012-04-24
看半天 没看懂它的意思
|
| 返回顶楼 | |
|
发表时间：2012-04-24
oracle:
[code="java"]select * from test where name in (select name from test where hobby in ('basketball','badminton') group by name having count(name) = 2) and hobby in ('basketball','badminton'); |
| 返回顶楼 | |
|
发表时间：2012-04-24
oracle ,
select name,hobby,rn1 from( select name,hobby,rownum rn1,count(name) over(partition by name order by name) rn from test t where t.hobby='basketball' or t.hobby='badminton') where rn=2 order by rn1 |
| 返回顶楼 | |
|
发表时间：2012-04-24
我觉得这个题目只用求出来name就可以了，因为hobby已经确定了= 'basketball' and 'badminton'
select name
from test
where t.hobby in ('basketball', 'badminton')
group by name
having count(name) = 2
|
| 返回顶楼 | |
|
发表时间：2012-04-24
刚看以为很简单，动手后发现有点意思。
在Oracle测试通过
select * from test
where name in
(
select name from
(
select name, sum(hobbycode) from
(
select name,hobby,decode(hobby,'basketball',1,'badminton',2,0) hobbycode
from test
)
group by name
having sum(hobbycode) >= 3
)
)
and hobby in ('basketball','badminton')
|
| 返回顶楼 | |
|
发表时间：2012-04-24
uniquejava 写道
有如下数据表 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');
即第5， 6 ，7 ，8行
首先我觉得你给的答案存在问题，应该是5,6,7,8,9 Oracle SQL我觉得用临时表非常慢，还不如用其他的：
select * from test where name in (
select t1.name from test t1 join test t2 on t1.name=t2.name
where t1.hobby in ('basketball','badminton') and t2.hobby in ('basketball','badminton')
and t1.hobby<>t2.hobby
) --and hobby in ('basketball','badminton')
|
| 返回顶楼 | |
|
发表时间：2012-04-24
如果是oracle,yong connect by ,很简单
|
| 返回顶楼 | |
|
发表时间：2012-04-24
问题说得很有问题
“即包含basketball,又包含badminton的name所在的行”有点儿说得不明不白 首先两者都包含的行是不能存在的 应该是name相同的记录中两者都包含还说得过去 其次，"...所在的行"，怎么看都不是要获取name，而是行数。 |
| 返回顶楼 | |
