选择必须对每个id只返回一行

Question

我使用下面的查询来获取结果。查询的目的是获取每个客户的最新sales_amount，但是当给定日期范围内的销售额为两个或两个以上时，查询返回所有记录，因此我只能根据id获取最新的记录。相同的id对于每个id应该只包含一行。

SELECT id,
Max(date),
sales_amount
FROM   customer
WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
AND id = 1001
GROUP  BY id,
sales_amount; 

Answer 1

您可以在子查询中使用行号来给您排序，然后只选择第一个。

SELECT *
FROM (
SELECT id, date, sales_amount,
ROW_NUMBER() OVER (ORDER BY date DESC) as RN
FROM   customer
WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
AND id = 1001
) sub
WHERE RN = 1

注意，如果你想为所有客户做这个，那么这是查询

SELECT *
FROM (
SELECT id, date, sales_amount,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) as RN
FROM   customer
WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
) sub
WHERE RN = 1

将为您提供每个客户的最新行。

Answer 2

这是因为您在group by子句中包含了ID，因此将返回所有ID。

Answer 3

您试过添加:

SELECT id,
Max(date),
sales_amount
FROM   customer
WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
AND id = 1001
GROUP  BY id,
sales_amount
ORDER BY date DESC 
LIMIT 1;

Answer 4

select * from (
SELECT  ROW_NUMBER() as Rn, 
id,
Max(date),
sales_amount
FROM   customer
WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
AND id = 1001
GROUP  BY id
order by max(date)
)
where Rn = 1