我希望使用SQL,但不理解联合操作符的使用。我的目标是为每个冰淇淋检索3列:ice_cream_id, ice_cream_name, manufacturer_name。这些应该根据ice_cream_id升序排列。例如,如果我有纯香草冰淇淋,我不想认同所有三个制造商,而只想认同第一个。
我理解SELECT和FROM命令,但不理解WHERE中的联合操作符:SELECT ice_cream_id, ice_cream_name, manufacturer_name FROM ice_cream, manufacturer .
另外,我应该检索制造成本大于1的所有冰淇淋的id、名称、制造成本和制造商名称。我想我也应该用WHERE ?
manufacturer
manufacturer_id manufacturer_name country
--------------- ----------------- ----------
1 Ben & Jerry's Canada
2 4 Friends Finland
3 Gelatron Italy
ice_cream
ice_cream_id ice_cream_name manufacturer_id manufacturing_cost
------------ ---------------- --------------- ------------------
1 Plain Vanilla 1 1
2 Vegan Vanilla 2 0.89
3 Super Strawberry 2 1.44
4 Very plain 2 1.2
ingredient
ingredient_id ingredient_name kcal protein plant_based
------------- --------------- ---------- ---------- -----------
1 Cream 400 3 0
2 Coconut cream 230 2.3 1
3 Sugar 387 0 1
4 Vanilla extract 12 0 1
5 Strawberry 33 0.7 1
6 Dark chocolate 535 8 1
contains
ice_cream_id ingredient_id quantity
------------ ------------- ----------
1 1 70
1 3 27
1 4 3
2 2 74
2 3 21
2 4 5
3 1 60
3 3 10
3 5 30
4 2 95
4 4 5
SELECT ice_cream_id, ice_cream_name, manufacturer_name from manufacturer JOIN
contains ON manufacturer.ice_cream_id = contains.ice_cream_id LEFT JOIN ingredient
ON contains.ingredient_id = ingredient.ingredient_id ORDER BY ice_cream_id ASC;
请试试这个,看看是否有帮助。如果没有,请告诉我。我来帮你。
使用join可以将两个或多个表中的行合并在一起。
在你的例子中:
SELECT i.ice_cream_id, i.ice_cream_name, m.manufacturer_name
FROM ice_cream i INNER JOIN manufacturer m ON i.manufacturer_id = m.manufacturer_id
ORDER BY i.ice_cream_id;
使用INNER JOIN可以合并两个表中需要值的行。所以NULL是无效的
不需要在where子句中JOIN两个表。这样做:
SELECT ic.ice_cream_id, ic.ice_cream_name, mf.manufacturer_name
FROM ice_cream ic
JOIN manufacturer mf on ic.manufacturer_id = mf.manufacturer_id
在本例中,ic
和mf
是您使用的表的别名。您可以根据需要使用不同的JOIN命令(如LEFT JOIN或RIGHT JOIN…)。要连接两个表,需要使用两个表中相同的列。有时您需要使用多个列来连接两个表…
您可以使用这两个查询来获得您想要的结果集。
--retrieve 3 columns for each ice cream
SELECT ice.ice_cream_id,
ice.ice_cream_name,
man.manufacturer_name
FROM manufacturer AS man
INNER JOIN ice_cream AS ice
ON ice.manufacturer_id = man.manufacturer_id
ORDER BY ice.ice_cream_id ASC;
---Costs greater than 1
SELECT *
FROM
(
SELECT man.manufacturer_id,
ice.ice_cream_name,
man.manufacturer_name,
SUM(ice.nufacturing_cost) AS manufacturing_cost
FROM manufacturer AS man
INNER JOIN ice_cream AS ice
ON ice.manufacturer_id = man.manufacturer_id
GROUP BY man.manufacturer_id,
man.manufacturer_name,
ice.ice_cream_name
) AS TotalCost
WHERE TotalCost.manufacturing_cost > 1;