sqlite - 如何根据行查找选择 sqlite 列

Question

我正在尝试编写 SQLite 查询，但发现了一个超出我技能范围的问题。我正在尝试输出基于另一个引用表的行的列。

食物 list :

| food_id | name      |
|---------|-----------|
|       1 | Apple     |
|       2 | Orange    |
|       3 | Pear      |

营养定义:

| nutrient_id | name      |
|-------------|-----------|
|          21 | Carbs     |
|          22 | Protein   |
|          23 | Fat       |

营养数据:

| food_id | nutrient_id | value |
|---------|-------------|-------|
|       1 |          21 |    50 |
|       1 |          22 |    24 |
|       1 |          23 |    63 |
|       2 |          22 |    12 |
|       2 |          23 |    95 |
|       3 |          21 |    66 |
|       3 |          22 |    87 |
|       3 |          23 |    38 |

输出:

| food_id | name      | Carbs | Protein | Fat |
|---------|-----------|-------|---------|-----|
|       1 | Apple     |    50 |      24 |  63 |
|       2 | Orange    |       |      12 |  95 |
|       3 | Pear      |    66 |      87 |  38 |

(请注意，Orange 在 Nutrient_Data 表中没有“碳水化合物”条目)

Answer 1

我相信以下内容会满足您的要求:-

DROP TABLE IF EXISTS food_list;
CREATE TABLE IF NOT EXISTS food_list(food_id INTEGER PRIMARY KEY, name TEXT);
DROP TABLE IF EXISTS nutrient_definition;
CREATE TABLE IF NOT EXISTS nutrient_definition(nutrient_id INTEGER PRIMARY KEY, name TEXT);
DROP TABLE IF EXISTS nutrient_data;
CREATE TABLE IF NOT EXISTS nutrient_data(food_id INTEGER, nutrient_id INTEGER, value INTEGER);

INSERT INTO food_list (name) VALUES
    ('apple'),('orange'),('pear')
;
INSERT INTO nutrient_definition (name) VALUES
    ('carbs'),('protien'),('fat')
;
INSERT INTO nutrient_data VALUES
    (1,1,50),(1,2,24),(1,3,63),
    (2,2,12),(2,3,95),
    (3,1,66),(3,2,87),(3,3,38)
;
SELECT food_list.food_id,food_list.name, 

    (
    SELECT value 
    FROM nutrient_data 
    WHERE nutrient_data.food_id = food_list.food_id AND 
        nutrient_data.nutrient_id = (SELECT nutrient_definition.nutrient_id FROM nutrient_definition WHERE nutrient_definition.name = 'carbs')
    ),
    (
    SELECT value 
    FROM nutrient_data 
    WHERE nutrient_data.food_id = food_list.food_id AND 
        nutrient_data.nutrient_id = (SELECT nutrient_definition.nutrient_id FROM nutrient_definition WHERE nutrient_definition.name = 'protien')
    ),
    (
    SELECT value 
    FROM nutrient_data 
    WHERE nutrient_data.food_id = food_list.food_id AND 
        nutrient_data.nutrient_id = (SELECT nutrient_definition.nutrient_id FROM nutrient_definition WHERE nutrient_definition.name = 'fat')
    )

 FROM food_list
 ;

结果是 :-