[散分]出个题散个分~~~

[fishmans]

题目很简单~

有一个表table1
结构与数据如下：
a b
01 2006-02-02
01 2006-02-02
02 2006-02-02
03 2006-02-01
03 2006-02-06
03 2006-02-02
04 2006-02-02
04 2006-02-02
05 2006-02-09
05 2006-02-12

'======================
要求如下：
以最简短的SQL求出有多个日期的a
如上结构结果为：
03
05

'===========================
分给最先给出结果并且SQL最简短的同志~~~~~~~~~~
OK，GO~~~

[northwolves]

try:

select distinct a from table1 group by a having max(b)>min(b)

[yedl830128]

希望小弟这次能帮得上你的忙
select a,count(b)
from table1
group by a
having count(b)=(select max(count(b))
from table 1
group by a);

[空心兜兜]

偶来接分咯!

[yedl830128]

select a,count(b)
from table1
having(count(b))>1
group by a
order by count(b) desc;
小弟自认为能拿到这100 分了.

[yedl830128]

我试了俩小时,认为大哥的表结构不满足关系表数据的行唯一性要求.
想问大哥认为 01 到底有几个日期?

[TERRYYRRET]

接分

[fishmans]

嗯，这里只有northwolves(狼行天下) 是最好的，不过应该可以不加distinct的

另：那个自称可以拿一百分的同志，那个having是否要放group by 后面？？而且就是加个主键对要求和结果也没什么影响并且也用不到。

[yedl830128]

谢谢指教.

[of123]

select a from (select a, b from table 1 group by a, b) where count(a) > 1

[of123]

select distinct a from (select a, b from table 1 group by a, b) where count(a) > 1

[GoldFox]

Select B.a From (Select A.a, Count(*) As Counts From (select distinct * from Table1) A Group By A.a) B Where B.Counts > 1

[junki]

进来学习一下.

[bulletCoderHope]

我都没读明白他的题目
求出有多个日期的a
又没主键

[faysky2]

create table tb1(a char(2),b datetime)
insert into tb1 select '01', '2006-02-02'
union all select '01','2006-02-02'
union all select '02','2006-02-02'
union all select '03','2006-02-01'
union all select '03','2006-02-06'
union all select '03','2006-02-02'
union all select '04','2006-02-02'
union all select '04','2006-02-02'
union all select '05','2006-02-09'
union all select '05','2006-02-12'

select distinct a1.a from tb1 a1 where a1.b<>(select top 1 b from tb1 a2 where a1.a=a2.a)

drop table tb1

[faysky2]

select distinct A.a from table1 A where A.b<>(select top 1 b from table1 B where A.a=B.a)

[yedl830128]

如果在 oracle 中 能否试试
select c from (select distinct(b) as c from table1 group by a)
where rownum<2

[yedl830128]

select max(c)
from (select count(b) as c from table1 group by (a));
这会应该行了.

[jabeginner]

Select B.a From (Select A.a, Count(*) As Counts From (select distinct * from Table1) A Group By A.a) B Where B.Counts > 1

[fishmans]

结帐了~~
看到yedl830128这么执着也给个20吧~

[yedl830128]

小弟出言不知天高地厚，大哥能谅解还能给分。实在感激，以后我定多加努力，不让大哥们失望！