如何限制MySQL查询中的重复列结果？

I'm having difficulty removing the duplicate data when exporting an order where more than 1 product is ordered.

导出订购了超过1个产品的订单时，我很难删除重复数据。

Example:

例：

orders_id, product_id, customer_first_name, customer_last_name
001     , ProductA  , FirstName          , LastName
001     , ProductB  , FirstName          , LastName
001     , ProductC  , FirstName          , LastName
002     , ProductA  , FirstName          , LastName
003     , ProductB  , FirstName          , LastName
003     , ProductC  , FirstName          , LastName
004     , ProductA  , FirstName          , LastName

I need to remove the duplicating data, like so:

我需要删除重复数据，如下所示：

orders_id, product_id, customer_first_name, customer_last_name
001     , ProductA  , FirstName          , LastName
        , ProductB  ,                    ,          
        , ProductC  ,                    ,          
002     , ProductA  , FirstName          , LastName
003     , ProductB  , FirstName          , LastName
        , ProductC  ,                    ,          
004     , ProductA  , FirstName          , LastName

I tried DISTINCT, without any change.

我尝试了DISTINCT，没有任何改变。

Assuming the query to extract the above is as follows:

假设查询提取上面的内容如下：

SELECT 
o.orders_id
, op.product_id
, o.customer_first_name
, o.customer_last_name
FROM orders AS o
, orders_products AS op
WHERE o.orders_id = op.orders_id;

How would I limit the data from duplicating?

如何限制数据重复？

Much appreciated!

非常感激！

Peace, Chris

和平，克里斯

5 个解决方案

#1

This is a display problem and not an SQL one. It could be solved with SQL but the query would be rather horrible (in MySQL).

这是一个显示问题，而不是SQL问题。它可以用SQL解决，但查询会相当糟糕（在MySQL中）。

One way to solve is to change the display a bit by using @Bauhaus advice and GROUP_CONCAT() function.

一种解决方法是使用@Bauhaus建议和GROUP_CONCAT（）函数稍微改变显示。

Another way would be to change the application code to combine the results of two queries:

另一种方法是更改​​应用程序代码以组合两个查询的结果：

--- Get orders
SELECT 
    o.orders_id
                                         --- REMOVED: op.product_id
  , o.customer_first_name
  , o.customer_last_name
FROM orders AS o
ORDER BY o.orders_id ;                   --- Order both queries by orders_id
                                         --- to help the application code 
                                         --- match related data

--- Get order details (products)
SELECT 
    o.orders_id
  , op.product_id
                                         --- REMOVED: o.customer_first_name
                                         --- REMOVED: o.customer_last_name
FROM orders AS o
  JOIN orders_products AS op             --- Don't use WHERE for joining tables,
    ON o.orders_id = op.orders_id        --- use the JOIN ... ON syntax
ORDER BY o.orders_id ;


--- Combine results
/* application code */

#2

The problem is with how your order_products table is constructed. Due to the fact that the order_id is duplicated several times in that table you'll return equally many rows whenever you join against order_id

问题在于您的order_products表的构造方式。由于order_id在该表中多次重复，因此每当您加入order_id时，您将返回相同数量的行

I'm assuming the table looks something like this:

我假设桌子看起来像这样：

order_id       product_id
001            ProductA
001            ProductB
001            ProductC

Which will always return 3 rows for every product_id with the current query.

对于当前查询，每个product_id将始终返回3行。

You're probably better of returning a flattened "Products" field that concatinates the product_ids together using the Group_Concat command:

您可能更好地返回使用Group_Concat命令将product_ids连接在一起的扁平化“Products”字段：

SELECT o.orders_id
    ,GROUP_CONCAT(DISTINCT op.product_id SEPARATOR ', ')
FROM orders AS o
, orders_products AS op
WHERE o.orders_id = op.orders_id
GROUP BY o.orders_id;

This will return something similar to the following:

这将返回类似于以下内容：

order_id       products
001            ProductA, ProductB, ProductC

#3

Check out DISTINCT on o.orders_id when doing your select statement. http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

在执行select语句时，请查看o.orders_id上的DISTINCT。 http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

Update..sorry I didn't see you had tried distinct already. Maybe try GroupBy http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html and group by the order_id.

更新......我没有看到你已经尝试过了。也许尝试GroupBy http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html并按order_id分组。

#4

If I understand what you're asking, you're still trying to get back a complete list of all products from all orders.

如果我理解您的要求，您仍然会尝试从所有订单中获取所有产品的完整列表。

You basically have two options:

你基本上有两个选择：

Send lots and lots of queries at the server, using a nested loops:

使用嵌套循环在服务器上发送大量查询：

1. Get a listing from the orders table
2. 从订单表中获取列表
3. For each record, ask for the associated records from the orders_products table.
4. 对于每条记录，请询问orders_products表中的相关记录。
5. Print the first line w/ the merged info from the first correlated record
6. 使用第一个相关记录中的合并信息打印第一行
7. Loop through the rest of the results, printing only the product information
8. 循环显示其余结果，仅打印产品信息

What's often more efficient is to just get it all, then use some if/then logic:

通常更高效的是获取所有内容，然后使用一些if / then逻辑：

1. initialize a variable to track the last order_id.
2. 初始化变量以跟踪最后的order_id。
3. loop through the merged list (what you started with)
4. 遍历合并列表（你开始的）
5. if the current order_id isn't the same as the last order_id, print the full record ... otherwise, only print the product info.
6. 如果当前order_id与上一个order_id不同，则打印完整记录...否则，仅打印产品信息。
7. update the last order_id w/ the current order_id.
8. 使用当前order_id更新最后一个order_id。

It's possible that PHP might have a way to specifically handle the second case. It's been years since I used it, but it was the recommended way in ColdFusion ... look at the 'group' argument to 'cfoutput'.

PHP可能有办法专门处理第二种情况。我使用它已经好几年了，但它是ColdFusion中推荐的方式......看看'cfoutput'的'group'参数。

#5

You don't have any duplicate results, your getting exactly what your asking for.

你没有任何重复的结果，你得到的正是你的要求。

Since and order can have many products you are getting all the products that belong to an order but the order id will always be duplicated, there is no way in relational databases to get it all in a single row unless you flatten your results.

既然和订单可以有很多产品，你得到的所有产品都属于一个订单，但订单ID将永远是重复的，关系数据库中没有办法将它全部放在一行，除非你压扁你的结果。