如何在一个表中获得多个计数(*)查询？

Question

我一直试图将来自不同表的4个或多个(*)查询转换为一个查询。

这可能吗?还是我要问四个不同的问题？

这是我的密码：

try (final PreparedStatement sel = conn.prepareStatement("SELECT COUNT(*), SUM(coins) FROM User");
            final PreparedStatement sel2 = conn.prepareStatement("SELECT COUNT(*) FROM Friends");
            final PreparedStatement sel3 = conn.prepareStatement("SELECT COUNT(*) FROM Clans");
            final PreparedStatement sel4 = conn.prepareStatement("SELECT COUNT(*) FROM ClanMembers")) {
        try (final ResultSet rs = sel.executeQuery(); final ResultSet rs2 = sel2.executeQuery(); final ResultSet rs3 = sel3.executeQuery()) {
            rs.last();
            rs2.last();
            rs3.last();
            return new Object[] { rs.getInt(1), rs.getInt(2), rs2.getInt(1) / 2, rs3.getInt(1) };
        }
}

Answer 1

SELECT COUNT(*), SUM(coins) FROM User
union all
SELECT COUNT(*), 0 FROM Friends
union all
SELECT COUNT(*), 0 FROM Clans
union all
SELECT COUNT(*), 0 FROM ClanMembers
...

对于所有联合查询，列的数量和类型必须相同。

更新答案，以显示如何组合查询。

Answer 2

只需给出另一个可选的解决方案(我还不能发表评论，所以我不得不写成答案)

StanislavL的答案是一个很好的答案，你可以用它，它会返回行

但是万一你只需要1行的话，你可以试试这个

SELECT q1.count1,q1.coins,q2.count2, q3.count3, q4.count4
from (select (COUNT(*) count1, SUM(coins) coins, 1 id FROM User, 1 var) q1
inner join (SELECT COUNT(*) count2, 1 id FROM Friends) q2 on q1.id=q2.id
inner join (SELECT COUNT(*) count3, 1 id FROM Clans) q3 on q1.id=q3.id
inner join (SELECT COUNT(*) count4, 1 id FROM ClanMembers) q4 on q1.id=q4.id

Answer 3

看来我错过了显而易见的答案。

这解决了我的问题：

SELECT COUNT(*) FROM User union all SELECT COUNT(*) FROM Friends union all SELECT COUNT(*) FROM Clans union all SELECT COUNT(*) FROM ClanMembers union all SELECT SUM(coins) FROM User ...