使用select，union和group的组合 [英] using combination of select, union and group by

问题描述

我有两个相似的表和第三个表，其中有一个共享列（sms_status_id），第一个和第二个表。我想从第一个和第二个表中选择相似的列，将它们与第三个表合并，然后删除重复的行。
这就是我迄今为止所做的：

I have two similar tables and a third table with one shared column (sms_status_id) with first and second table. I want to select similar columns from first and second table, union them with third table and then remove repetitive rows. This is what I've done so far:

select *
from
(
    select sms_log_id, atm_id, device_id, error_id, datetime, retries, message
    from first_table
    join third_table
    on sms_log_id = sms_status_id
)
union
(
    select sms_id, atm_id, device_id, error_id, datetime, retries, message
    from second_table
    join third_table
    on sms_id = sms_status_id
 )

这给了我我想要的，但结果有重复的行。我尝试使用

This gives me what I want but the result has repetitive rows. I tried to use

GROUP BY sms_status_id

但我不知道我应该把它放在哪里，所以没有解决。任何建议??

but I didn't know where should I put it so didn't work out. Any suggestions??

这是w

推荐答案

错误在您的查询。

请参阅下面的代码，如果这将回答您的问题。

Please see below code, if this will answer to your question.

@dnoeth是正确的， UNION 总是返回不同的行，否则使用 UNION ALL 。

@dnoeth is right, UNION always returns distinct rows otherwise use UNION ALL.

mytable1

sms_log_id     | Detail
1                a
1                b
1                c

mytable2

sms_id     | Detail
1            a
2            b
1            d
1            e

mytable3

id     | Status
1        Approve
2        Disapprove

查询：

SELECT t1.sms_log_id as t3_id, t1.Detail, t3.Status
FROM mytable1 as t1
LEFT JOIN mytable3 as t3
  ON t1.sms_log_id = t3.id

UNION

SELECT t2.sms_id as t3_id, t2.Detail, t3.Status
FROM mytable2 as t2
LEFT JOIN mytable3 as t3
  ON t2.sms_id = t3.id

结果：

Result:

t3_id   Detail  Status
1       a       Approve
1       b       Approve
1       c       Approve
2       b       Disapprove
1       d       Approve
1       e       Approve

你不能在mytable2中看到结果 a ，因为它已经在mytable1中，并且打印 b ，因为它有不同的地位。

you can't see in the result a from mytable2 because it is already in mytable1, and b is printed because it has different status.

演示

这篇关于使用select，union和group的组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！