MySQL-使用默认值连接

要获得该输出,您需要创建一个 CROSS JOIN 你的 tipe 值,其中每个日期都不同 request 表:

SELECT r.date, t.tipe
FROM (SELECT 'Menunggu Approve Atasan' AS tipe UNION
      SELECT 'Sudah Approved Atasan' UNION 
      SELECT 'Dalam Proses Pengerjaan IT' UNION 
      SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION 
      SELECT 'Ditutup'
     ) t
CROSS JOIN 
    (SELECT DISTINCT DATE(tanggal_permintaan) AS date 
     FROM request
     WHERE YEAR(tanggal_permintaan) = 2018) r

这将为您提供一张如下所示的桌子:

| date               | tipe                                  |
+--------------------+---------------------------------------+
| 2018-01-02         | Menunggu Approve Atasan               |
| 2018-01-02         | Sudah Approved Atasan                 |
| 2018-01-02         | Dalam Proses Pengerjaan IT            |
| 2018-01-02         | Sudah Selesai Tapi Belum Ditutup User |
| 2018-01-02         | Ditutup                               |
| 2018-01-03         | Menunggu Approve Atasan               |
| 2018-01-03         | Sudah Approved Atasan                 |
| 2018-01-03         | Dalam Proses Pengerjaan IT            |
| 2018-01-03         | Sudah Selesai Tapi Belum Ditutup User |
| 2018-01-03         | Ditutup                               |

那你可以 LEFT JOIN 对你的 requests 表以获得所需的结果:

SELECT s.date, s.tipe, COUNT(*) AS total
FROM (SELECT r.date, t.tipe
      FROM (SELECT 'Menunggu Approve Atasan' AS tipe UNION
            SELECT 'Sudah Approved Atasan' UNION 
            SELECT 'Dalam Proses Pengerjaan IT' UNION 
            SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION 
            SELECT 'Ditutup'
           ) t
      CROSS JOIN 
          (SELECT DISTINCT DATE(tanggal_permintaan) AS date 
           FROM request
           WHERE YEAR(tanggal_permintaan) = 2018) r
     ) s
LEFT JOIN request rq ON rq.status = s.tipe
GROUP BY s.date, s.tipe

• 我们可以在派生表中获得2018年的所有唯一日期。 Year() 函数不允许使用索引。我把它改成了 Between.. 这样它就可以使用索引了。
• 同样,在一个单独的派生表中获取所有唯一的状态。
• Cross Join 在它们之间找到所有可能的组合。
• 现在,用这个 all_combinations 和 left join 对于你的主要问题, 在…上 status 和 date ,以获得所需的结果集。

尝试以下方法:

SELECT all_combinations.tanggal_permintaan, 
       all_combinations.tipe, 
       COALESCE(requests.total, 0) AS total 
FROM 
(
  SELECT all_tanggal_permintaan.tanggal_permintaan, 
         all_tipe.tipe 
  FROM 
  (SELECT DISTINCT DATE(tanggal_permintaan) as tanggal_permintaan
   FROM request 
   WHERE tanggal_permintaan BETWEEN '2018-01-01 00:00:00' AND 
                                    '2018-12-31 23:59:59'
  ) AS all_tanggal_permintaan 

  CROSS JOIN 

  (SELECT 'Menunggu Approve Atasan' AS tipe UNION ALL
   SELECT 'Sudah Approved Atasan' UNION ALL
   SELECT 'Dalam Proses Pengerjaan IT' UNION ALL
   SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION ALL
   SELECT 'Ditutup'
  ) AS all_tipe
) AS all_combinations 

LEFT OUTER JOIN

(
  SELECT
    DATE(tanggal_permintaan) as tanggal_permintaan,
    status,
    COUNT(*) AS total 
  FROM request
  WHERE tanggal_permintaan BETWEEN '2018-01-01 00:00:00' AND 
                                   '2018-12-31 23:59:59'
  GROUP BY DATE(tanggal_permintaan), status
) AS requests 
ON requests.tanggal_permintaan = all_combinations.tanggal_permintaan AND 
   requests.status = all_combinations.tipe

ORDER BY all_combinations.tanggal_permintaan

这是一个猜测,但似乎您需要一个状态值列表,这让我惊讶,它还没有在表中,但这可能会起作用:

SELECT
    g.tanggal_permintaan, g.total, s.status
FROM (
    SELECT DISTINCT
        request.status
    FROM request
) s
LEFT JOIN (
        SELECT
            DATE(request.tanggal_permintaan) AS tanggal_permintaan
           ,request.status
           ,COUNT(*)                         AS total
        FROM request
        WHERE request.tanggal_permintaan) >= '20180101'
          AND request.tanggal_permintaan) <  '20190101'
        GROUP BY
            DATE(request.tanggal_permintaan)
           ,request.status
    ) g
    ON s.status = g.status

感谢兄弟@Nick和@Used_已经被_使用了

这是我的最后一个问题。 我创建了一个基于唯一id的concat,通过左连接将它们连接起来。

SELECT standard.*, transactions.* FROM (
    SELECT CONCAT(r.date, '#', t.tipe) as 'id', r.date AS tanggal_permintaan, t.tipe
    FROM (
         SELECT DISTINCT
             request.status AS tipe
         FROM request
    ) t
    CROSS JOIN (
        SELECT DISTINCT DATE(tanggal_permintaan) AS date 
        FROM request
        WHERE YEAR(request.tanggal_permintaan) = '2018' 
    ) r

) AS standard

LEFT JOIN (
    SELECT CONCAT(requests.tanggal_permintaan, '#' , requests.status) AS 'id', requests.tanggal_permintaan, requests.status, requests.total 
    FROM (
        SELECT
            DATE(request.tanggal_permintaan) as tanggal_permintaan,
            request.`status`,
            COUNT(*) as total
        FROM request
        WHERE YEAR(request.tanggal_permintaan) = '2018'
        GROUP BY DATE(request.tanggal_permintaan), request.status
    ) requests
) AS transactions
ON transactions.id = standard.id

ORDER BY standard.id