Oracle SQL如何查询含负Qty的BillNo下所有明细记录

表结构与数据 #

table_a 

billno    desc        qty    amount
10324    orange       -1     -1.00
10324    apple         1      3.00
10324    papaya        1      2.00

10431    water melon   1      2.00
10431    kiwi          1      3.00   

10233    banana       -1     -2.50
10233    mango         1      4.00
..
.. 
total 378 records

需求 #

在未知billno的情况下，查询所有存在负qty的billno对应的desc、qty、amount字段的全部记录。

尝试过的方法及问题 #

曾使用sign(qty)筛选：

select desc,qty,amount from table_a where sign(qty)='-1';

返回结果仅包含负qty的单条记录：

orange       -1     -1.00
banana       -1     -2.50

但目标是获取对应账单下的所有明细：

orange       -1     -1.00
apple         1      3.00
papaya        1      2.00  

banana       -1     -2.50
mango         1      4.00

解决方案 #

方法1：子查询+IN #

先筛选出所有存在负qty的billno，再匹配这些billno的全部记录：

SELECT `desc`, qty, amount
FROM table_a
WHERE billno IN (
    SELECT DISTINCT billno
    FROM table_a
    WHERE qty < 0
);

方法2：EXISTS子查询 #

通过关联子查询检查当前记录的billno是否存在负qty的条目：

SELECT t1.`desc`, t1.qty, t1.amount
FROM table_a t1
WHERE EXISTS (
    SELECT 1
    FROM table_a t2
    WHERE t2.billno = t1.billno
      AND t2.qty < 0
);

注：desc是SQL关键字，建议用反引号包裹避免语法错误。

方法3：窗口函数（适用于支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL等） #

按billno分组，标记该组是否存在负qty，再筛选出标记为存在的记录：

SELECT `desc`, qty, amount
FROM (
    SELECT 
        `desc`, qty, amount,
        MAX(CASE WHEN qty < 0 THEN 1 ELSE 0 END) OVER (PARTITION BY billno) AS has_neg_qty
    FROM table_a
) AS sub
WHERE has_neg_qty = 1;

内容的提问来源于stack exchange，提问作者Hoong wai kong