我正试图在一个查询中从一个表中获得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
相当简单：
SELECT COUNT(*) as total, orderdate
FROM orders
WHERE (win = 0 OR win = 1 OR win = 2) AND endprice != ""
GROUP BY orderdate
ORDER BY starttime DESC