程式橫向總月銷售額

我正在嘗試根據程式名稱按月生成 2021 年的月度總銷售額。每行必須包含程式名稱和月份名稱，如果任何月份有任何銷售，則必須輸出銷售總額，否則為零。到目前為止，我管理了我正在尋找的顯示程式名稱和月份的內容，但是銷售額顯示當年總銷售額的月份，

SELECT 
    tt.procedure_name,
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'January', 1, 0)) AS 'January',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'February', 1, 0)) AS 'February',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'March', 1, 0)) AS 'March',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'April', 1, 0)) AS 'April',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'May', 1, 0)) AS 'May',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'June', 1, 0)) AS 'June',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'July', 1, 0)) AS 'July',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'August', 1, 0)) AS 'August',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'September', 1, 0)) AS 'September',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'October', 1, 0)) AS 'October',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'November', 1, 0)) AS 'November',
    SUM(IF(DATE_FORMAT(mt.plan_date, '%M') = 'December', 1, 0)) AS 'December'
FROM 
    treatments tt
        LEFT JOIN (
            SELECT 
                plan_date,procedure_name
            FROM 
                treatments
            WHERE 
                YEAR(plan_date) = 2021 
            GROUP BY 
                DATE_FORMAT(plan_date, '%M'),procedure_name
            ORDER BY 
                plan_date ASC,
                procedure_name ASC 
        ) mt ON mt.procedure_name = tt.procedure_name
WHERE 
    YEAR(tt.plan_date) = 2021 
GROUP BY 
    tt.procedure_name 
ORDER BY 
    tt.procedure_name ASC 

錯誤的表：

|procedure_name|January|February|March|April|May|June|July|August|September|October|November|December|
|--------------|-------|--------|-----|-----|---|----|----|------|---------|-------|--------|--------|
|Botox         |5      |0       |0    |0    |0  |0   |0   |0     |0        |0      |0       |5       |
|Fillers       |2      |0       |2    |0    |0  |0   |0   |0     |0        |0      |0       |0       |

正確的表格應該是：

|procedure_name|January|February|March|April|May|June|July|August|September|October|November|December|
|--------------|-------|--------|-----|-----|---|----|----|------|---------|-------|--------|--------|
|Botox         |2      |0       |0    |0    |0  |0   |0   |0     |0        |0      |0       |3       |
|Fillers       |1      |0       |1    |0    |0  |0   |0   |0     |0        |0      |0       |0       |

uj5u.com熱心網友回復：

為什么是自外連接？我想你只是想要：

SELECT 
    tt.procedure_name,
    SUM(MONTH(tt.plan_date) =  1) AS "January",
    SUM(MONTH(tt.plan_date) =  2) AS "February",
    SUM(MONTH(tt.plan_date) =  3) AS "March",
    SUM(MONTH(tt.plan_date) =  4) AS "April",
    SUM(MONTH(tt.plan_date) =  5) AS "May",
    SUM(MONTH(tt.plan_date) =  6) AS "June",
    SUM(MONTH(tt.plan_date) =  7) AS "July",
    SUM(MONTH(tt.plan_date) =  8) AS "August",
    SUM(MONTH(tt.plan_date) =  9) AS "September",
    SUM(MONTH(tt.plan_date) = 10) AS "October",
    SUM(MONTH(tt.plan_date) = 11) AS "November",
    SUM(MONTH(tt.plan_date) = 12) AS "December"
FROM 
    treatments tt
WHERE 
    YEAR(tt.plan_date) = 2021 
GROUP BY 
    tt.procedure_name 
ORDER BY 
    tt.procedure_name ASC;

如果您想包括在 2021 年任何月份都沒有銷售的治療：

SELECT 
    p.procedure_name,
    SUM(MONTH(tt.plan_date) =  1) AS "January",
    SUM(MONTH(tt.plan_date) =  2) AS "February",
    SUM(MONTH(tt.plan_date) =  3) AS "March",
    SUM(MONTH(tt.plan_date) =  4) AS "April",
    SUM(MONTH(tt.plan_date) =  5) AS "May",
    SUM(MONTH(tt.plan_date) =  6) AS "June",
    SUM(MONTH(tt.plan_date) =  7) AS "July",
    SUM(MONTH(tt.plan_date) =  8) AS "August",
    SUM(MONTH(tt.plan_date) =  9) AS "September",
    SUM(MONTH(tt.plan_date) = 10) AS "October",
    SUM(MONTH(tt.plan_date) = 11) AS "November",
    SUM(MONTH(tt.plan_date) = 12) AS "December"
FROM 
  (SELECT DISTINCT procedure_name FROM treatments) p
LEFT OUTER JOIN
    treatments tt 
      ON tt.procedure_name = p.procedure_name
      AND YEAR(tt.plan_date) = 2021 
GROUP BY 
    p.procedure_name 
ORDER BY 
    p.procedure_name ASC;

更新：你說你有一個程式表。在這種情況下，我建議您使用它并加入聚合資料：

SELECT *
FROM procedure p
LEFT JOIN
(
  SELECT 
      procedure_name,
      SUM(MONTH(plan_date) =  1) AS "January",
      SUM(MONTH(plan_date) =  2) AS "February",
      SUM(MONTH(plan_date) =  3) AS "March",
      SUM(MONTH(plan_date) =  4) AS "April",
      SUM(MONTH(plan_date) =  5) AS "May",
      SUM(MONTH(plan_date) =  6) AS "June",
      SUM(MONTH(plan_date) =  7) AS "July",
      SUM(MONTH(plan_date) =  8) AS "August",
      SUM(MONTH(plan_date) =  9) AS "September",
      SUM(MONTH(plan_date) = 10) AS "October",
      SUM(MONTH(plan_date) = 11) AS "November",
      SUM(MONTH(plan_date) = 12) AS "December"
  FROM treatments
  WHERE YEAR(plan_date) = 2021 
  GROUP BY procedure_name
) t ON t.procedure_name = p.procedure_name
ORDER BY p.procedure_name;

標籤：

上一篇：MySql表中的問題、收據和余額

下一篇：Javaswing淡入/淡出效果作為UI過渡緩沖區？