mysql - 'SubQuery 返回多于一行' 用例 When

Question

当有两个成人和一个 child 进行预订时，我似乎很难为用户提供折扣……输出文本如标题所示。虽然我知道这个问题，但我不确定如何解决它才能使结果更加独特。

作为引用，价格来自不同的表格，但其他一切都在预订中。

感谢任何帮助并提前致谢

UPDATE BOOKING SET TotalCost=
(SELECT SUM(

CASE WHEN NumAdults = 2 AND NumChilds = 1 THEN
(NumAdults*(SELECT Cost FROM prices WHERE AgeType = 'Adult'))
+
(NumChilds*(SELECT Cost FROM prices WHERE AgeType = 'Child' / 2))


ELSE
(NumAdults*(SELECT Cost FROM prices WHERE AgeType = 'Adult'))
+
(NumChilds*(SELECT Cost FROM prices WHERE AgeType = 'Child'))
END
))
WHERE BookID  = 1

Answer 1

基本修复是在子查询中执行 SUM:-

UPDATE BOOKING SET TotalCost=
(
    CASE 
        WHEN NumAdults = 2 AND NumChilds = 1 
        THEN
            (NumAdults * (SELECT SUM(Cost) FROM prices WHERE AgeType = 'Adult'))
            +
            (NumChilds * (SELECT SUM(Cost) FROM prices WHERE AgeType = 'Child' / 2))

        ELSE
            (NumAdults * (SELECT SUM(Cost) FROM prices WHERE AgeType = 'Adult'))
            +
            (NumChilds * (SELECT SUM(Cost) FROM prices WHERE AgeType = 'Child'))
    END
)
WHERE BookID  = 1

但不相信如果我们能看到表描述和一些示例数据就没有更好的方法来完成整个查询

例如，如果每个成人和 child 的价格都是唯一的，那么可以这样做:-

UPDATE BOOKING a
LEFT OUTER JOIN prices pa ON pa.AgeType = 'Adult'
LEFT OUTER JOIN prices pc ON pc.AgeType = 'Child'
SET TotalCost = CASE 
                    WHEN NumAdults = 2 AND NumChilds = 1 
                    THEN NumAdults * pa.Cost + (NumChilds * pc.Cost / 2)
                    ELSE NumAdults * pa.Cost + NumChilds * pc.Cost
                END
WHERE BookID  = 1