将行更改为列并计数

Question

如何根据行计算计数？

源表 每个员工可以休假 2 天

Employee-----First_Day_Off-----Second_Day_Off
1------------10/21/2009--------12/6/2009
2------------09/3/2009--------12/6/2009
3------------09/3/2009--------NULL
4
5
.
.
.

现在我需要一个表格来显示当天休假的日期和人数

Date---------First_Day_Off-------Second_Day_Off
10/21/2009---1-------------------0
12/06/2009---1--------------------1
09/3/2009----2--------------------0

有什么想法吗？

Answer 1

Oracle 9i+，使用子查询分解 (WITH)：

WITH sample AS (
   SELECT a.employee,
          a.first_day_off AS day_off,
          1 AS day_number
     FROM YOUR_TABLE a
    WHERE a.first_day_off IS NOT NULL
   UNION ALL
   SELECT b.employee,
          b.second_day_off,
          2 AS day_number
     FROM YOUR_TABLE b
    WHERE b.second_day_off IS NOT NULL)
  SELECT s.day_off AS date,
         SUM(CASE WHEN s.day_number = 1 THEN 1 ELSE 0 END) AS first_day_off,
         SUM(CASE WHEN s.day_number = 2 THEN 1 ELSE 0 END) AS second_day_off
    FROM sample s
GROUP BY s.day_off

非子查询版本

  SELECT s.day_off AS date,
         SUM(CASE WHEN s.day_number = 1 THEN 1 ELSE 0 END) AS first_day_off,
         SUM(CASE WHEN s.day_number = 2 THEN 1 ELSE 0 END) AS second_day_off
    FROM (SELECT a.employee,
                 a.first_day_off AS day_off,
                 1 AS day_number
            FROM YOUR_TABLE a
           WHERE a.first_day_off IS NOT NULL
          UNION ALL
          SELECT b.employee,
                 b.second_day_off,
                 2 AS day_number
            FROM YOUR_TABLE b
           WHERE b.second_day_off IS NOT NULL) s
GROUP BY s.day_off

Answer 2

处理这些查询有点尴尬，因为您的休息日存储在不同的列中。更好的布局是这样的

EMPLOYEE_ID    DAY_OFF

然后，如果一名员工请了多天假，您将有多行

EMPLOYEE_ID    DAY_OFF
1              10/21/2009
1              12/6/2009
2              09/3/2009
2              12/6/2009
3              09/3/2009
...

在这种情况下，您可以使用以下查询找出每个人请了多少天假：

SELECT EMPLOYEE_ID, COUNT(*) AS NUM_DAYS_OFF FROM DAYS_OFF_TABLE GROUP BY EMPLOYEE_ID

以及请了多少天假每个日期的休息日如下：

SELECT DAY_OFF, COUNT(*) AS NUM_PEOPLE FROM DAYS_OFF_TABLE GROUP BY DAY_OFF

但我离题了...

您可以尝试使用 SQL CASE 语句来帮助解决这个问题：（

SELECT Employee, CASE
    WHEN First_Day_Off is NULL AND Second_Day_Off is NULL THEN 0
    WHEN First_Day_Off is NOT NULL AND Second_Day_Off is NULL THEN 1
    WHEN First_Day_Off is NULL AND Second_Day_Off is NOT NULL THEN 1
    ELSE 2
    END AS NUM_DAYS_OFF
FROM DAYS_OFF_TABLE

请注意，您可能需要根据您的数据库稍微更改语法。

获取当天起飞的日期和人数可能会更复杂。

我不知道这是否会工作，但你可以尝试一下：

SELECT
    Date_Off,
    COUNT(*) AS Num_People
FROM
    (SELECT
        First_Day_Off, COUNT(*) AS Num_People FROM DAYS_OFF_TABLE WHERE First_Day_Off IS NOT NULL GROUP BY First_Day_Off
    UNION
    SELECT Second_Day_Off, COUNT(*) AS Num_People FROM DAYS_OFF_TABLE WHERE Second_Day_Off IS NOT NULL GROUP BY Second_Day_Off)
GROUP BY
    Num_People