英文:
SQLITE: Table names to select from as a subquery
问题
我正在尝试从多个表中一次性选择一个列的不同值。 我要合并的两个查询如下：
1. 选择所有适当的表：
```sql
SELECT name
FROM sqlite_schema
WHERE type='table'
AND name NOT LIKE 'sqlite_%';
- 从一个表中选择不同的值：
SELECT DISTINCT CATEGORY FROM my_table;
我想在第一个查询的结果中对每个表名运行最后一个查询。 我相当肯定这是可能的，但还没有搞清楚。
我想象的是类似这样的东西：
SELECT DISTINCT CATEGORY
FROM (
SELECT name
FROM sqlite_schema
WHERE type='table'
AND name NOT LIKE 'sqlite_%'
);
这个合并的查询会引发错误。
<details>
<summary>英文:</summary>
I'm trying to select distinct values of one column from many tables at once. The two queries I want to combine are:
1. Select all the appropriate tables:
```sql
SELECT name
FROM sqlite_schema
WHERE type='table'
AND name NOT LIKE 'sqlite_%';
- Select distinct values from one table:
SELECT DISTINCT CATEGORY FROM my_table;
I want to run this last query on each table name from the results of the first query. I'm pretty sure it should be possible, but haven't worked it out.
Something like this is what I imagine:
SELECT DISTINCT CATEGORY
FROM (
SELECT name
FROM sqlite_schema
WHERE type='table'
AND name NOT LIKE 'sqlite_%'
);
This combined query throws an error.
答案1
得分: 1
以下是翻译好的部分：
SQLite 最好的用法是将所需的 SQL 语句作为字符串获取，然后使用您选择的编程语言执行它：
SELECT GROUP_CONCAT('SELECT CATEGORY FROM ' || name, ' UNION ') AS sql
FROM sqlite_schema
WHERE type='table' AND name NOT LIKE 'sqlite_%';
您将获得一个字符串，类似于：
SELECT CATEGORY FROM table1
UNION
SELECT CATEGORY FROM table2
UNION
SELECT CATEGORY FROM table3
我使用 UNION 而不是 SELECT DISTINCT。
请查看演示。
英文:
The best that you can do with SQLite is get the sql statement that you need as a string and execute it with the programming language of your choice:
SELECT GROUP_CONCAT('SELECT CATEGORY FROM ' || name, ' UNION ') AS sql
FROM sqlite_schema
WHERE type='table' AND name NOT LIKE 'sqlite_%';
You will get a string like:
SELECT CATEGORY FROM table1
UNION
SELECT CATEGORY FROM table2
UNION
SELECT CATEGORY FROM table3
I use UNION instead of SELECT DISTINCT.<br/>
See the demo.<br/>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论