选择*从多个表.的MySQL [英] SELECT * FROM multiple tables. MySQL

问题描述

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?

编辑:我希望每杯饮料都包含name, price和所有photo.

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

推荐答案

此处执行的操作称为

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中，您必须告诉每个分组结果行应采用哪些不同的条目.对于数字，这可以是平均值，最小值，最大值(仅举几例).

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.

这篇关于选择*从多个表.的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！