SQLite查询以基于另一个表的值获取表 [英] SQLite query to get table based on values of another table

问题描述

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

I am not sure what title has to be here to correctly reflect my question, I can only describe what I want.

有一个带有字段的表：

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

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

So, I need to take all unique values of name and find an appropriate quantity for unique values of another field (city) Also I want to get a total quantity of each city

简单的方法是：

1）获取唯一名称列表

1)Get a list of unique names

SELECT DISTINCT name FROM table

2）获取唯一城市列表

2)Get a list of unique cities

SELECT DISTINCT city FROM table

中选择不同的城市3）为每个名称和城市创建一个查询

3)Create a query for every name and city

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

4）总计：

SELECT COUNT(city) FROM table WHERE name = some_name

（我没有测试这些查询，所以也许这里有些错误，但这仅仅是来展示这个想法）

(I did't test these queries, so maybe there are some errors here but it's only to show the idea)

因为有3个名称和2个城市-> 3 * 2 = 6个对DB的查询

As there are 3 names and 2 cities -> 3 * 2 = 6 queries to DB

但是对于具有100个城市和100个名称的表-> 100 * 100 = 10000个查询DB
，这可能需要很多时间。

But for a table with 100 cities and 100 names -> 100 * 100 = 10 000 queries to DB and it may take a lot of time to do.

此外，名称和城市可能会更改，因此，我无法创建带有预定义名称或城市的查询，因为它每天都是新的，因此，代替伦敦和巴黎的可能是莫斯科，都灵和柏林。与名称相同。

Also, names and cities may be changed, so, I can't create a query with predefined names or cities as every day it's new ones, so, instead of London and Paris it may be Moscow, Turin and Berlin. The same thing with names.

如何使用sqlite通过原始查询对原始表进行一两次查询？

How to get such table with one-two queries to original table using sqlite?

（sqlite：我为android做）

(sqlite: I do it for android)

推荐答案

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

You can get the per-name results with conditional aggregation. As for the total, unfortunately SQLite does not support the with rollup clause, that would generate it automatically.

一个解决方法是全部联合和一个用于订购的附加列：

One workaround is union all and an additional column for ordering:

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

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

Actually the subquery might not be necessary:

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

这篇关于SQLite查询以基于另一个表的值获取表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！