自存 sql常见语句和实际应用

关于连表

查询两个表

SELECT *
FROM `study_article`
JOIN study_article_review 

查询的就是两个表相乘，结果为两个表的笛卡尔积

相这样

这种并不是我们想要的结果

通常会添加一些查询条件

SELECT *
FROM `study_article`JOIN study_article_review ON study_article.id=study_article_review.article_id

查询结果为

那么就会查询id=article_id的数据

左外连接，就是主表都查询出来，然后右表根据条件匹配

sql

SELECT *
FROM `study_article`
LEFT JOIN study_article_review ON study_article.id=study_article_review.article_id

相比于上面这种把主表的数据都查出来了

分组查询，就是对某一列的数据的相同的分组 group by 这一列

查询文章列表，并且查询每条文章的评论数

sql语句

SELECT study_article.id, study_article.content, study_article.title, study_article.url, count(study_article.id) AS count
FROM `study_article`
LEFT JOIN study_article_review ON study_article.id=study_article_review.article_id
GROUP BY study_article.id

然后这样的查询结果为

因为文章和评论属于1对多的关系，左外连接评论表就会产生许多多余的文章数据，所以要对文章id进行分组，然后统计文章id的数量就是这个文章的评论数量

2024 11.20 补充 虽然上面连表分组查询也可以，但是太过于麻烦，并且扩展性不太好

，如果我们要查询文章表的点赞数和评论数 就相当于文章表既要连接评论表统计评论数量，又要连接点赞表统计点赞表的数量，就相当于文章表要 left join 两次，当然也可以这样

select a.*,count(study_star.obj_id) as liked from 
(
SELECT study_article.id, study_article.content, study_article.title, study_article.url, count(study_article_review.article_id) AS reviewCount
FROM `study_article`
LEFT JOIN study_article_review ON study_article.id=study_article_review.article_id
GROUP BY study_article.id
) as a
left JOIN study_star on a.id=study_star.obj_id
GROUP BY a.id

就是先文章表左外连接评论表统计出评论数目 文章id分组，然后在把这个查询出来的表作为子查询

再左外连接点赞表统计出点赞的数量 id分组

虽然也可以实现但过于复杂

第二种方法

SELECT 
study_article.id, study_article.content, study_article.title, study_article.url,
(SELECT COUNT(1) FROM study_article_review where article_id=study_article.id) as reviewCount,
(SELECT COUNT(1) FROM study_star where study_star.obj_id=study_article.id) as likedFROM study_article

把子查询放到select之后分别查询点赞量和评论量，这样子扩展性大大提升 结果一样

查询父级评论列表，并查询该父级评论的子评论数量

要查询评论的子评论数量条件是pid等于父级评论的id

可以连表，自身连接自身条件为第一张表的id等于第二张表的pid，查询父级评论为

SELECT * FROM study_article_review as s1
left JOIN  study_article_review as s2  on s1.id=s2.pid
WHERE s1.pid=0

由于一个评论有很多回复属于一对多连接，对第一个表id分组，聚合查询回复数量

SELECT s1.*,count(s2.pid) AS reviewCount FROM study_article_review as s1
left JOIN  study_article_review as s2  on s1.id=s2.pid
WHERE s1.pid=0
GROUP BY s1.id

结果