mysql - 如何高效，简洁地编写查询语句？

首先，我从两个不同的数据库（db1和db2）中进行选择。从每个数据库。在db1上，我基于一列中的数据，根据一个条件计算结果。这同样适用于db2。

    SELECT
    (SELECT COUNT(*) FROM db1.table1) AS Column1,
    (SELECT COUNT(*) FROM db1.table1 where
                          table1.X ='a') AS Column2,
    (SELECT COUNT(*) FROM db1.table1 where
                         table1.X = 'b') AS Column3,
    (SELECT COUNT(*) FROM db1.table1 where
                         table1.X = 'c') AS Column4,
    (SELECT COUNT(*) FROM db1.table1 where
                          table1.X = 'd') AS Column5,
    (SELECT COUNT(*) FROM db1.table1 where
                          table1.X = 'e') AS Column6,
    (SELECT COUNT(*) FROM db2.table2 where
                          table2.Y = 'f') AS Column7,
    (SELECT COUNT(*) FROM db2.table2 where
                          table2.Y = 'g') AS Column8,
    (SELECT COUNT(*) FROM db2.table2 where
                          table2.Y = 'h') AS Column9,
    (SELECT COUNT(*) FROM db2.table2 where
                          table2.Y = 'i') AS Column10;

最佳答案

你试过这样的方法吗：

SELECT
    MAX(Column1) AS Column1,
    MAX(Column2) AS Column2,
    MAX(Column3) AS Column3,
    MAX(Column4) AS Column4,
    MAX(Column5) AS Column5,
    MAX(Column6) AS Column6,
    MAX(Column7) AS Column7,
    MAX(Column8) AS Column8,
    MAX(Column9) AS Column9,
    MAX(Column10) AS Column10
FROM
    (SELECT
        COUNT(X) AS Column1,
            SUM(X = 'a') AS Column2,
            SUM(X = 'b') AS Column3,
            SUM(X = 'c') AS Column4,
            SUM(X = 'd') AS Column5,
            SUM(X = 'e') AS Column6,
            NULL AS Column7,
            NULL AS Column8,
            NULL AS Column9,
            NULL AS Column10
    FROM
        _ds_stack_table1 AS table1 UNION ALL SELECT
        NULL AS Column1,
            NULL AS Column2,
            NULL AS Column3,
            NULL AS Column4,
            NULL AS Column5,
            NULL AS Column6,
            SUM(Y = 'f') AS Column7,
            SUM(Y = 'g') AS Column8,
            SUM(Y = 'h') AS Column9,
            SUM(Y = 'i') AS Column10
    FROM
        _ds_stack_table2 AS table2) D;

SQL小提琴链接：
http://sqlfiddle.com/#!9/1eb5cf/1/0

关于mysql - 如何高效，简洁地编写查询语句？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50762397/