mysql将两个表与自建列组合在一起

mwecs4sa 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(401)

嗨，我对这两个表有两个不同的查询，如下所示：
表1：

表2：

[1] select exerciseId, sum(frustrated = 'Y') as Frustrated,
sum(frustrated = 'N') as NotFrustrated
from selfreportfrustration
group by exerciseId;

和

[2] SELECT exercisesId, count(*) as total FROM exerciseprogress GROUP BY 
exercisesId;

如何将这两个查询组合成一个查询，这样就可以有一个包含4列的视图？如果我没有错的话，exerciseid和exerciseid都是相同的，所以它们可以合并。谢谢你的时间！

mysql

来源：https://stackoverflow.com/questions/52734319/mysql-combining-two-tables-with-self-created-columns

2条答案

按热度按时间

xqnpmsa81#

这里有一个查询：

select main.exerciseId, sum(main.frustrated = 'Y') as Frustrated,sum(main.frustrated = 'N') as NotFrustrated,
(SELECT count(*) FROM exerciseprogress WHERE exercisesId = main.exerciseId) as total
from selfreportfrustration AS main
group by exerciseId;

赞(0）回复(0）举报 2021-06-18

gev0vcfq2#

使用左连接

select a.*, b.total 
from

(select exerciseId, sum(frustrated = 'Y') as Frustrated,
sum(frustrated = 'N') as NotFrustrated
from selfreportfrustration
group by exerciseId) a 

left join 
(SELECT exercisesId, count(*) as total FROM exerciseprogress GROUP BY 
exercisesId) b 
on a.exerciseId=b.exercisesId

赞(0）回复(0）举报 2021-06-18

我来回答

mysql将两个表与自建列组合在一起

2条答案

相关问题

热门标签

最新问答