当前位置：文江博客话题详情

具有限制和不同的 SQL Group By - MySQL

发布于 2024-11-07 19:53:19 字数 740 浏览 0 评论 0原文

这是我的表格和结构

Shoutbox

SID //PK
Title //标题

shoutbox_follower

SID
UID //用户 ID

Shouts

SID
Text //Shouts

I想要获取每个 Shoutbox 的最后 1 个（最新）喊叫声（Shouts.Text），用户正在关注

我尝试了这个，但它不起作用

select shoutbox_follower.SID,shoutbox.title, shouts.text from shoutbox_follower, shoutbox, shouts where shoutbox_follower.uid=1;

但我可以使用多个查询来完成工作

SELECT SID from shoutBox_Follower where UID=5
SELECT SID,TEXT from Shouts where SID in ($boxList) order by id desc limit 1
SELECT Title from Shoutbox where SID=? limit 1

原文

This is my tables and structure

Shoutbox

SID //PK
Title //Title

shoutbox_follower

SID
UID //User ID

Shouts

SID
Text //Shouts

I want to get the last 1 (latest) shouts(Shouts.Text) for each Shoutbox, which the user is following

i tried this but it did not worked

select shoutbox_follower.SID,shoutbox.title, shouts.text from shoutbox_follower, shoutbox, shouts where shoutbox_follower.uid=1;

But i can do the work with multiple query

SELECT SID from shoutBox_Follower where UID=5
SELECT SID,TEXT from Shouts where SID in ($boxList) order by id desc limit 1
SELECT Title from Shoutbox where SID=? limit 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

む无字情书 2024-11-14 19:53:19

在这样的情况下，您可以组合查询：

SELECT SID,
       (select TEXT
       from Shouts
       where Shouts.SID = shoutBox_Follower.SID
       order by Shouts.SID desc limit 1
       ) as shout_text
from shoutBox_Follower
where UID=5

它对于少量行执行得非常快（当然，假设您有所需的索引）。根据您的第三个查询，可以使用简单的内部联接来获取标题。

You can combine the queries, in cases like this:

SELECT SID,
       (select TEXT
       from Shouts
       where Shouts.SID = shoutBox_Follower.SID
       order by Shouts.SID desc limit 1
       ) as shout_text
from shoutBox_Follower
where UID=5

It performs very fast for small numbers of rows (assuming you've the needed indexes, of course). Based on your third query, the title can be fetched using a simple inner join.

回复收藏 0 原文

霊感 2024-11-14 19:53:19

在评论中您提到您需要快速解决方案。如果是这样 - 则将字段 latest_shout_id 添加到 shoutbox 表中，并使用 shouts 表的 AFTER INSERT 触发器来维护它。就这样。

这是将执行您想要的操作的查询（假设您将 shout_id 作为 shouts 表中的 PK 并通过 引用 shoutbox shoutbox_id 字段）：

    SELECT x.*
      FROM shoutbox_follower f
INNER JOIN (SELECT MAX(shout_id) shout_id,
                   shoutbox_id
              FROM Shouts s
          GROUP BY shoutbox_id) x ON x.shoutbox_id = f.sid 
     WHERE f.uid = 5

将其更改为相关子查询可能会以更快或更慢的方式更改它，这取决于很多因素。

In comments you mentioned that you need fast solution. If so - then add a field latest_shout_id to the shoutbox table and maintain it with AFTER INSERT trigger of shouts table. That's all.

Here is the query that will do what you want (assuming you have shout_id as a PK in shouts table and reference to shoutbox by shoutbox_id field):

    SELECT x.*
      FROM shoutbox_follower f
INNER JOIN (SELECT MAX(shout_id) shout_id,
                   shoutbox_id
              FROM Shouts s
          GROUP BY shoutbox_id) x ON x.shoutbox_id = f.sid 
     WHERE f.uid = 5

Changing it to correlated subquery may change it in faster or slower manner, it depends on a lot of factors.

回复收藏 0 原文

~没有更多了~