问题描述
我有两张桌子:
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 equalLooking 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
这篇关于两个表的并集,但显示数据来自哪个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!