mysql - 如何在一行中分组和计数？

Question

为了简化，我有树表:products、products-vs-orders、orders

1. products 字段:“ProductID”、“Name”、“isGratis”...
2. products-vs-orders 字段:“ProductID”、“OrderID”
3. 订单 字段:'OrderID'、'Title' ...

实际上，我有这样的查询:

SELECT orders.OrderID, orders.Title, COUNT(`products`.`isGratis`) AS "Quantity", `products`.`isGratis`
FROM `orders`, `products-vs-orders`, `products`
WHERE `orders`.`OrderID` = `products-vs-orders`.`OrderID` AND `products-vs-orders`.`ProductID` = `products`.`ProductID`
GROUP BY `products`.`PackID`, `products`.`isGratis`

此查询有效并返回以下结果:

OrderID,    Title,      Quantity,   isGratis
1            My Order      20           0
1            My Order      3            1
2            An other      8            0
2            An other      1            1

如何将“免费”和“付费”产品的数量检索到单独的列中？

OrderID,    Title,      Qt Paid,        Qt Gratis
1            My Order       20              3
2            An other       8               1

Answer 1

试试这个:

SELECT
    orders.OrderID,
    orders.Title, 
    COUNT(orders.OrderId) - SUM(`products`.`isGratis`) AS "Qt Paid", 
    SUM(`products`.`isGratis`) AS "Qt Gratis" 
WHERE `orders`.`OrderID` = `products-vs-orders`.`OrderID`
  AND `products-vs-orders`.`ProductID` = `products`.`ProductID`
GROUP BY `products`.`PackID`