求助sql大神，一个简单的查询
本帖最后由 zx2000 于 2019-9-1 22:30 编辑刚学sql一学期，碰到这个问题不知道解决:'(weeqw:'(weeqw SELECT 帖子ID,帖子内容,(SELECT COUNT(*) FROM 评论表 WHERE 评论表.帖子ID=帖子表.帖子ID)评论数,(SELECT COUNT(*) FROM 点赞表 WHERE 点赞表.帖子ID=帖子表.帖子ID)评论数, FROM 帖子表 ruo40018293 发表于 2019-9-1 22:00
SELECT 帖子ID,帖子内容,(SELECT COUNT(*) FROM 评论表 WHERE 评论表.帖子ID=帖子表.帖子ID)评论数,(SELECT ...
https://s2.ax1x.com/2019/09/01/n9QDBD.png成功了，感谢感谢！！{:1_921:} 你是要查什么的呢？ xz1997 发表于 2019-9-1 21:13
你是要查什么的呢？
看理想的查询结果，查询出的结果和那个表结构一样 zx2000 发表于 2019-9-1 21:14
看理想的查询结果，查询出的结果和那个表结构一样
你这样放有点乱啊。。 本帖最后由 从入门到入狱 于 2019-9-1 22:07 编辑
SELECTa.帖子ID,a.帖子内容,COUNT(b.帖子ID) AS 评论数,COUNT(c.帖子ID) AS 点赞数 FROM 帖子表a, 评论表 b,点赞表 c WHERE a.帖子id=b.帖子id AND a.帖子id= c.帖子id AND b.帖子id =c.帖子id;
我也是菜鸟，你看看行不行 本帖最后由 XuHonPhin 于 2019-9-1 21:24 编辑
select tzb.tzId, tzb.tzNr,plb.pls, dzb.dzs from tzb left join (select count(1) as pls,tzId from plb group by tzId) plb on tzb. tzId = plb. tzId left join (select count(1) as dzs, tzId from dzb group by tzId)dzb on plb. tzId= dzb. tzId select
帖子表.帖子ID,
帖子表.帖子内容,
count(评论表.评论ID) AS 评论数，
count(点赞表.帖子ID) AS 点赞数
from 帖子表
left join 评论表 on 帖子表.帖子ID=评论表.帖子ID
left join 点赞表 on 帖子表.帖子ID=点赞表.帖子ID
group by 帖子表.帖子ID,帖子表.帖子内容
order by帖子表.帖子ID select a.帖子ID,a.帖子内容,count(c.评论数),sum(dd.点赞数) from 帖子表 a
left join 评论表 b on b.帖子ID=a.帖子ID
left join 用户表 c on c.用户ID=b.用户ID
left join 点赞表 dd on dd.帖子ID=a.帖子ID
select 帖子ID,帖子内容,table1.评论数, table.点赞数
from 帖子表
inner join
(
select count(*) 评论数, 帖子ID from 评论表 group by 帖子ID
) table1
on table1.帖子ID = 帖子表.帖子ID
inner join
(
select count(*) 点赞数, 帖子ID from 点赞表 group by 帖子ID
) table2
on table2.帖子ID = 点赞表.帖子ID ubeing 发表于 2019-9-1 21:40
select 帖子ID,帖子内容,table1.评论数, table.点赞数
from 帖子表
inner join
select 帖子表.帖子ID,帖子表.帖子内容,table1.评论数, table2.点赞数
from 帖子表
inner join
(
select count(*) 评论数, 帖子ID from 评论表 group by 帖子ID
) table1
on table1.帖子ID = 帖子表.帖子ID
inner join
(
select count(*) 点赞数 , 帖子ID from 点赞表 group by 帖子ID
) table2
on table2.帖子ID = `点赞表`.`帖子ID`
> 1054 - Unknown column '点赞表.帖子ID' in 'on clause'
> 时间: 0.001s