具有聚合函数的MYSQL CASE语句只返回一行

Question

我对这个查询有问题，它只返回一行。我正在使用位于此处的数据库：https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

SELECT CustomerName, TotalPrice,
(CASE TotalPrice
WHEN TotalPrice =  MIN(TotalPrice) THEN 'Lowest Sales Potential'
WHEN TotalPrice =  MAX(TotalPrice) THEN 'Highest Sales Potential'
WHEN TotalPrice > AVG(TotalPrice) THEN 'High Sales Potential'
ELSE 'Average Sales Potential'
END) AS Potential
FROM
(
SELECT CustomerName, SUM(Price*Quantity) AS TotalPrice
FROM Orders
INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
GROUP BY CustomerName
)

Answer 1

您需要的是from。。。首先是每个客户的总销售额，以及一个完全附加的查询，该查询只将min/max/avg作为一条记录返回。然后，您可以根据自己的需求进行比较。

为了获得每个客户的总数，还需要使用完全相同的方法来获得最低值、平均值和最大值，以便进行最终比较。所以这个过程我将与with CTE构造一起使用。CTE是一个常见的表表达式。因此，您可以在实际查询之前编写一个查询，并且该别名将在最终查询中被识别。这样你就不必每次都把整个查询写下来。。。您很快就会看到。

此外，我正在申请"；别名"；名称添加到查询中以缩短可读性。将所有部分标识为table.column或alias.column也很好，这样人们就知道数据来自哪个表，而不知道你的结构。我假设CustomerName来自customer表，并且我使用别名"；c"；，至于价格和数量，我也假设它们来自订单详细信息，因为库存的价格可能会发生变化，但产品本身是相对静态的，是一次性输入。因此，Price和Quantity使用"；od"；OrderDetail中的别名。如果您的表结构在产品表中有价格，只需将别名引用更改为"；p"；。

我还发现缩进可以查看表的来源，链接可以帮助查看查询的哪个部分依赖于另一部分的各个组件，希望它也能对您有所帮助。

WITH SumByCustomerQry as
( SELECT 
c.CustomerName, 
SUM( od.Price * od.Quantity ) TotalPrice
FROM 
Orders o
JOIN OrderDetails od
ON o.OrderID = od.OrderID
JOIN Products p
ON od.ProductID = p.ProductID
JOIN Customers c
ON o.CustomerID = c.CustomerID
GROUP BY 
CustomerName 
) 
SELECT 
sbc.CustomerName, 
sbc.TotalPrice,
CASE when sbc.TotalPrice = agg.MinTotal THEN 'Lowest Sales Potential'
when sbc.TotalPrice = agg.MaxTotal THEN 'Highest Sales Potential'
when sbc.TotalPrice > agg.AvgTotal THEN 'Above Average Sales'
else 'below average Sales Potential'
END Potential
FROM
-- first, getting all individual customers
SumByCustomerQry sbc
-- now, we need the same pre-aggregated result to get the min/max/avg
JOIN
( select 
min( TotalPrice ) MinTotal,
max( TotalPrice ) MaxTotal,
avg( TotalPrice ) AvgTotal
from
-- notice using the same pre-aggregate query
SumByCustomerQry ) Agg
-- the result "Agg" query will return 1 record, so 
-- just joining on 1=1 which is always true
on 1=1

Answer 2

