当某些列具有不同的值时，将所有联合的结果合并为一行 [英] Combing results from union all into one row when some columns have different values

问题描述

我正在使用 union all 来组合两个 select 语句的结果.我正在尝试按 date_requested 列对结果进行分组.如果日期相同，我需要将两个查询合并为一行.现在，它在同一日期保留两行.这是因为两行之间的 tracking_id 列不同.由于我不担心 tracking_id ，我该如何绕过它并将它们组合起来?谢谢.

I'm using union all to combine the results of two select statements. I'm trying to group the results by the date_requested column. I need to combine both queries into one row if the dates are the same. Right now, it's keeping two rows for the same date. This is because the tracking_id column is different between the two rows. Since I'm not worried about tracking_id for this, how do I bypass this and combine them anyway? Thanks.

(select trv.requested_date, trv.requested_status
from  tbl_trackvalue as trv ,tbl_tracking as t , tbl_offers as off , tblusers as usr
where t.id=trv.tracking_id  and off.id=t.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
and trv.payment_status='pending' and trv.requested_status='declined'  group by trv.tr_user_id, trv.requested_date order by trv.requested_date asc )
union all 
(select mlc.requested_date, mlc.requested_status
from  tbl_trackvalue as trv ,tbl_tracking as t , tbl_offers as off , tblusers as usr, tbl_mailchimp_trackvalue as mlc
where trv.tracking_id=mlc.tracking_id  and off.id=t.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
and mlc.payment_status='pending' and mlc.requested_status='declined'  group by trv.tr_user_id, mlc.requested_date order by mlc.requested_date asc ) 

更新代码:

select requested_date, requested_status
from (select trv.requested_date as requested_date, trv.requested_status as requested_status
        from  tbl_trackvalue as trv ,tbl_tracking as trk , tbl_offers as off , tblusers as usr
        where trk.id=trv.tracking_id  and off.id=trk.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
        and trv.payment_status='pending' and trv.requested_status='declined'  group by trv.requested_date asc 
        union all
        select mlc.requested_date as requested date, mlc.requested_status as requested_status
        from  tbl_trackvalue as trv ,tbl_tracking as trk , tbl_offers as off , tblusers as usr, tbl_mailchimp_trackvalue as mlc
        where trv.tracking_id=mlc.tracking_id  and off.id=trk.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
        and mlc.payment_status='pending' and mlc.requested_status='declined'  group by mlc.requested_date asc ) t 
group by requested_date

UDPATE II:我从选择中删除了别名，这似乎修复了语法错误.我将进行更多测试以确保它适用于各种情况.谢谢您的帮助.我不知道你不能为你的选择使用别名.

UDPATE II: I deleted the aliases from the selections and that seems to have fixed the syntax error. I'll do more testing to make sure it works for a variety of cases. Thanks for the help. I didn't know you can't use aliases for your selects.

推荐答案

如果除了 tracking_id 之外的行完全相同，只需将其从列表中删除并使用 UNION 而不是 UNION全部.如果您有其他差异，您可能更喜欢在最终结果上使用 GROUP BY，以便您可以对其他不同的值使用聚合函数.

If the rows are exactly the same except for tracking_id, just remove that from your list and use UNION instead of UNION ALL. If you have other differences, you may prefer to use GROUP BY on your final results, so that you can use aggregate functions on other values that are different.

这篇关于当某些列具有不同的值时，将所有联合的结果合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！