MySQL-从多个表中选择而不产生重复数据 [英] Mysql - select from multiple tables without producing duplicate data

问题描述

我有三个表，我想从表中选择一个而不重复.

I have three tables and I would like to select from the tables without producing duplicates.

表如下:

客户

id     |     name   |     lastName  
---------------------------------------
1      |    john    |      doe
2      |    helen   |      keller    

订单

userID列是引用John Doe的外键，因此John订购了3件商品.

The userID column is a foreign key that references John Doe, so John orders 3 items.

id     |   userID   |     order 
---------------------------------------
1      |    1       |      pizza
2      |    1       |      pasta
3      |    1       |      lasagna    

客户评分

userID列是引用John Doe的外键，因此John留下了5条评论.

The userID column is a foreign key that references John Doe, so John leaves 5 reviews.

 id     |   userID  |     rating   |  comment
-------------------------------------------------
1      |    1       |      5/5     |  was good
2      |    1       |      5/5     |  excellent
3      |    1       |      4/5     |  great
4      |    1       |      4/5     |  great
5      |    1       |      4/5     |  great

如何从3个表中选择可以得到如下所示的返回结果的表?

How would I select from the 3 tables where I can get a return results that look like this?

id     |     name   |     lastName    |   order      |   rating
-----------------------------------------------------------------
1      |    john    |      doe        |   pasta      |   5/5
       |            |                 |   pizza      |   5/5
       |            |                 |   lasagna    |   4/5
       |            |                 |              |   4/5
       |            |                 |              |   4/5

我尝试加入这些表，但是由于John留下了5条评论并且仅排序了3次，因此id，name，lastName和order列中填充了重复数据.

I've tried joining these tables, but since John has left 5 reviews and only ordered 3 times, the id, name,lastName, and order columns gets filled with duplicate data.

谢谢！

推荐答案

我对MySQL没有任何经验，但是我认为它的工作方式类似于MSSQL.

I don't have any experience in MySQL but I assume that it works similar to MSSQL.

因此，您期望输出的格式是不可能的.您宁可使用逗号分隔来获取订单和评级列的值

So the format in which you are expecting the output is not possible. You can rather get the order and rating column values as comma separated

此处是一种类似的问题，可能会对您有所帮助

Here is a similar kind of question that might help you

包括基于链接的示例 尝试这样的事情

including example based on link try something like this

SELECT Customers.id, Customers.name, Customers.lastName, GROUP_CONCAT(Orders.order) OrderedDishes, GROUP_CONCAT(CustomerRating.rating) RatingsGiven FROM ..... rest of your query .....

SELECT Customers.id, Customers.name, Customers.lastName, GROUP_CONCAT(Orders.order) OrderedDishes, GROUP_CONCAT(CustomerRating.rating) RatingsGiven FROM ..... rest of your query .....

这篇关于MySQL-从多个表中选择而不产生重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！