两个表的并集，但显示数据来自哪个表 [英] Union of two tables but show which table the data came from

问题描述

我有两张桌子:

           TABLE_A                 TABLE_B
Fields:    Trans   Amend           Trans   Amend 
data:      100       0             100      0
           100       1
           110       0         
                                   120      0
                                   120      1
           130       0             130      0 
                                   130      1
           140       0             140      0
           150       0             150      0
           150       1             150      1
           150       2             

我想要的是一个表格(视图)，它将把这些组合(联合)到表格中，但只会显示每个 Trans 的最高修正

What I want is a table (view) that will combine (union) these to tables but will only show the highest Amend for each Trans

寻找这个作为答案:

Fields:    Trans   Amend   
data:      100       1
           110       0
           120       1
           130       1
           140       0 
           150       2   

然后更难的是，我想知道是否有办法知道数据来自哪个表.当记录 A 和记录 B 相等时，表 A 总是获胜寻找这个作为答案:

Then to make it harder, I would like to know if there is a way I can tell from which table the data is coming from. Table A always wins when Record A and Record B are equal Looking for this as the answer:

Fields:    Trans   Amend    WhichTBL
data:      100       1      Table_A
           110       0      Table_A
           120       1      Table_B
           130       1      Table_B
           140       0      Table_A
           150       2      Table_A

我知道无法通过 UNION 来获得此结果.

I know a UNION can't be done to get this result.

推荐答案

这行得通吗?

SELECT 
    trans, MAX(max_amend) as max_max_amend
FROM
    (SELECT
        'a' AS src, trans, MAX(amend) AS max_amend
    FROM
        table_a
    GROUP BY
        trans

    UNION ALL

    SELECT
        'b' AS src, trans, MAX(amend) AS max_amend
    FROM
        table_b
    GROUP BY
        trans) m
GROUP BY
    trans

<小时>

Lucero 下面的观点是正确的，min(src) 将在全局集合上，而不是相关的 max()

我认为您必须将源值和表值合并到您可以最大化的一列中.在您的示例中，将值加 1 即可区分来源，例如:

---

Lucero's point below is correct, the min(src) would be on the global set, not the related max()

I think you'd have to combine the source and table values into one column you can max. In your example, adding 1 to the value is all you need to distinguish the sources, like:

SELECT trans, Max(amend) AS MaxOfamend, 1+[amend] AS isa, 0 AS isb
FROM TableA
GROUP BY trans

但您可以加 100，或乘以一个大值，或任何适用于您的数据的方法.这个想法是将修改值和来源这两条信息合并为一列.

but you could add 100, or multiply by a big value, or whatever works with your data. The idea is to combine the two pieces of information, the amend value and the source, into one column.

然后，在组合信息后，您获得该值的最大值，然后通过取消组合(减去 1，除以 100，等等)来剥离源标志

Then, after the information is combined, you get the max of that value, then strip off the source flag by uncombining them (subtracting 1, dividing by 100, whatever)

好的，这是我得到的:

CREATE VIEW [dbo].[viewA]    AS
SELECT trans, MAX(amend + .20) AS srcIsA, 0 AS srcIsb
FROM  dbo.tableA
GROUP BY trans

CREATE VIEW [dbo].[viewB]    AS
SELECT trans, 0 AS srcIsA, MAX(amend + .10) AS srcIsB
FROM  dbo.tableB
GROUP BY trans

CREATE VIEW [dbo].[viewU]    AS
SELECT * from viewA
union all
select *
FROM  viewb

CREATE VIEW [dbo].[viewv]    AS
SELECT trans, srcIsA, srcIsb, srcIsA + srcIsb AS total
FROM  dbo.viewU

CREATE VIEW [dbo].[vieww]    AS
SELECT trans, MAX(total) AS max_total
FROM  dbo.viewv
GROUP BY trans

CREATE VIEW [dbo].[viewx] AS
SELECT trans, 
max_total, 
CAST(max_total AS int) AS maxval, 
CASE WHEN (max_total - CAST(max_total AS int)) = .1 THEN 'a' ELSE 'b' END AS src
FROM  dbo.vieww

这篇关于两个表的并集，但显示数据来自哪个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！