mysql - 统计前值数据

Question

我有一张工作单表

Create table joborder (
   jo_no number,
   jo_date date,
   jo_status char(10)
);

insert into joborder values( 1,'01-03-13','A');
insert into joborder values( 2,'01-03-13','A');
Insert into joborder values (3,'01-03-13','A');
insert into joborder values( 4,'04-03-13','A');
insert into joborder values(5,'08-03-13','B');
insert into joborder values( 6,'12-03-13','C');

状态:

• A表示完成
• B代表进步
• C 表示开发

我要的报告如下，请不要使用解析函数，因为我要这个条件使用基本条件函数

Date         totalJoborder    A      B     C
---------------------------------------------
01-03-13      3               3      0     0
04-03-13      4               3      1     0
08-03-13      5               3      1     1
12-03-13      6               4      1     1

Answer 1

也许这就是您需要的...？

WITH job_order AS
         (SELECT 1 jo_no,
                 to_date ('01-03-13', 'dd-mm-yy') jo_date,
                 'A' jo_status
            FROM dual
          UNION ALL
          SELECT 2, to_date ('01-03-13', 'dd-mm-yy'), 'A' FROM dual
          UNION ALL
          SELECT 3, to_date ('01-03-13', 'dd-mm-yy'), 'A' FROM dual
          UNION ALL
          SELECT 4, to_date ('04-03-13', 'dd-mm-yy'), 'A' FROM dual
          UNION ALL
          SELECT 5, to_date ('08-03-13', 'dd-mm-yy'), 'B' FROM dual
          UNION ALL
          SELECT 6, to_date ('12-03-13', 'dd-mm-yy'), 'C' FROM dual),
     my_cte AS
         (  SELECT jo_date,
                   count (jo_status) AS total_job_order,
                   count (CASE jo_status WHEN 'A' THEN 1 ELSE NULL END) AS A,
                   count (CASE jo_status WHEN 'B' THEN 1 ELSE NULL END) AS B,
                   count (CASE jo_status WHEN 'C' THEN 1 ELSE NULL END) AS C
              FROM job_order
          GROUP BY jo_date)
  SELECT c.jo_date,
         (SELECT sum (c1.total_job_order)
            FROM my_cte c1
           WHERE c1.jo_date <= c.jo_date)
             AS total_job_order_2,
         (SELECT sum (c1.a)
            FROM my_cte c1
           WHERE c1.jo_date <= c.jo_date)
             AS a,
         (SELECT sum (c1.b)
            FROM my_cte c1
           WHERE c1.jo_date <= c.jo_date)
             AS b,
         (SELECT sum (c1.c)
            FROM my_cte c1
           WHERE c1.jo_date <= c.jo_date)
             AS c
    FROM my_cte c
ORDER BY c.jo_date;

输出(基于您的示例数据):

JO_DATE       TOTAL_JOB_ORDER      A    B    C
1/03/2013     3                    3    0    0
4/03/2013     4                    4    0    0
8/03/2013     5                    4    1    0
12/03/2013    6                    4    1    1