从带有一些重复记录的2个MySQL表中查询数据 [英] Query Data from 2 MySQL tables with some duplicate records

问题描述

我有2张桌子跟踪奖励积分.一个是汇总表，一个是明细表.假设发生一笔交易(加减积分)时，一个条目被写到两个表中.不幸的是，我们通过脚本错误发现有时一个条目只写到一个表中.

I have 2 tables that track rewards points. One is a summary table and one is a detail table. When a transaction (addition or subtraction of reward points)happens an entry is suppose to be written to both tables Unfortunately we found through a scripting error that sometimes an entry was only written to one table.

因此，现在我需要查询数据库以列出事务，而无需重复重复项.表中没有匹配的ID，但是DateSubmitted是一个时间戳，两个表之间的所有匹配记录共享相同的时间戳.

So now I need to query the database to list the transactions, without repeating the duplicates. There are not matching IDs in the table, however the DateSubmitted is a timestamp and all matching records between the 2 table share the same timestamp.

所以，我有2个查询

SELECT CustID, DateSubmitted, Type, Points
FROM `trans_summary`
WHERE CustID = '10009'

和

SELECT CustID, DateSubmitted, Type, PointTotal as Points
FROM `ptrans_detail` 
WHERE CustID = '10009'
  and DateSubmitted NOT IN 
        (SELECT DateSubmitted FROM `trans_summary` 
         WHERE CustID = '10009'
        )

如何结合这两个查询来获得一个输出?

How can I combine these 2 queries to get one output?

推荐答案

如果两者都需要不同的结果，则可以使用union；如果需要同时也需要重复的结果，则可以全部并入

you can use union if you need distinct result for both or union all if need also duplicated result

      SELECT CustID
          , DateSubmitted
          , Type
          , Points
              FROM `trans_summary`
                WHERE CustID = '10009'
    UNION

    SELECT CustID
    , DateSubmitted
    , Type
    , PointTotal 
        FROM `ptrans_detail` 
           WHERE CustID = '10009'
                and DateSubmitted NOT IN 
               (SELECT DateSubmitted FROM 
                 `trans_summary` 
                  WHERE CustID = '10009')

或在需要时也合并所有结果

or union all if need also duplicated result

      SELECT CustID
          , DateSubmitted
          , Type
          , Points
              FROM `trans_summary`
                WHERE CustID = '10009'
    UNION ALL 

    SELECT CustID
    , DateSubmitted
    , Type
    , PointTotal 
        FROM `ptrans_detail` 
           WHERE CustID = '10009'
                and DateSubmitted NOT IN 
               (SELECT DateSubmitted FROM 
                 `trans_summary` 
                  WHERE CustID = '10009')

这篇关于从带有一些重复记录的2个MySQL表中查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！