MySQL 查询在多列上使用 DISTINCT 并按日期(最后一个时间戳)排序

Question

我在查询中挣扎，day_number上选择了 DISTINCT 但按日期时间戳排序。

新记录是微不足道地添加的，我只需要带有最新插入日期 (date_add( 并按day_number排序的记录。

id   Dept_id  day_number   price     date_add
1      46         1        20000    2019-08-19 17:06:30
2      46         2        18000    2019-08-19 17:06:30
3      46         3        19000    2019-08-19 17:06:30
4      46         4        16000    2019-08-19 17:06:30
5      46         5        18000    2019-08-19 17:06:30
6      46         6        22000    2019-08-19 17:06:30
7      46         7        30000    2019-08-19 17:06:30
10     46         1        38000    2019-08-19 17:15:24
11     46         1        34000    2019-08-19 17:18:44  

我试过这个：

SELECT DISTINCT day_number, date_add, (price)
FROM ai_targets where dept_id=46
GROUP BY day_number
ORDER BY day_number DESC 

但它给出了错误的价格。

Answer 1

如果您希望每个date_add具有最大day_number的行，请使用NOT EXISTS：

SELECT t.*
FROM ai_targets *
WHERE dept_id = 46
AND NOT EXISTS (
SELECT 1 FROM ai_targets
WHERE dept_id = t.dept_id AND date_add = t.date_add AND day_number > t.day_number
)

如果您希望每个day_number具有最大date_add的行：

SELECT t.*
FROM ai_targets *
WHERE dept_id = 46
AND NOT EXISTS (
SELECT 1 FROM ai_targets
WHERE dept_id = t.dept_id AND day_number = t.day_number AND date_add > t.date_add
)

Answer 2

SELECT DISTINCT t.day_number, t.date_add, (t.price)
FROM ai_targets t
INNER JOIN 
(
SELECT MAX(date_add) AS max_datea_dd 
FROM ai_target
) mx 
ON ma.max_date_add = t.date_add
where t.dept_id=46
GROUP BY t.day_number
ORDER BY t.day_number DESC;

Answer 3

你能试试下面的查询吗？

SELECT 
* 
FROM
ai_targets t1 
WHERE DATE_ADD= 
(SELECT 
MAX(DATE_ADD) 
FROM
ai_targets 
WHERE t1.`day_number` = day_number 
AND LEFT(t1.date_add, 10) = LEFT(DATE_ADD, 10) 
GROUP BY LEFT(DATE_ADD, 10),
day_number) ;