如何查询大于使用第一天出现的值的值？

flvlnr44 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(320)

我有下表。

"customers_dishes"
id - customer id, STRING
date - date of arriving at the restaurant, DATE
bill - total bill, DOUBLE

我试图输出所有的事件的客户，他们的帐单是大于他们的第一次约会访问餐厅的帐单

id, date, bill
Alen, 2018-03-01, 50
Alen, 2018-03-02, 48
Alen, 2019-03-01, 60
Bob, 2018-03-04, 45
Bob, 2018-03-06, 55
Bob, 2019-03-01, 50

输出应为：

id, date, bill
Alen, 2019-03-01, 60
Bob, 2018-03-06, 55
Bob, 2019-03-01, 50

试过这样做：

SELECT (*) FROM customers_dishes
WHERE date NOT IN (
SELECT date FROM customers_dishes ORDER BY id, date ASC LIMIT 1)
AND id NOT IN(
SELECT id FROM customers_dishes ORDER BY id, date ASC LIMIT 1)

有什么建议吗？

sql

来源：https://stackoverflow.com/questions/62049949/sql-how-to-query-values-that-are-greater-than-the-value-appeared-on-the-first

2条答案

按热度按时间

qmelpv7a1#

你可以用 first_value() :

select cd.*
from (select cd.*,
             first_value(bill) over (partition by id order by date) as first_bill
      from customers_dishes cd
     ) cd
where bill > first_bill;

也可以使用相关子查询：

select cd.*
from customers_dishes cd
where cd.bill > (select cd2.bill
                 from customers_dishes cd2
                 where cd2.id = cd.id
                 order cd2.date
                 fetch first 1 row only
                );

赞(0）回复(0）举报 2021-08-13

kxe2p93d2#

SELECT a.*
FROM customers_dishes a
CROSS APPLY (
              SELECT TOP 1 bill
              FROM customers_dishes t
              WHERE t.id = a.id
                  AND a.DATE <> t.DATE
              ORDER BY DATE ASC
) b
WHERE b.bill is not null and a.bill > b.bill

赞(0）回复(0）举报 2021-08-13

我来回答

如何查询大于使用第一天出现的值的值？

2条答案

相关问题

热门标签

最新问答