sql - 如何在 Where 子句中使用分组功能

Question

有没有办法在 where 子句中使用组函数？我正在尝试根据以下逻辑插入记录，但仅适用于 qty1 < qty2

我的代码：

    With t1 as (select item_no, qty,
                min (stock_date)
           From stock_tab
          Where stock_date < SYSDATE
        Group by item_no, qty
     Select item_no, min(t1.qty) qty1, max(t2.qty) qty2, max(stock_date)
         From t1
        Where t1.qty1 < t1.qty2  --incorrect
         --    min(t1.qty) < max(t1.qty2)  --also tried this 
         Group by item_no
        Oder by max(t1.stock_date) desc, max(t1.qty) desc

标签： sqloraclegroup-by

Answer 1

如果您想使用聚合函数作为您正在搜索的过滤器having。

With t1 as (select item_no, qty,
            min (stock_date)
       From stock_tab
      Where stock_date < SYSDATE
    Group by item_no, qty)
 Select item_no, min(t1.qty) qty1, max(t2.qty) qty2, max(stock_date)
     From t1
     Group by item_no
     having min(t1.qty) < max(t1.qty2)
    Order by max(t1.stock_date) desc, max(t1.qty) desc