SQL为每个唯一ID选择值最高的行

Question

edited 18/02/2021

这里是信息

<<p>列描述/strong>

1. orderNumber:长度正好为10个字符的字符串。第一个字符为字母，最后一个字符为a、G、H、l，中间字符为数字。
2. datetimeVisit:日期不能晚于当前日期的日期和时间，时间在上午11点到下午2点59分和下午5点到9点59分之间。
3. tablennumber 1 ~ 10之间的整数
4. numPax:非负整数。
5. orderItemNo:一个整数
6. dishId:一个自动生成的值，从1开始，增量为1。一个值，可以是小、中、大、标准价格。一个非负的十进制值，默认值为9.90
7. disname:长度不超过30个字符的值
8. size:小、中、大、标准的值
9. 价格:非负十进制值，默认值为9.90
10. disname:长度不超过30个字符的值

外键约束

• FoodOrder和OrderItem之间的最小基数是1:N, M:M，删除和更新级联。
• MenuItemSize和OrderItem之间的最小基数是1:N, M:O与delete不允许，更新级联。
• MenuItem和MenuItemSize之间的最小基数是1:N, M:M与delete和更新无效。

问题:

• 创建一个视图OrderSummary，其中包含详细信息，包括时隙订购项目的数量，订购项目的总成本，杂项收费是每位顾客2.5美元，总金额是项目成本及杂项费用。在视图中显示行。

CREATE VIEW ORDERSUMMARY AS
SELECT DISTINCT FO.orderNumber, FO.datetimeVisit, FO.tableNumber, FO.numPax,
CASE
WHEN CAST(datetimeVisit AS TIME) BETWEEN '11:00:00' AND '11:30:00' THEN '11:00 to 11.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '11:31:00' AND '11:59:00' THEN '11:31 to 11.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '12:00:00' AND '12:30:00' THEN '12:00 to 12.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '12:31:00' AND '12:59:00' THEN '12:31 to 12.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '13:00:00' AND '13:30:00' THEN '13:00 to 13:30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '13:31:00' AND '13:59:00' THEN '13:31 to 13:59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '14:00:00' AND '14:59:00' THEN '14:00 to 14.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '14:31:00' AND '14:59:00' THEN '14:31 to 14.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '17:00:00' AND '17:30:00' THEN '17:00 to 17.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '17:31:00' AND '17:59:00' THEN '17:31 to 17.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '18:00:00' AND '18:30:00' THEN '18:00 to 18.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '18:31:00' AND '18:59:00' THEN '18:31 to 18.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '19:00:00' AND '19:30:00' THEN '19:00 to 19.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '19:31:00' AND '19:59:00' THEN '19:31 to 19.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '20:00:00' AND '20:30:00' THEN '20:00 to 20.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '20:31:00' AND '20:59:00' THEN '20:31 to 20.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '21:00:00' AND '21:30:00' THEN '21:00 to 21.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '21:31:00' AND '21:59:00' THEN '21:31 to 21.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '22:00:00' AND '22:30:00' THEN '22:00 to 22.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '22:31:00' AND '22:59:00' THEN '22:31 to 22.59'
ELSE 'Invalid timeslot' 
END AS timeslot,
OI.orderItemNo AS NumberOfItems, OI.orderItemNo*price AS FoodAmount, 2.50*numPax AS MiscCharge, (OI.orderItemNo*price + 2.50*numPax) AS TotalAmount
FROM FOODORDER AS FO
LEFT JOIN ORDERITEM AS OI
ON FO.orderNumber = OI.orderNumber
LEFT JOIN MENUITEMSIZE AS MIS
ON MIS.dishID = OI.dishID AND MIS.size = OI.size

如何从每个orderNumber中获得最高orderItemNo的一行?

Answer 1

我创建了一个更简单的例子，我将尝试向您解释您需要做什么。下面是代码:

SELECT T1.orderNumber
, MAX(orderItemNo)
, CASE WHEN CAST(datetimeVisit AS TIME) BETWEEN '11:00:00' AND '11:30:00' THEN '11:00 to 11.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '11:31:00' AND '11:59:00' THEN '11:31 to 11.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '12:00:00' AND '12:30:00' THEN '12:00 to 12.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '12:31:00' AND '12:59:00' THEN '12:31 to 12.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '13:00:00' AND '13:30:00' THEN '13:00 to 13:30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '13:31:00' AND '13:59:00' THEN '13:31 to 13:59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '14:00:00' AND '14:59:00' THEN '14:00 to 14.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '14:31:00' AND '14:59:00' THEN '14:31 to 14.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '17:00:00' AND '17:30:00' THEN '17:00 to 17.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '17:31:00' AND '17:59:00' THEN '17:31 to 17.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '18:00:00' AND '18:30:00' THEN '18:00 to 18.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '18:31:00' AND '18:59:00' THEN '18:31 to 18.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '19:00:00' AND '19:30:00' THEN '19:00 to 19.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '19:31:00' AND '19:59:00' THEN '19:31 to 19.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '20:00:00' AND '20:30:00' THEN '20:00 to 20.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '20:31:00' AND '20:59:00' THEN '20:31 to 20.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '21:00:00' AND '21:30:00' THEN '21:00 to 21.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '21:31:00' AND '21:59:00' THEN '21:31 to 21.59'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '22:00:00' AND '22:30:00' THEN '22:00 to 22.30'
WHEN CAST(datetimeVisit AS TIME) BETWEEN '22:31:00' AND '22:59:00' THEN '22:31 to 22.59'
ELSE 'Invalid timeslot' 
END timeslot
FROM T1
LEFT JOIN T2 ON T1.orderNumber = T2.orderNumber
GROUP BY T1.orderNumber, T2.datetimeVisit
ORDER BY T1.orderNumber;      

下面是一个示例:演示

您需要为每个orderNumber选择'max(orderItemNo)'。这就是为什么我在代码的select子句中添加了'max(orderItemNo)'。

在您选择了max(orderItemNo)之后，您需要添加group by子句，您将添加所选择的所有列。在我的例子中，我必须在group by子句中添加orderNumber和datetimeVisit。

在group by子句之后，我还添加了order by子句，只是为了更清楚地表示查询的结果。