如何在一个查询中获取2个不同表（和数据库）的行数？

Question

我得到了一个名为 accounts 的数据库和该数据库内的 account 表，此外我还得到了名为 players 和 player 的数据库> 该数据库内的表。

如何在一个查询中获取这两个表的行数？

我已经尝试过这个：

SELECT
    SUM(`account`.`account`.`id`) AS 'accounts',
    SUM(`player`.`player`) AS 'players';

但它不起作用。

Answer 1

如果您需要精确的行数（而不是总和），请执行以下操作：

select
(select count(*) from accounts.account) as count1,
(select count(*) from players.player) as count2

或

select count(*) as `count`,"account" as `table` from accounts.account
union all
select count(*) as `count`,"player" as `table` from players.player

Answer 2

对两个 select 语句进行简单的 UNION 操作即可：

SELECT COUNT(*), 'Accounts' FROM Accounts.Account
UNION
SELECT COUNT(*), 'Players' FROM Players.Player

并且您必须使用数据库名称来限定每个表，因为它们位于不同的数据库中。

Answer 3

尝试：

SELECT
   COUNT(`account`.`id`) AS 'accounts',
   COUNT(`player`.`player`) AS 'players'
FROM
   `account`,
   `player`

Answer 4

SELECT COUNT(*) 
FROM (
    SELECT Id 
    FROM accounts.account 
    UNION ALL 
    SELECT player 
    FROM players.player ) AS BothTables

Answer 5

with Value (nbr, name ) as
(
select count(*) amount, 'AccountsCount' as ab from accounts..account
union all
select count(*) amount, 'PlayersCount' as ab from players..player
)
select * 
from value as s
PIVOT(sum(nbr) for name in (AccountsCount, PlayersCount) ) as pvt