如果你有MySQL 8.0(或MariaDB 10.2.2(或更好的版本，你可以使用CTE和窗口函数，如下所示：

没有数据的小提琴

WITH aggr AS (
SELECT CustomerName
, SUM(Price*Quantity) AS TotalPrice
FROM Orders
JOIN OrderDetails
ON OrderDetails.OrderID = Orders.OrderID
JOIN Products  ON OrderDetails.ProductID = Products.ProductID
JOIN Customers ON Orders.CustomerID      = Customers.CustomerID
GROUP BY CustomerName
)
SELECT CustomerName
, TotalPrice
, CASE WHEN TotalPrice = MIN(TotalPrice) OVER w THEN 'Lowest Sales Potential'
WHEN TotalPrice = MAX(TotalPrice) OVER w THEN 'Highest Sales Potential'
WHEN TotalPrice > AVG(TotalPrice) OVER w THEN 'High Sales Potential'
ELSE 'Average Sales Potential'
END AS Potential
FROM aggr
WINDOW w AS ()
ORDER BY TotalPrice DESC, CustomerName
;

结果：

+------------------------------------+------------+-------------------------+
| CustomerName                       | TotalPrice | Potential               |
+------------------------------------+------------+-------------------------+
| Ernst Handel                       |   35631.21 | Highest Sales Potential |
| Mère Paillarde                    |   23362.60 | High Sales Potential    |
| Save-a-lot Markets                 |   22500.06 | High Sales Potential    |
| Rattlesnake Canyon Grocery         |   18421.42 | High Sales Potential    |
| QUICK-Stop                         |   18178.80 | High Sales Potential    |
| Queen Cozinha                      |   17880.60 | High Sales Potential    |
| Piccolo und mehr                   |   16040.75 | High Sales Potential    |
| Hungry Owl All-Night Grocers       |   15391.02 | High Sales Potential    |
| Blondel père et fils              |   15253.75 | High Sales Potential    |
| Simons bistro                      |   14619.00 | High Sales Potential    |
| Frankenversand                     |   13384.32 | High Sales Potential    |
| Split Rail Beer & Ale              |   11114.02 | High Sales Potential    |
| Old World Delicatessen             |   10744.50 | High Sales Potential    |
| Suprêmes délices                 |    8051.30 | High Sales Potential    |
| Bottom-Dollar Marketse             |    7963.75 | High Sales Potential    |
| Seven Seas Imports                 |    7438.70 | High Sales Potential    |
| LILA-Supermercado                  |    7358.68 | High Sales Potential    |
| Richter Supermarkt                 |    5875.75 | High Sales Potential    |
| Wartian Herkku                     |    5872.50 | High Sales Potential    |
| Berglunds snabbköp                |    5406.90 | High Sales Potential    |
| Bon app'                           |    5256.35 | High Sales Potential    |
| Eastern Connection                 |    5017.09 | Average Sales Potential |
| Die Wandernde Kuh                  |    4841.90 | Average Sales Potential |
| La maison d'Asie                   |    4826.75 | Average Sales Potential |
| White Clover Markets               |    4416.00 | Average Sales Potential |
| Lehmanns Marktstand                |    4384.45 | Average Sales Potential |
| HILARIÓN-Abastos                  |    4320.50 | Average Sales Potential |
| Folk och fä HB                    |    4313.90 | Average Sales Potential |
| Tortuga Restaurante                |    4269.40 | Average Sales Potential |
| Hanari Carnes                      |    4073.25 | Average Sales Potential |
| Que Delícia                        |    4049.10 | Average Sales Potential |
| Tradição Hipermercados           |    3949.25 | Average Sales Potential |
| Vaffeljernet                       |    3251.85 | Average Sales Potential |
| Magazzini Alimentari Riuniti       |    3240.90 | Average Sales Potential |
| Wellington Importadora             |    3020.00 | Average Sales Potential |
| Comércio Mineiro                  |    2713.85 | Average Sales Potential |
| Princesa Isabel Vinhoss            |    2514.00 | Average Sales Potential |
| Chop-suey Chinese                  |    2249.00 | Average Sales Potential |
| Königlich Essen                   |    2078.75 | Average Sales Potential |
| Folies gourmandes                  |    2030.20 | Average Sales Potential |
| Around the Horn                    |    1723.75 | Average Sales Potential |
| Familia Arquibaldo                 |    1658.20 | Average Sales Potential |
| Furia Bacalhau e Frutos do Mar     |    1656.00 | Average Sales Potential |
| Ricardo Adocicados                 |    1596.00 | Average Sales Potential |
| Godos Cocina Típica                |    1555.00 | Average Sales Potential |
| Morgenstern Gesundkost             |    1503.60 | Average Sales Potential |
| Ottilies Käseladen                |    1492.50 | Average Sales Potential |
| GROSELLA-Restaurante               |    1377.10 | Average Sales Potential |
| Santé Gourmet                     |    1323.60 | Average Sales Potential |
| Gourmet Lanchonetes                |    1275.00 | Average Sales Potential |
| Bólido Comidas preparadas         |    1227.50 | Average Sales Potential |
| Island Trading                     |    1127.00 | Average Sales Potential |
| Hungry Coyote Import Store         |    1104.25 | Average Sales Potential |
| Reggiani Caseifici                 |    1025.20 | Average Sales Potential |
| Victuailles en stock               |    1020.50 | Average Sales Potential |
| Romero y tomillo                   |     926.69 | Average Sales Potential |
| Lonesome Pine Restaurant           |     890.50 | Average Sales Potential |
| Pericles Comidas clásicas         |     851.16 | Average Sales Potential |
| Vins et alcools Chevalier          |     825.60 | Average Sales Potential |
| Consolidated Holdings              |     790.00 | Average Sales Potential |
| Toms Spezialitäten                |     710.50 | Average Sales Potential |
| Drachenblut Delikatessend          |     667.00 | Average Sales Potential |
| B's Beverages                      |     599.25 | Average Sales Potential |
| Galería del gastrónomo            |     593.00 | Average Sales Potential |
| Wolski                             |     573.75 | Average Sales Potential |
| Wilman Kala                        |     566.00 | Average Sales Potential |
| Antonio Moreno Taquería            |     504.00 | Average Sales Potential |
| LINO-Delicateses                   |     500.00 | Average Sales Potential |
| The Big Cheese                     |     421.00 | Average Sales Potential |
| Océano Atlántico Ltda.           |     399.00 | Average Sales Potential |
| Du monde entier                    |     336.00 | Average Sales Potential |
| Centro comercial Moctezuma         |     126.00 | Average Sales Potential |
| Ana Trujillo Emparedados y helados |     111.00 | Average Sales Potential |
| Franchi S.p.A.                     |      62.46 | Lowest Sales Potential  |
+------------------------------------+------------+-------------------------+
74 rows in set (0.004 sec)