mysql如何选择多个表,选择*从多个表。的MySQL

超、凢脫俗 2023-01-24 03:30 38阅读 0赞

![Image 1][]

SELECT name, price, photo FROM drinks, drinks\_photos WHERE drinks.id = drinks\_id

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?

Edit: 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.

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!

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).

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

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

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.

[Image 1]: