带有子查询的 SQL

发布于 2024-11-02 18:28:18 字数 683 浏览 0 评论 0原文

我记得在数据库课上做过这件事，感觉像是很久以前的事，但记不起，似乎无法搜索正确的单词。

我试图本质上将一个生成 foreach 的查询，然后根据第一个查询的信息运行另一个查询，转换为一个查询。更清楚地说，我有两个表成员和条目。出于我们的目的，成员有两列member_id、member_name，条目有entry_id、channel_id、author_id。 Author_id 是member_id 的外键。

我想要计算每个作者的条目数，并在一个 SQL 语句中完成。因此，我首先需要一个成员 id 的列表，然后获取每个成员 id 并查询条目表以获取author_id = member_id 的条目数。

从编程角度来看，它看起来像这样（不是完整的代码 obvi）：

$mems = select member_id from members_table;
foreach($mems as $cur_mem) {
    $entry_count = select count(*) from entries_table where author_id=$cur_mem[member_id];
    echo $entry_count;
}

这是针对 MySQL 的，并使用名为 ExpressionEngine 的 CMS，这就是解决方案中令人头疼的原因。很难描述这个问题，所以我可以澄清任何问题。

谢谢！

原文

I remember doing this in DB class in what feels like ages ago, but can't recall it and can't seem to search for the right words.

I am trying to essentially turn a query that produces a foreach and then runs another query based on information from the first one, into one query. More clearly, I have two tables members and entries. For our purposes members has two columns member_id, member_name, and entries has entry_id, channel_id, author_id. Author_id is an FK to member_id.

I want a count of entries by each author and do it in one SQL statement. So I need a list of the member id's first then take each member id and query the entries table for the count of entries where author_id = member_id.

Programmatically it would look something like this (not full code obvi):

$mems = select member_id from members_table;
foreach($mems as $cur_mem) {
    $entry_count = select count(*) from entries_table where author_id=$cur_mem[member_id];
    echo $entry_count;
}

This is for MySQL and using a CMS called ExpressionEngine, which is the cause of this headache of a solution. Having a hard time describing this issue, so I can clarify any questions.

Thanks!

分享到QQ

分享到微博

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

发布评论

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

莳間冲淡了誓言ζ 2024-11-09 18:28:18

尝试这个查询：

SELECT member_id, 
       COUNT(author_id ) 
FROM   members_table m 
       INNER JOIN entries_table 
         ON author_id = member_id 
GROUP  BY member_id

Try this query:

SELECT member_id, 
       COUNT(author_id ) 
FROM   members_table m 
       INNER JOIN entries_table 
         ON author_id = member_id 
GROUP  BY member_id

回复收藏 0 原文

~没有更多了~