错误"用作表达式的子查询返回多行"

Question

我有以下3个表：

机场

airport_id  name
1           Frankfurt
2           Paris
3           Amsterdam

区域

areaid    name    airport_id
1         name1   2
2         name2   2
3         name3   3
4         name4   3

预订

id      booking_date        price   commission  areaid
1       2022-09-1T10:00     70      12          1
2       2022-09-2T11:00     60      16          2
3       2022-09-2T20:00     50      15          3
4       2022-09-3T01:00     110     15          3
5       2022-09-10T22:00    90      14          4
6       2022-09-11T19:00    65      12          1
7       2022-09-20T12:00    84      16          2

我有这个查询

SELECT ar.name,
(SELECT (b.price * b.commission) AS com
FROM booking AS b
LEFT JOIN area AS p ON b.areaid = p.areaid
AND p.areaid = 3
AND (b.booking_date >= '2022-09-01T00:00' AND b.booking_date <= '2022-09-30T23:59:59')
)
FROM airport AS ar WHERE ar.airport_id = 2 

运行查询我得到错误：

more than one row returned by a subquery used as an expression

我不明白问题出在哪里。

我添加了一个SQL Fiddle来玩：http://sqlfiddle.com/#！17/8a09f/1

Answer 1

SELECT列表中的子查询只允许返回单个值。不是多行，也不是多列。如果您想要其中的任何一个，标准替换是LATERAL子查询。参见：

• PostgreSQL中的LATERAL JOIN和子查询有什么区别

查询更严重的问题是它根本没有意义。

这可能是您想要的，返回给定机场和给定区域的所有佣金列表(如果有的话(：

SELECT ar.name AS airport, b.com
FROM   airport ar
LEFT   JOIN LATERAL (
SELECT b.price * b.commission / 100.0 AS com
FROM   area    p
JOIN   booking b USING (areaid)
WHERE  p.airport_id = ar.airport_id  -- my assumption
AND    p.areaid = 3
AND    b.booking_date >= '2022-09-01'
AND    b.booking_date <  '2022-10-01'
) b ON true
WHERE  ar.airport_id = 2;

小提琴

(但在要求机场2和区域3时，com没有结果，因为没有匹配的条目。(

您的子查询是不相关的。假设你真的想通过airport_id连接到指定的机场。

CCD_ 5与条件CCD_。这是一个隐藏的[INNER] JOIN。参见：

• SQL/PostgreSQL左联接忽略"on＝常数"；谓词，在左表上

过滤器b.booking_date <= '2022-09-30T23:59:59'可能也有点不正确(除非您的数据保证具有1分钟的分辨率(。无论哪种方式，b.booking_date < '2022-10-01'都是包含"；整个九月"；。'2022-10-01'是有效的timestamp文字，是'2022-10-01T00:00:00'的缩写。

假设commission中的值实际上是一个百分比。(但我去掉了最初的四舍五入。你没有要求。(

顺便说一句，如果你真的想要和-一个单一的值-相关的(！(子查询再次成为一个选项：

SELECT ar.name AS airport
, (SELECT sum(b.price * b.commission) / 100.0
FROM   area    p
JOIN   booking b USING (areaid)
WHERE  p.airport_id = ar.airport_id
AND    p.areaid = 3
AND    b.booking_date >= '2022-09-01'
AND    b.booking_date <  '2022-10-01') AS com
FROM   airport ar
WHERE  ar.airport_id = 2;

Answer 2

查询

SELECT (b.price * b.commission) AS com
FROM booking AS b
LEFT JOIN area AS p ON b.areaid = p.areaid
AND p.areaid = 3
AND (b.booking_date >= '2022-09-01T00:00' AND b.booking_date <= '2022-09-30T23:59:59')

返回7行。SELECT列表中使用的子查询返回的行数不得超过1行。

获得所需内容的自然方法是加入所有表，例如：

SELECT
ar.name,
(b.price * b.commission) AS com
FROM airport AS ar
LEFT JOIN area AS p 
ON ar.airport_id = p.airport_id
LEFT JOIN booking AS b 
ON b.areaid = p.areaid
AND p.areaid = 3
AND b.booking_date >= '2022-09-01T00:00' 
AND b.booking_date <= '2022-09-30T23:59:59'

将LEFT JOIN替换为JOIN以跳过结果为空的行。