php - 在1个查询mysqli中从1个表中选择3种不同的总计

我试图在一个查询中从一张表中获得3个总数，这可能吗？我现在处于这个位置，我试图加入那些也没有用的人。

$queryfr = '(SELECT COUNT(*) as toA, orderdate FROM orders WHERE win = 0 && endprice != "" GROUP BY orderdate ORDER BY starttime DESC)
            UNION
            (SELECT COUNT(*) as toB, orderdate FROM orders WHERE win = 1 && endprice != "" GROUP BY orderdate ORDER BY starttime DESC)
            UNION
            (SELECT COUNT(*) as toC, orderdate FROM orders WHERE win = 2 && endprice != "" GROUP BY orderdate ORDER BY starttime DESC)';

if ($resultfr = $conn->query($queryfr)) {
    while ($fr = $resultfr->fetch_object()){
        echo $fr->orderdate.' - '.$fr->toA.' - '.$fr->toB.' - '.$fr->toC.' <br>';
    }
}

结果：

2015-12-07 - 1 - -
2015-12-08 - 4 - -
2015-12-09 - 1 - -
2015-12-10 - 1 - -
2015-12-11 - 5 - -
2015-12-14 - 1 - -
2015-12-17 - 1 - -
2015-12-23 - 12 - -
2015-12-24 - 1 - -
2015-12-27 - 3 - -
2015-12-28 - 11 - -
2015-12-29 - 2 - -
2015-12-30 - 6 - -
2015-12-31 - 6 - -
2016-01-07 - 4 - -
2016-01-12 - 3 - -
2015-12-21 - 1 - -
2015-12-23 - 5 - -
2015-12-24 - 5 - -
2015-12-27 - 1 - -
2015-12-28 - 14 - -
2015-12-30 - 4 - -
2015-12-31 - 4 - -
2016-01-02 - 1 - -
2015-12-08 - 2 - -
2015-12-23 - 7 - -
2015-12-24 - 7 - -
2015-12-27 - 8 - -
2015-12-28 - 9 - -
2015-12-29 - 1 - -
2015-12-30 - 2 - -
2015-12-31 - 5 - -

我想获取$fr->toB和$fr->toC的值

最佳答案

效率不是很高，但是应该可以解决问题：

SELECT
    tbl1.orderdate,
    tbl1.toA,
    tbl2.toB,
    tbl3.toC
from
(
   SELECT COUNT(*) as toA, orderdate
   FROM orders WHERE win = 0 && endprice != ""
   GROUP BY orderdate ORDER BY starttime DESC
) tbl1
LEFT JOIN
(
   SELECT COUNT(*) as toB, orderdate
   FROM orders WHERE win = 1 && endprice != ""
   GROUP BY orderdate ORDER BY starttime DESC
) tbl2 on tbl1.orderdate = tbl2.orderdate
LEFT JOIN
(
   SELECT COUNT(*) as toC, orderdate
   FROM orders WHERE win = 2 && endprice != ""
   GROUP BY orderdate ORDER BY starttime DESC
) tbl3 on tbl1.orderdate = tbl3.orderdate