SELECT * FROM 多个表.MySQL

2021-11-20 00:00:00 mysql multiple-tables

SELECT name, price, photo FROM Drinks, Drinks_photos WHERE Drinks.id = Drinks_id

产生 5 行(5 个数组)，photo 是一行中唯一的唯一字段.name, price 重复(这里， fanta- name, price 重复 3 次.) 我如何摆脱这些重复?

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

我想要每种饮料的名称、价格和所有照片.

I want name, price and all photo for each drink.

 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

推荐答案

你在这里做的事情叫做JOIN(虽然你隐式这样做是因为你从多个表中选择).这意味着，如果您没有在 WHERE 子句中放置任何条件，则您拥有这些表的所有组合.只有根据您的条件，您才能将连接限制为饮料 ID 匹配的那些行.

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 combinations of those tables. Only with your condition you restrict your join to those rows where the drink id matches.

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

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 which photo(s) you want to have!

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

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 分组(以便每杯饮料仅获得一行):

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 photos in your result for each drink, but SQL can't do this. If you only want any photo and you don't care which you'll get, just group by the drinks_id (in order to get only one row per drink):

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

在 MySQL 中，我们也有 GROUP_CONCAT，如果你想将文件名连接成一个字符串:

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

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

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

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.

相关文章