SQL 只有每個值的第一行

MYSQL 版本 5.7.31

如何使它顯示mainthreadid具有最高值的值/列的不同行date？

請注意，數據是使用過濾的

WHERE approved = 1 AND section != 150 

這裡有一個小提琴。

小提琴 DDL 和 DML

桌子

CREATE TABLE forum (
   id int,
   mainthreadid int,
   section int,
   approved int,
   date int(20),
   title varchar(255),
   text varchar(255)
  
);

樣本數據

INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (1, 1, 5, 1, 1000, "title1", "text1");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (2, 2, 5, 1, 1000, "title2", "text2");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (3, 2, 5, 1, 1001, "title3", "text3");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (4, 2, 5, 1, 1002, "title4", "text4");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (5, 2, 5, 1, 1003, "title5", "text5");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (6, 2, 5, 1, 1004, "title6", "text6");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (7, 2, 5, 0, 1005, "title7", "text7");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (8, 8, 150, 1, 1004, "title8", "text8");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (9, 1, 5, 1, 1006, "title9", "text9");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (10, 1, 5, 1, 1005, "title10", "text10");

SQL 語句

SELECT date, id, mainthreadid 
FROM `forum` 
WHERE approved = 1 AND section != 150 ORDER BY date DESC

結果集

我想要的結果集是：

date    id  mainthreadid
1006    9   1
1004    6   2

也許你需要在

SELECT forum.*
FROM forum
JOIN ( SELECT mainthreadid, MAX(`date`) `date`
      FROM forum
      GROUP BY mainthreadid ) lastdates USING (mainthreadid, `date`)

?

小提琴

對於每個mainthreadid只有最後一行（最大的行date）被選中。如果存在多個這樣的行（相同的最大日期），則將返回所有這些行。

---

您不包括檢查已批准 = 1 和 if section != 150

沒問題。

SELECT forum.*
FROM forum
JOIN ( SELECT mainthreadid, MAX(`date`) `date`
      FROM forum
      WHERE approved = 1 AND section != 150
      GROUP BY mainthreadid ) lastdates USING (mainthreadid, `date`)

小提琴

引用自：https://dba.stackexchange.com/questions/280570