One sql question help! - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

One sql question help!

One sql question help!# Database - 数据库

h*u2003-05-29 07:05

1 楼

If I have a table named Emp and it has a column, named hire_time.
how to write a query to get the result like:
total | 1995 | 1996|
20 3 4
in one record?

b*e2003-05-29 07:05

2 楼

in my opinion, no way.
if you use 'group by', you can't get the number for each year,
but you can't get total number.

【在 h**u 的大作中提到】

: If I have a table named Emp and it has a column, named hire_time.
: how to write a query to get the result like:
: total | 1995 | 1996|
: 20 3 4
: in one record?

z*y2003-05-29 07:05

3 楼

How about this :
Select count(*) as year_number, year
from table
group by year
where year is not null
But by this way, still cannot put the total of totall at the same line.

【在 b*e 的大作中提到】

: in my opinion, no way.
: if you use 'group by', you can't get the number for each year,
: but you can't get total number.

n*a2003-05-29 07:05

4 楼

If you are using Oracle 8.1.6 upper, you are lucky, because you can do this:
select year, count(*) as year_number
from table
where year is not null
group by rollup(year)

【在 z***y 的大作中提到】

:
: How about this :
: Select count(*) as year_number, year
: from table
: group by year
: where year is not null
: But by this way, still cannot put the total of totall at the same line.