嵌套选择数据库SQL查询 - nested select database sql query

nested select database sql query

本文关键字：查询 SQL 数据库选择嵌套 | 更新日期: 2023-09-27

如何从数据库中获取值??

SELECT * FROM comment;
------------------------------------
| commentid | content | read_unread|
------------------------------------
1              hello      Unread
2              hi         read    

Select * From replies;
//commentid Fk from table "  comment  " so it means row 1 from table comments has 2 replies
------------------------------------
| repnum | rep_content | commentid |
------------------------------------
1              see ya        1
2              ok            1

我想在html/php表中显示这个

comment num |  Content | replies count
--------------------------------------------------   
 1              hello    2
 2              hi       0

//"replies count 2"来自表comments

中回复到注释1的回复数。

我如何在一个查询SQL顺序中显示这个回复计数的计数?

试试这个:

SELECT a.commentid, a.content, count(b.repnum) as replies_count 
FROM comment a left join replies b on a.commentid = b.commentid 
GROUP by a.commentid

您可以在SQL查询中使用JOIN:

SELECT c.commentid, c.content, COUNT(r.replies) FROM comment c JOIN replies r ON r.commentid = c.commentID GROUP BY c.commentid, c.content;

更多信息在这里:http://dev.mysql.com/doc/refman/5.7/en/join.html

左连接和计数回复表是您的朋友。内连接将移除comments，这些还没有得到任何replies，所以这里需要左连接。replies中的计数是您的要求，因此计数(*)将不起作用。

SELECT A.commentid AS comment_num, A.content AS Content, count(B.commentid) as replies_count
FROM comment A
LEFT JOIN replies B on A.commentid=B.commentid
GROUP BY A.commentid, A.content

试一试

SELECT a.commentid, a.content, count(*) as replies_count
FROM comment a
LEFT JOIN replies b on a.commentid=b.commentid
GROUP BY a.commentid, a.content