SQLite 查询根据另一个表的值获取表

我不确定这里必须有什么标题才能正确反映我的问题，我只能描述我想要的内容。

有一个包含字段的表：

id, name, city

还有接下来的几行：

1 John London
2 Mary Paris
3 John Paris
4 Samy London

我想得到这样的结果：

      London  Paris
Total   2       2
John    1       1
Mary    0       1
Samy    1       0

因此，我需要获取名称的所有唯一值，并为另一个字段（城市）的唯一值找到适当的数量 我还想得到每个城市的总数

简单的方法是：

1）获取唯一名称列表

SELECT DISTINCT name FROM table

2）获取独特城市列表

SELECT DISTINCT city FROM table

3）为每个名称和城市创建一个查询

SELECT COUNT(city) FROM table WHERE name = some_name AND city = some_city

4）获取总计：

SELECT COUNT(city) FROM table WHERE name = some_name

（我没有测试这些查询，所以这里可能有一些错误，但这只是为了展示这个想法）

因为有 3 个名字和 2 个城市 -> 3 * 2 = 6 个数据库查询

但是对于包含 100 个城市和 100 个名称的表 -> 100 * 100 = 10 000 个数据库查询 这可能需要很多时间才能完成。

此外，名称和城市可能会更改，因此，我无法使用预定义的名称或城市创建查询，因为每天都是新的，因此，可能是莫斯科、都灵和柏林，而不是伦敦和巴黎。名字也是一样。

如何使用sqlite对原始表进行一二查询来获取这样的表？

（sqlite：我是为android做的）

您可以通过条件聚合获取每个名称的结果。至于总数，不幸的是 SQLite 不支持with rollup子句，这会自动生成它。

一种解决方法是union all以及用于订购的附加列：

select name, london, paris
from (
    select name, sum(city = 'London') london, sum(city = 'Paris') paris, 1 prio
    from mytable
    group by name
    union all
    select 'Total', sum(city = 'London'), sum(city = 'Paris'), 0
    from mytable
) t
order by prio, name

实际上子查询可能不是必需的：

select name, sum(city = 'London') london, sum(city = 'Paris') paris, 1 prio
from mytable
group by name
union all
select 'Total', sum(city = 'London'), sum(city = 'Paris'), 0
from mytable
order by prio, name