Server:连接2个表，更倾向于在有冲突的情况下从一个表中得到结果

Question

我有几张桌子看起来像这样

tblConsuptionsFromA

id/T1582-1995工业税转制转帐转帐 1.=‘1’> 2 3._‘_ 4._ 5._‘_ 6

还有..。

tblConsuptionsFromB

id/T1582-1995工业税转制转帐转帐 1/01/01/2014 2.=‘1’>. 3._~_ 4.=‘1’>. 5._‘_ 6 7附属产品 8._ 9/09/01/2014 10 11/01/2014

我需要加入这两个表，但是如果两个表中都有同一天的条目.只接受tblConsumptionsFromA的结果。

结果是：-

id source_id  meter  from       date        total 1 2 3 4 5-暗 6-暗 7/7 8 9 10 11

我不知道，所以如果有人能解决..。我会印象深刻的。

Answer 1

UNION操作符用于组合两个或多个SELECT语句的结果集。

 SELECT column_name(s) FROM table1
 UNION
 SELECT column_name(s) FROM table2;

联合的文件在这里：union.asp

ROW_NUMBER()返回结果集分区内一行的顺序数，每个分区的第一行从1开始。

 ROW_NUMBER ( ) 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

ROW_NUMBER()的文档在这里：http://technet.microsoft.com/en-us/library/ms186734.aspx

下面的SQL语句使用UNION从"tblConsuptionsFromA“中选择所有记录，并从"tblConsuptionsFromB”表中选择部分记录。

SELECT ROW_NUMBER() OVER(ORDER BY DATE ASC) AS 'id', 
id AS 'source_id',meter, date,t AS 'from',total 
FROM(
    SELECT id,meter, date, 'A' AS t, total FROM tblConsuptionsFromA
UNION 
    SELECT id,meter, date, 'B' AS t,total FROM tblConsuptionsFromB
    WHERE NOT date IN (SELECT date FROM tblConsuptionsFromA) 
) AS C;

希望这能有所帮助。

Answer 2

有一种方法可以做到：

SELECT
    COALESCE(a.source_id,b.source_id) as source_id,
    COALESCE(a.meter,b.meter) as meter,
    COALESCE(a.[from],b.[from]) as [from],
    COALESCE(a.[date],b.[date]) as [date],
    COALESCE(a.total,b.total)
FROM (select source_id,meter,'b' as [from],[date],total
       from tblConsuptionsFromB) b
         left join
     (select source_id,meter,'a' as [from],[date],total
       from tblConsuptionsFromA) a
         on
            a.meter = b.meter and
            a.[date] = b.[date]

不幸的是，没有像COALESCE(a.*,b.*)这样的速记来将COALESCE应用于所有列

Answer 3

select ta.id, tb.id, ta.meter,
    if(ta.date is null, 'B', 'A') as from,
    if(ta.date is null, tb.date, ta.date) as date,
    if(ta.date is null, tb.total, ta.total) as total
from tblConsuptionsFromA ta
    full join tblConsuptionsFromB tb on ta.date=tb.date