从计数器获取整数集合

Question

这将是一周中每一天雇佣7年的员工人数的最佳方式，而entry_date的日月年为01-6月-91。

例如：

     2000 2001 2002 etc..

SUN   2    0    1
MON   0    0    2

我必须为每年的每一天创建一个柜台吗？比如Sun2000，Sun2001等等？

Answer 1

你需要每天加入你的entry_date和枢轴的结果。

SQL Fiddle

查询

with x(days) as (
  select 'sunday' from dual union all
  select 'monday' from dual union all
  select 'tuesday' from dual union all
  select 'wednesday' from dual union all
  select 'thursday' from dual union all
  select 'friday' from dual union all
  select 'saturday' from dual
)  
select * from (
  select x.days,
     extract(year from emp.entry_date) entry_year
  from x left outer join emp
  on x.days = to_char(emp.entry_date,'fmday')
 )
pivot(count(entry_year)
    for entry_year in (
    2007,
    2008,
    2009,
    2010,
    2011,
    2012
    )
)
order by 
    case days when 'sunday' then 1
        when'monday' then 2
        when'tuesday' then 3
        when'wednesday' then 4
        when'thursday' then 5
        when'friday' then 6
        when'saturday' then 7
    end

结果

|      DAYS | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 |
|-----------|------|------|------|------|------|------|
|    sunday |    0 |    0 |    0 |    0 |    0 |    0 |
|    monday |    0 |    0 |    0 |    2 |    0 |    0 |
|   tuesday |    0 |    0 |    0 |    0 |    1 |    0 |
| wednesday |    0 |    0 |    0 |    1 |    2 |    1 |
|  thursday |    0 |    0 |    0 |    0 |    0 |    3 |
|    friday |    0 |    0 |    0 |    0 |    0 |    0 |
|  saturday |    0 |    0 |    0 |    0 |    0 |    0 |

Answer 2

您需要在year和entry_date上使用year和entry_date来获取每个日期加入的员工数量。

例如：

Rem -- Assuming following table structure
create table People(id number, name varchar2(20), entry_date date);

Rem -- Following groups the results
select extract(year from entry_date) "Year", entry_date, count(id) 
from People
where extract(year from entry_date) between 2008 and 2015 
group by extract(year from entry_date), entry_date
order by extract(year from entry_date), entry_date;

请查看这 sqlfiddle以了解更多内容。

Answer 3

根据您使用的Oracle版本(例如，10g没有PIVOT函数)，您可能会尝试以下条件聚合：

SELECT day_abbrev
     , SUM(CASE WHEN year_num = 2000 THEN person_cnt ELSE 0 END) AS "2000"
     , SUM(CASE WHEN year_num = 2001 THEN person_cnt ELSE 0 END) AS "2001"
     , SUM(CASE WHEN year_num = 2002 THEN person_cnt ELSE 0 END) AS "2002"
     , SUM(CASE WHEN year_num = 2003 THEN person_cnt ELSE 0 END) AS "2003"
     , SUM(CASE WHEN year_num = 2004 THEN person_cnt ELSE 0 END) AS "2004"
     , SUM(CASE WHEN year_num = 2005 THEN person_cnt ELSE 0 END) AS "2005"
     , SUM(CASE WHEN year_num = 2006 THEN person_cnt ELSE 0 END) AS "2006"
  FROM (
    SELECT TO_CHAR(entry_date, 'DY') AS day_abbrev
         , EXTRACT(YEAR FROM entry_date) AS year_num
         , COUNT(*) AS person_cnt
      FROM people
     GROUP BY TO_CHAR(entry_date, 'DY'), EXTRACT(YEAR FROM entry_date)
) GROUP BY day_abbrev
 ORDER BY TO_CHAR(NEXT_DAY(SYSDATE, day_abbrev), 'D');