SQL合并两个表-每个键值只得到一行

Question

我正试图在数据库上运行SQL命令。我有两张表，一张是用户用的，另一张是帖子用的。我想找到每个用户的最后一篇帖子。我尝试了以下内容：

SELECT b.`username`, b.`id`, c.`home`, c.`text` FROM `sunlight-users` AS b 
LEFT JOIN `sunlight-posts` AS c ON b.`id` = c.`author`
WHERE b.`group` = 1 OR b.`group`=3 OR b.`group`= 5 OR b.`group` = 10 OR b.`group` = 14 OR b.`group` = 
23 OR b.`group` = 25
ORDER by c.`time` DESC
GROUP BY b.`username`

它失败了，错误为"您的语法在第5行的GROUP BY b.username附近很奇怪"。我试着用while循环(在网页上(运行命令，如下所示：

$query = DB::query("SELECT * FROM `"._mysql_prefix."-users` WHERE `group` = 1 OR `group` = 3 OR `group` = 5 OR `group` = 10 OR `group` = 14 OR `group` = 23 OR `group` = 25");
while($item = DB::row($query)) {
$post = DB::query("SELECT type,home,xhome,author,time FROM `"._mysql_prefix."-posts` WHERE ".$knihy." AND `author` = ".item['id']." ORDER BY `time` DESC LIMIT 1");
switch($post['home']){
case 2:
//code
break;
case 3:
//code
break;
default:
//code
}

但看起来第二个查询被完全忽略了，我得到了默认值，即使手动将查询键入数据库会得到2或3。我知道在一个循环中运行50个查询可能不是一个好主意，这就是为什么我试图一次完成所有查询的原因。此代码：

SELECT b.`username`, b.`id`, c.`home`, c.`text` FROM `sunlight-users` AS b 
LEFT JOIN `sunlight-posts` AS c ON b.`id` = c.`author`
WHERE b.`group` = 1 OR b.`group`=3 OR b.`group`= 5 OR b.`group` = 10 OR b.`group` = 14 OR b.`group` = 
23 OR b.`group` = 25
ORDER by c.`time` DESC

中的结果

+----+----------+------+------+
| id | username | home | text |
+----+----------+------+------+
|  1 | user1    |    2 | .... |
|  1 | user1    |    3 | aaaa |
|  0 | user0    |    4 | .... |
+----+----------+------+------+

顺序是正确的，我只想要每个用户的上排。如果有任何帮助，我将不胜感激。

Answer 1

您实际上需要过滤，而不是分组。您可以使用相关的子查询：

select 
u.username, 
u.id, 
p.home, 
p.text 
from sunlight_users u
left join sunlight_posts p
on  u.id = p.author
and p.time = (select max(p1.time) from sunlight_posts p1 where p1.author = u.id)
where u.group in (1, 3, 5, 10, 14, 23, 25)
order by u.username

在MySQL 8.0中，您可以使用窗口函数：

select *
from (
select 
u.username, 
u.id, 
p.home, 
p.text,
row_number() over(partition by u.id order by p.time desc) rn
from sunlight_users u
left join sunlight_posts p on  u.id = p.author
where u.group in (1, 3, 5, 10, 14, 23, 25)
) t
where rn = 1
order by u.username