关于日期查询统计...oracle (30分)

[hoofi]

问题1:
table1
姓名,出生日期,专业,email,爱好
现在要统计出各个专业里70-75年,75-80年,80-85年所有学生的人数
比如:
专业名称,70-75年人数,75-80年人数,80-85年人数
专业1 ,23, 34, 12
这个sql语句怎么写??
问题2:
怎么从上述表中的出生日期里面得到出生的年份??
比如:1980-02-16 得到 1980
这个查询如何实现>??

[4_Eye_Cat]

灌猪

[suny]

肯定可以用一句SQL写出来，不信你可以看看《Sybase Advanced Transact-SQL》

如果是Sybase......

select Major,
"70-75"=sum( sign( 1 + datediff(yy,"1970-01-01",Birthday) ) *
(1 - sign( 1 + datediff(yy,"1975-01-01",Birthday) ) )
),
"75-80"=sum( sign( 1 + datediff(yy,"1975-01-01",Birthday) ) *
(1 - sign( 1 + datediff(yy,"1980-01-01",Birthday) ) )
),
"80-85"=sum( sign( 1 + datediff(yy,"1980-01-01",Birthday) ) *
(1 - sign( 1 + datediff(yy,"1985-01-01",Birthday) ) )
)
from table1
where Birthday is not null
group by Major

如果要考虑Birthday为NULL的情况，可能会更复杂，但是还是能用一条语句搞定！不过不同的数据库所使用的技巧可能不一样。

[guostong]

每个时间段建一个试图，再把几个试图连接

[icedian]

table1
姓名,c,专业,email,爱好
现在要统计出各个专业里70-75年,75-80年,80-85年所有学生的人数
比如:
专业名称,70-75年人数,75-80年人数,80-85年人数
专业1 ,23, 34, 12
这个sql语句怎么写??
select distinct
"专业名称",
(select sum(出生日期) from table1 b where b.专业名称 =a.专业名称 [and 出生日期条件1]) as "70-75年人数",
(select sum(出生日期) from table1 b where b.专业名称 =a.专业名称 [and 出生日期条件2]) as "75-80年人数",
(select sum(出生日期) from table1 b where b.专业名称 =a.专业名称 [and 出生日期条件3]) as "80-85年人数"
from table1 a ;

[suny]

sorry,写错了，更正如下：

select Major,
"70-75"=sum( sign( 1 + sign(datediff(yy,"1970-01-01",Birthday)) ) *
(1 - sign( 1 + sign(datediff(yy,"1975-01-01",Birthday)) ) )
),
"75-80"=sum( sign( 1 + sign(datediff(yy,"1975-01-01",Birthday)) ) *
(1 - sign( 1 + sign(datediff(yy,"1980-01-01",Birthday)) ) )
),
"80-85"=sum( sign( 1 + sign(datediff(yy,"1980-01-01",Birthday)) ) *
(1 - sign( 1 + sign(datediff(yy,"1985-01-01",Birthday)) ) )
)
from table1
where Birthday is not null
group by Major

虽然你问的是Oracle，但我想SQL语句还是可以相通的。

[icedian]

select distinct
"专业名称",
(select sum(出生日期) from table1 b where b.专业名称 =a.专业名称 [and 出生日期条件1]) as "70-75年人数",
(select sum(出生日期) from table1 b where b.专业名称 =a.专业名称 [and 出生日期条件2]) as "75-80年人数",
(select sum(出生日期) from table1 b where b.专业名称 =a.专业名称 [and 出生日期条件3]) as "80-85年人数"
from table1 a ;

[guostong]

select a.major,isnull(year70,0),isnull(year75,0),isnull(year80,0) from
(select distinct major from table1) a,
(select major,count(*) year70 from table_1
where to_char(brth,'yyyy') between '70' and '75' group by major) b,
(select major,count(*) year75 from table_1
where to_char(brth,'yyyy') between '76' and '80' group by major) c,
(select major,count(*) year80 from table_1
where to_char(brth,'yyyy') between '82' and '85' group by major) d,
where b.major = a.major(+)
and c.major = a.major(+)
and d.major = a.major(+)

[suny]

OK，给出Oracle版本的SQL
select Major as "专业",
sum( sign( 1 + sign(to_number(to_char(Birthday,'YYYY'))-1970) ) *
( 1 - sign( 1 + sign(to_number(to_char(Birthday,'YYYY'))-1975) ) )
) as "70-75年人数",
sum( sign( 1 + sign(to_number(to_char(Birthday,'YYYY'))-1975) ) *
( 1 - sign( 1 + sign(to_number(to_char(Birthday,'YYYY'))-1980) ) )
) as "75-80年人数",
sum( sign( 1 + sign(to_number(to_char(Birthday,'YYYY'))-1980) ) *
( 1 - sign( 1 + sign(to_number(to_char(Birthday,'YYYY'))-1985) ) )
) as "80-85年人数"
from table1
group by Major

[zheng]

to_date(field,'YYYY')

[guo]

一句sql就可以出来上面的结果,我不信.