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

Question

我有一个名为accounts的数据库，在这个数据库中有一个account表。此外，我还有一个名为players的数据库，在这个数据库中有一个player表。

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

我尝试过以下方法：

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

但它没有起作用。

- Cyclone

Answer 1

只需在两个SELECT语句上执行简单的UNION操作：

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

由于它们位于不同的数据库中，因此您必须使用数据库名称来限定每个表的资格。

- Paul Sasik

Answer 2

如果您需要确切的行数（而非总和），则可以像这样操作：

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

- Timur

Answer 3

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

- Rabih Filfili

Answer 4

尝试：

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

- Wes Crow

---

收到以下信息：SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected - Cyclone

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

- rabih filfili

---

请提供这段代码如何解决问题的说明。这将有助于回答者和未来的搜索者学习。 - SnareChops