时间戳

首页 > 解决方案 > 尝试仅输出总额超过 2000 美元的预订

sql - 尝试仅输出总额超过 2000 美元的预订

问题描述

SELECT 
    reservationWH.RNUM AS Reservation, 
    packageWH.PNAME AS "Package Name", 
    (COUNT(guestWH.CELL_PHONE) * packageWH.COSTPERSON) AS "Total Cost"
FROM 
    packageWH 
INNER JOIN 
    reservationWH ON packageWH.P_ID = reservationWH.P_ID 
INNER JOIN 
    guestWH ON reservationWH.RNUM = guestWH.RNUM
WHERE 
    (COUNT(guestWH.CELL_PHONE)) * packageWH.COSTPERSON > 2000
GROUP BY 
    reservationWH.RNUM
ORDER BY 
    reservationWH.RNUM ASC

我收到此错误：

聚合可能不会出现在 WHERE 子句中，除非它位于 HAVING 子句或选择列表中包含的子查询中，并且被聚合的列是外部引用。

标签： sqlsql-servertsqlhaving-clause

解决方案

由于无法访问数据，也无法看到想要的结果，解决这个问题就像一场猜谜游戏。也许您需要将分组移动到子查询中，这样您就可以使用 where 子句，例如

SELECT
    reservationWH.RNUM                   AS Reservation
  , packageWH.PNAME                      AS "Package Name"
  , (g.num_cells * packageWH.COSTPERSON) AS "Total Cost"
FROM packageWH
INNER JOIN reservationWH ON packageWH.P_ID = reservationWH.P_ID
INNER JOIN (
        SELECT
            RNUM
          , COUNT( CELL_PHONE ) num_cells
        FROM guestWH
        GROUP BY
            RNUM
    ) g ON reservationWH.RNUM = g.RNUM
WHERE (g.num_cells * packageWH.COSTPERSON) > 2000
ORDER BY
    reservationWH.RNUM ASC

但是，如果没有样本数据和预期结果，我无法验证。

推荐阅读