一句sql就可以出来上面的结果,我不信.
解决方案 »
- SQL递归来取得树型目录下的数据?
- 求一SQL语句?
- 不使用MAX,MIN和子查询,如何去除重复行
- 如何创建不同数据类型的数据表
- 一个表述日期时间的字段,却定义为numeric(13,0)类型,如:1159440621580 代表:2006-09-28 18:50:21 请问怎样还原为时间?
- 请教关于CASE WHEN 的一个写法
- select有空格的项目,为什么会自动过滤掉最后的空格?
- 如何开设ftp
- 如何连接局域网内不同网段的SQL SERVER 2000 数据库!
- 关于bcp的问题(加急),大力,邹建请进
- 请问我在发布用SQL SERVER数据库的问题?????????
- 老鸟请进!高分探讨数据库应用问题
"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的情况,可能会更复杂,但是还是能用一条语句搞定!不过不同的数据库所使用的技巧可能不一样。
姓名,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 ;
"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语句还是可以相通的。
"专业名称",
(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 ;
(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(+)
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