mysql获取连续多表的计数

7rtdyuoh 于 2021-06-20 发布在 Mysql

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

我的数据库中有4个表，比如t1，t2，t3，t4，我想从orderid=1的条件中得到count id，但我不知道如何做到这一点。有人能帮我吗？这是一个基本问题，但我对sql查询还不熟悉。我使用这个查询并显示四列，我只需要一列就可以将我的total cont设置为另一个select查询。

(SELECT
(SELECT COUNT(id)
FROM c.fa
WHERE foruser = 1
) AS FA  ,
(SELECT COUNT(id)
FROM c.ia
WHERE foruser = 1
) AS IA ,
(SELECT COUNT(id)
FROM c.la
WHERE foruser = 1
) AS LA ,
(SELECT COUNT(id)
FROM c.ta
WHERE foruser = 1
) AS TA ,
(SELECT SUM(FA+IA+LA+TA)) AS TOTAL
)

sql mysql

来源：https://stackoverflow.com/questions/50731920/mysql-get-count-of-multi-table-with-contition

2条答案

按热度按时间

0ejtzxu11#

您的查询基本上是正确的，但要获得总数，您需要另一个子查询：

SELECT x.*, (FA + IA + LA + TA) as total
FROM (SELECT (SELECT COUNT(id) FROM c.fa WHERE foruser = 1) AS FA ,
             (SELECT COUNT(id) FROM c.ia WHERE foruser = 1) AS IA,
             (SELECT COUNT(id) FROM c.la WHERE foruser = 1) AS LA,
             (SELECT COUNT(id) FROM c.ta WHERE foruser = 1) AS TA
     ) x;

问题是不能在同一个目录中重复使用列别名 SELECT .
以上只是编写查询的一种方法。子查询通常放在 FROM 条款，允许：

SELECT FA.cnt, IA.cnt, LA.cnt, TA.cnt,
       (FA.cnt + IA.cnt + LA.cnt + TA.cnt) as total
FROM (SELECT COUNT(id) as cnt FROM c.fa WHERE foruser = 1) FA CROSS JOIN
     (SELECT COUNT(id) as cnt FROM c.ia WHERE foruser = 1) IA CROSS JOIN
     (SELECT COUNT(id) as cnt FROM c.la WHERE foruser = 1) LA CROSS JOIN
     (SELECT COUNT(id) as cnt FROM c.ta WHERE foruser = 1) TA;

展开查看全部

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

xuo3flqw2#

我找到了答案：

SELECT SUM(FA+IA+LA+TA) AS TOTAL FROM (
(SELECT COUNT(id) AS FA FROM c.f WHERE foruser = 1 ) AS FAA,
(SELECT COUNT(id) AS IA FROM c.i WHERE foruser = 1) AS IAA  ,
(SELECT COUNT(id) AS LA FROM c.l WHERE foruser = 1 ) AS LAA ,
(SELECT COUNT(id) AS TA FROM c.t WHERE foruser = 1 ) AS TAA 
)

像我这样的人需要这个。

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

我来回答

mysql获取连续多表的计数

2条答案

相关问题

热门标签

最新问答