如何用union合并SQL查询的数据

[我才是小花]

为什么查询出来的结果相同的TJBH的数据合并不成一条数据（TJBH是唯一的，不会出现两个TJBH相同的数据），我用了union也合并不了，union all也合并不了

SELECT h.DABH,h.TJBH,h.tw,h.sg
FROM(
SELECT a.DABH,a.TJBH,cast(g.jg as varchar(8000)) as tw,'0' as sg
FROM TJ_TJDJB a,tj_tjjlmxb g  
WHERE ( a.tjbh = g.tjbh ) and
		g.xmbh = '001' and
		a.tjrq>= '2016-7-18' and a.tjrq<= '2016-7-18'

union all

SELECT a.DABH,a.TJBH,'0' as tw,cast(g.jg as varchar(8000)) as sg
FROM TJ_TJDJB a,tj_tjjlmxb g  
WHERE ( a.tjbh = g.tjbh ) and
		g.xmbh = '005' and
		a.tjrq>= '2016-7-18' and a.tjrq<= '2016-7-18'

) h
group by h.DABH,h.TJBH,h.tw,h.sg
order by h.TJBH

[卖水果的net]

union 和 union all 是按行叠加 你要把你的结果再加工一下 select DA, TJ, MAX(tw) , MAX(tg) from ( ) group by DA, TJ

[TurboWay]

union 是简单的把 查询结果加起来，并不是合并。 如果要合并起来，你可以这么写

SELECT  CASE WHEN a.DABH IS NULL THEN b.DABH ELSE a.DABH END AS DABH,
        CASE WHEN a.TJBH IS NULL THEN b.TJBH ELSE a.TJBH END AS TJBH,
        isnull(tw,0) AS tw,
        isnull(sg,0) AS sg
FROM    ( SELECT    a.DABH ,
                    a.TJBH ,
                    CAST(g.jg AS VARCHAR(8000)) AS tw ,
          FROM      TJ_TJDJB a ,
                    tj_tjjlmxb g
          WHERE     ( a.tjbh = g.tjbh )
                    AND g.xmbh = '001'
                    AND a.tjrq >= '2016-7-18'
                    AND a.tjrq <= '2016-7-18'
        ) a
        FULL JOIN ( SELECT  a.DABH ,
                            a.TJBH ,
                            CAST(g.jg AS VARCHAR(8000)) AS sg
                    FROM    TJ_TJDJB a ,
                            tj_tjjlmxb g
                    WHERE   ( a.tjbh = g.tjbh )
                            AND g.xmbh = '005'
                            AND a.tjrq >= '2016-7-18'
                            AND a.tjrq <= '2016-7-18'
                  ) b ON a.DABH = b.DABH
                         AND a.TJBH = b.TJBH

[Ginnnnnnnn]

SELECT h.DABH,h.TJBH,max(h.tw),max(h.sg)
FROM(
SELECT a.DABH,a.TJBH,cast(g.jg as varchar(8000)) as tw,'0' as sg
FROM TJ_TJDJB a,tj_tjjlmxb g  
WHERE ( a.tjbh = g.tjbh ) and
		g.xmbh = '001' and
		a.tjrq>= '2016-7-18' and a.tjrq<= '2016-7-18'

union all

SELECT a.DABH,a.TJBH,'0' as tw,cast(g.jg as varchar(8000)) as sg
FROM TJ_TJDJB a,tj_tjjlmxb g  
WHERE ( a.tjbh = g.tjbh ) and
		g.xmbh = '005' and
		a.tjrq>= '2016-7-18' and a.tjrq<= '2016-7-18'

) h
group by h.DABH,h.TJBH
order by h.TJBH