SELECT * FROM 多个表。MySQL

SELECT * FROM multiple tables. MySQL

提问by NestedWeb

SELECT name, price, photo FROM drinks, drinks_photos WHERE drinks.id = drinks_id

SELECT name, price, photo FROM drinks, drinks_photos WHERE drinks.id = drinks_id

yeilds 5 rows (5 arrays), photois the only unique field in a row. name, priceget repeated (here,fanta-name, pricerepeat 3 times.) How do i get rid of these duplicates?

产生 5 行（5 个数组），photo是一行中唯一的唯一字段。name, price重复（在这里，fanta-name, price重复 3 次。）我如何摆脱这些重复？

Edit:I want name, priceand all photofor each drink.

编辑：我想name, price和所有photo为每个饮料。

 id      name      price
  1.    fanta        5
  2.     dew         4


 id      photo                   drinks_id
  1.     ./images/fanta-1.jpg      1
  2.     ./images/fanta-2.jpg      1  
  3.     ./images/fanta-3.jpg      1 
  4.     ./images/dew-1.jpg        2
  5.     ./images/dew-2.jpg        2

回答by leemes

What you do here is called a JOIN(although you do it implicitly because you select from multiple tables). This means, if you didn't put any conditions in your WHERE clause, you had all combinationsof those tables. Only with your condition you restrict your join to those rows where the drink id matches.

您在此处所做的称为 a JOIN（尽管您隐式执行此操作是因为您从多个表中进行选择）。这意味着，如果您没有在 WHERE 子句中放置任何条件，则您拥有这些表的所有组合。只有在您的条件下，您才能将加入限制为饮料 ID 匹配的那些行。

But there are still X multiple rows in the result for every drink, if there are X photos with this particular drinks_id. Your statement doesn't restrict whichphoto(s) you want to have!

但是，如果有 X 张带有此特定 Drinks_id 的照片，则每种饮料的结果中仍然有 X 多行。您的声明不会限制您想要拥有哪些照片！

If you only want one row per drink, you have to tell SQL what you want to do if there are multiple rows with a particular drinks_id. For this you need grouping and an aggregate function. You tell SQL which entries you want to group together (for example all equal drinks_ids) and in the SELECT, you have to tell which of the distinct entries for each grouped result row should be taken. For numbers, this can be average, minimum, maximum (to name some).

如果你只想要每杯饮料一行，你必须告诉 SQL 如果有多行具有特定的drinks_id，你想做什么。为此，您需要分组和聚合函数。您告诉 SQL 要将哪些条目组合在一起（例如所有相等的 Drinks_ids），并且在 SELECT 中，您必须告诉每个分组结果行的哪些不同条目应该被采用。对于数字，这可以是平均值、最小值、最大值（仅举几例）。

In your case, I can't see the sense to query the photos for drinks if you only want one row. You probably thought you could have an array of photosin your result for each drink, but SQL can't do this. If you only want anyphoto and you don't care which you'll get, just group by the drinks_id (in order to get only one row per drink):

在你的情况下，如果你只想要一行，我看不出查询照片中的饮料的意义。您可能认为您可以在结果中为每种饮料提供一系列照片，但 SQL 无法做到这一点。如果您只想要任何照片并且不关心会得到哪张照片，只需按drinks_id 分组（以便每杯饮料仅获得一行）：

SELECT name, price, photo
FROM drinks, drinks_photos
WHERE drinks.id = drinks_id 
GROUP BY drinks_id

name     price   photo
fanta    5       ./images/fanta-1.jpg
dew      4       ./images/dew-1.jpg

In MySQL, we also have GROUP_CONCAT, if you want the file names to be concatenated to one single string:

在 MySQL 中，我们还有GROUP_CONCAT，如果您希望将文件名连接为一个字符串：

SELECT name, price, GROUP_CONCAT(photo, ',')
FROM drinks, drinks_photos
WHERE drinks.id = drinks_id 
GROUP BY drinks_id

name     price   photo
fanta    5       ./images/fanta-1.jpg,./images/fanta-2.jpg,./images/fanta-3.jpg
dew      4       ./images/dew-1.jpg,./images/dew-2.jpg

However, this can get dangerous if you have ,within the field values, since most likely you want to split this again on the client side. It is also not a standard SQL aggregate function.

但是，如果您有,字段值，这可能会变得危险，因为很可能您想在客户端再次拆分它。它也不是标准的 SQL 聚合函数。

回答by AnandPhadke

You will have the duplicate values for name and price here. And ids are duplicate in the drinks_photos table.There is no way you can avoid them.Also what exactly you want the output ?

您将在此处获得名称和价格的重复值。并且 id 在drinks_photos 表中是重复的。您无法避免它们。此外，您到底想要什么输出？

回答by bhovhannes

In order to get rid of duplicates, you can group by drinks.id. But that way you'll get only one photo for each drinks.id(which photo you'll get depends on database internal implementation).

为了摆脱重复，您可以按 分组drinks.id。但是这样你只能得到一张照片drinks.id（你得到的照片取决于数据库内部实现）。

Though it is not documented, in case of MySQL, you'll get the photo with lowest id(in my experience I've never seen other behavior).

虽然没有记录在案，但在 MySQL 的情况下，您将获得最低的照片id（根据我的经验，我从未见过其他行为）。

SELECT name, price, photo 
FROM drinks, drinks_photos 
WHERE drinks.id = drinks_id
GROUP BY drinks.id