问题描述
我需要从多个表中获取数据，但无法进行查询.在这方面的任何帮助将不胜感激.已经发布了一些关于如何从多个表中获取数据的问题，但不知何故我无法抓住这种查询背后的概念.因此，我发布了一个问题，询问是否有人可以使用以下详细信息为我构建查询:
I need to fetch data from multiple tables but cannot make a query for that. Any help in this regard will be appreciated. There are some questions already posted about how to fetch data from multiple tables, but somehow i cannot grab the concept behind such query. Therefore i am posting question to ask if someone can build a query for me with the following details:
表格:
CREATE TABLE users(id INTEGER PRIMARY KEY,user_name TEXT)
CREATE TABLE category(id INTEGER PRIMARY KEY,name TEXT,user_id INTEGER)
CREATE TABLE item(id INTEGER PRIMARY KEY,name TEXT,category_id INTEGER)
CREATE TABLE Used(id INTEGER PRIMARY KEY,frequency REAL,item_id INTEGER)
一个用户可能有多个类别，每个类别可能有多个项目，每个项目可能有多个使用频率.
A user may have multiple categories and each category may have multiple items, and each item may have multiple frequency of use.
我想选择以下内容:
- 用户的所有类别
- 这些类别的所有项目
- 这些项目的所有频率
谢谢.
推荐答案
所以你想要这样的东西:
So you want something like this:
SELECT u.user_name,c.name,i.name,used.frequency
FROM users u
LEFT OUTER JOIN category c
ON(u.id = c.user_id)
LEFT OUTER JOIN item i
ON(i.category_id = c.id)
INNER JOIN Used
ON(i.id = used.item_id)
WHERE u.user_id = YOUR_USER_HERE
这篇关于SQLite 从多个表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！