# 一道简单的分组合计疑问，求解,该怎么处理

www.MyException.Cn  网友分享于：2013-03-28  浏览：10次

SELECT TO_CHAR(hiredate,'YYYY') "YEAR",COUNT(empno)
FROM emp
GROUP BY ROLLUP(TO_CHAR(hiredate,'YYYY'))
ORDER BY TO_CHAR(hiredate,'YYYY');

YEAR COUNT(empno)
1980 1
1981 10
1982 1
1987 2
14

YEAR COUNT(empno)
1980 1
1981 10
1982 1
1983 0
1987 2
14

------解决方案--------------------

SQL code
```
SELECT x.a years,nvl(y.counts,0) counts
FROM (SELECT a
FROM (SELECT '1980' a, '1981' b, '1982' c, '1983' d FROM dual)
UNION ALL
SELECT b
FROM (SELECT '1980' a, '1981' b, '1982' c, '1983' d FROM dual)
UNION ALL
SELECT c
FROM (SELECT '1980' a, '1981' b, '1982' c, '1983' d FROM dual)
UNION ALL
SELECT d
FROM (SELECT '1980' a, '1981' b, '1982' c, '1983' d FROM dual)) x,

(SELECT TO_CHAR(e.hiredate, 'yyyy') years, COUNT(1) counts
FROM emp e
WHERE TO_CHAR(e.hiredate, 'yyyy') IN ('1980', '1981', '1982', '1983')
GROUP BY TO_CHAR(e.hiredate, 'yyyy')) y
WHERE x.a = y.years(+)
ORDER BY 1

1    1980    1
2    1981    10
3    1982    1
4    1983    0
------解决方案--------------------SQL code

create table year(
year date
);
1980-1-1
1981-1-1
1982-1-1
1983-1-1
1987-1-1

select
to_char(t1.year,'yyyy') YEAR,count(t2.empno) EMP_NUM
from year t1 left join emp t2 on to_char(t1.year,'yyyy')=to_char(t2.hiredate,'yyyy')
group by to_char(t1.year,'yyyy')
order by to_char(t1.year,'yyyy')
------解决方案--------------------表中数据：实测结果：```