mysql 如何使用别名显示两个计数之间的差异？

c86crjj0 于 2023-02-28 发布在 Mysql

关注(0)|答案(4)|浏览(111)

我正在创建显示数据库中不同事物的总计的自定义视图，我还想显示差异。
例如：

SELECT
   (SELECT COUNT(*) FROM `documents`) AS `doc_count`,
   (SELECT COUNT(*) FROM `contacts`) AS `user_count`,
   (`doc_count` - `user_count`) AS `difference`;

用这种方法使用别名时出现错误。有没有办法在不重复select count(*)查询的情况下编写此查询？

mysql

来源：https://stackoverflow.com/questions/25585101/how-to-show-the-difference-between-two-counts-using-aliases

4条答案

按热度按时间

cld4siwp1#

您可以用另一个查询 Package 这两个查询：

SELECT doc_count, user_count, doc_count - user_count AS difference
FROM   ((SELECT COUNT(*) FROM `documents`) AS doc_count,
        (SELECT COUNT(*) FROM `contacts`) AS user_count) t

赞(0）回复(0）举报 2023-02-28

jdgnovmf2#

不，您不能在查询的同一级别使用别名，必须使用整个表达式或使用子选择

SELECT
   (SELECT COUNT(*) FROM `documents`) AS `doc_count`,
   (SELECT COUNT(*) FROM `contacts`) AS `user_count`,
   ((SELECT COUNT(*) FROM `documents`) - (SELECT COUNT(*) FROM `contacts`)) AS `difference`;

赞(0）回复(0）举报 2023-02-28

2exbekwf3#

以下是获得您所需结果的“变通方法”：

SELECT C.doc_count
    ,C.user_count
    ,C.doc_count - C.user_count AS `difference`
FROM (SELECT
         (SELECT COUNT(*) FROM `documents`) AS `doc_count`
         ,(SELECT COUNT(*) FROM `contacts`) AS `user_count`) C

但是我不确定这种查询的性能...
希望这对你有帮助

赞(0）回复(0）举报 2023-02-28

bpsygsoo4#

我会将这些语句移到from子句中，并使用cross join：

SELECT d.doc_count, u.user_count, (d.doc_count - u.user_count) as difference
FROM (SELECT COUNT(*) as doc_count FROM `documents`) d CROSS JOIN
     (SELECT COUNT(*) as user_count FROM `contacts`) u;

赞(0）回复(0）举报 2023-02-28

我来回答

mysql 如何使用别名显示两个计数之间的差异？

4条答案

相关问题

热门标签

最新问答