求助sql大神，一个简单的查询

zx2000

刚学sql一学期，碰到这个问题不知道解决

ruo40018293

SELECT 帖子ID,帖子内容,(SELECT COUNT(*) FROM 评论表 WHERE 评论表.帖子ID=帖子表.帖子ID)评论数,(SELECT COUNT(*) FROM 点赞表 WHERE 点赞表.帖子ID=帖子表.帖子ID)评论数, FROM 帖子表

zx2000

ruo40018293 发表于 2019-9-1 22:00
SELECT 帖子ID,帖子内容,(SELECT COUNT(*) FROM 评论表 WHERE 评论表.帖子ID=帖子表.帖子ID)评论数,(SELECT ...

成功了，感谢感谢！！

xz1997

你是要查什么的呢？

zx2000

xz1997 发表于 2019-9-1 21:13
你是要查什么的呢？

看理想的查询结果，查询出的结果和那个表结构一样

xz1997

zx2000 发表于 2019-9-1 21:14
看理想的查询结果，查询出的结果和那个表结构一样

你这样放有点乱啊。。

从入门到入狱

SELECT  a.帖子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

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

xyz253

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

ubeing

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

zx2000

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