我需要帮助在PHP(WordPress(中进行SQL查询。我有这个数据:
| id | kw_id | date | position
| 1 | 1 | 2020.08.23 | 16 |
| 2 | 1 | 2020.10.02 | 17 |
| 3 | 2 | 2020.10.08 | 5 |
| 4 | 2 | 2020.10.08 | 6 |
我需要得到两行(id=2,id=3(。查询标准有两个:
- 获取最长日期的行
- 按kw_id分组
当日期重复(id=3和id=4(时,获取具有最小位置的行。
结果必须如下:
id | kw_id | date | position
2 | 1 | 2020.10.02 | 17
3 | 2 | 2020.10.08 | 5
提前感谢。
对于传统的MySQL 5.*,它可能有点复杂。以下查询返回针id:
select min(id) as min_id
from tbl
join (
select kw_id, max(date) max_date
from tbl
group by kw_id
) tbl1 on tbl1.max_date = tbl.date and tbl1.kw_id = tbl.kw_id
group by tbl.kw_id;
结果:
+========+
| min_id |
+========+
| 2 |
+--------+
| 3 |
+--------+
现在是最后一个查询:
select * from tbl where id in (
select min(id) as min_id
from tbl
join (
select kw_id, max(date) max_date
from tbl
group by kw_id
) tbl1 on tbl1.max_date = tbl.date and tbl1.kw_id = tbl.kw_id
group by tbl.kw_id
);
最终结果:
+====+=======+============+==========+
| id | kw_id | date | position |
+====+=======+============+==========+
| 2 | 1 | 2020-10-02 | 17 |
+----+-------+------------+----------+
| 3 | 2 | 2020-10-08 | 5 |
+----+-------+------------+----------+
这里是SQL小提琴
如果您使用的是MySQL 8+,那么ROW_NUMBER
提供了一个选项:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC, position) rn
FROM yourTable
)
SELECT id, kw_id, date, position
FROM cte
WHERE rn = 1;
上面对ROW_NUMBER
的调用将每个id
的最新日期放在第一位,在日期为平局的情况下,将最小位置放在第一位置。