select a.`創建日期`,b.`訂單數`,c.結算數,d.失效數,e.付款數
FROM
(SELECT
tkorder.`創建日期`
FROM
tkorder
group by tkorder.`創建日期`) AS a
LEFT JOIN (
SELECT
tkorder.`創建日期`,
count(*) 訂單數
FROM
tkorder
group by tkorder.`創建日期`
) AS b ON a.`創建日期`= b.`創建日期`
LEFT JOIN (
SELECT
tkorder.`創建日期`,
count(*) 結算數
FROM
tkorder where `訂單狀態`='訂單結算'
group by tkorder.`創建日期`
) AS c ON a.`創建日期`= c.`創建日期`
LEFT JOIN (
SELECT
tkorder.`創建日期`,
count(*) 失效數
FROM
tkorder where `訂單狀態`='訂單失效'
group by tkorder.`創建日期`
) AS d ON a.`創建日期`= d.`創建日期`