在使用UNION SELECT时删除重复的结果 [英] Removing duplicate results while using UNION SELECT

问题描述

假设我们有两个表：

id qty

<1> 1

<2> 1 ...... p>

<1> 3 ...... 1

<4> 3

和

id qty

<1> 1 ...... 2

<1> 2 ...... 1

6 ...... 1

7 ...... 2

我实际需要得到的结果是由id过滤的结果，所以有没有重复的ID。如果他们是两个相同的ID，只有身份证号码较高的数字才会被考虑。所以在理想情况下我会得到这样的结果：
$ b $ h2 id qty

1 ...... 2 b
$ b $ 2 ...... 1

3 ...... 1

4 ...... 3

<6> 1

7 ...... 2

到目前为止我所尝试的是UNION SELECT，它工作正常，但是当它遇到一个重复的ID它不会删除重复，因为它删除重复的行。 GROUP BY也没有帮助。我仍然可以通过PHP中的数组对它进行排序，但如果可能的话，我希望在MySQL级别进行排序。

感谢大家为您提供的帮助，非常感谢！

解决方案

GROUP BY没有帮助

真的吗？

  SELECT id，MAX（qty）as qty 
 FROM 
（
 SELECT id，qty FROM table1 
 UNION ALL 
 SELECT id，qty FROM table2 
）T1 
 GROUP BY id 
  

Ive got a problem with a MySQL query.

Let's say, we have two tables:

id qty

1......1

2......1

3......1

4......3

and

id qty

1......2

2......1

6......1

7......2

What I actually need to get is a result filtered by id, so there are no duplicate id's. If they are two same id's, only id whith a higher qty number will be considered. So in and ideal situation I would get this result:

id qty

1......2

2......1

3......1

4......3

6......1

7......2

What Ive tried so far is a UNION SELECT, which works fine, but when it comes across a duplicate ID it wont remove duplicates, as it removes just duplicates rows. GROUP BY didnt help either. I could still sort this out through an array in PHP, but I would love having this sorted at MySQL level if possible.

Thanks everyone for you help, it's much appreciated!

解决方案

GROUP BY didnt help either

Really? Did you try like this?

SELECT id, MAX(qty) AS qty
FROM
(
    SELECT id, qty FROM table1
    UNION ALL
    SELECT id, qty FROM table2
) T1
GROUP BY id

这篇关于在使用UNION SELECT时删除重复的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！