小白发帖——Sqlserver两条查询语句合并一条

[名叫厄运的烈马]

select '销售订单' as [Type1],a.FInterID 订单号,c.FName 客户,d.FName 部门,sum(b.金额) 金额,sum(b.数量) 数量 from [SEOrder] a
join(select FItemID,FInterID,sum(FAmount) 金额,sum(FQty) 数量 from [SEOrderEntry] group by FItemID,FInterID) b
on a.FInter=b.FInterID
join t_Organization c on c.FItemID=a.FCustID
join t_Department d on a.FDeptID=d.FItemID
group by a.FInterID,b.FItemID,c.FName,a.FCustID,d.FName,d.FItemID

select '采购订单' [Type2],a.FInterID 订单号,sum(b.金额) 采购金额,sum(b.数量) 采购数量,null,null from [POOrder] a join
(select FItemID,FInterID,sum(FAmount) 金额 ,sum(FQty) 数量 from [POOrderEntry] group by FItemID,FInterID) b
on a.FInterID=b.FInterID
join t_Department d on a.FDeptID=d.FItemID
group by a.FInterID,b.FItemID,d.FName,d.FItemID
ID
例如
t_table1 id name
1 a
t_table2 id name number
1 a 123

查询出来后
table id name number
1 a 123

[二月十六]

试试这个：

SELECT  *
FROM    ( SELECT    '销售订单' AS [Type1] ,
                    a.FInterID 订单号 ,
                    c.FName 客户 ,
                    d.FName 部门 ,
                    SUM(b.金额) 金额 ,
                    SUM(b.数量) 数量
          FROM      [SEOrder] a
                    JOIN ( SELECT   FItemID ,
                                    FInterID ,
                                    SUM(FAmount) 金额 ,
                                    SUM(FQty) 数量
                           FROM     [SEOrderEntry]
                           GROUP BY FItemID ,
                                    FInterID
                         ) b ON a.FInter = b.FInterID
                    JOIN t_Organization c ON c.FItemID = a.FCustID
                    JOIN t_Department d ON a.FDeptID = d.FItemID
          GROUP BY  a.FInterID ,
                    b.FItemID ,
                    c.FName ,
                    a.FCustID ,
                    d.FName ,
                    d.FItemID
        ) t1
        JOIN ( SELECT   '采购订单' [Type2] ,
                        a.FInterID 订单号 ,
                        SUM(b.金额) 采购金额 ,
                        SUM(b.数量) 采购数量 ,
                        NULL ,
                        NULL
               FROM     [POOrder] a
                        JOIN ( SELECT   FItemID ,
                                        FInterID ,
                                        SUM(FAmount) 金额 ,
                                        SUM(FQty) 数量
                               FROM     [POOrderEntry]
                               GROUP BY FItemID ,
                                        FInterID
                             ) b ON a.FInterID = b.FInterID
                        JOIN t_Department d ON a.FDeptID = d.FItemID
               GROUP BY a.FInterID ,
                        b.FItemID ,
                        d.FName ,
                        d.FItemID
             ) t2 ON t1.订单号 = t2.订单号

[学海无涯-回头是岸]

SELECT * FROM T1 (字段多的表) UNION ALL SELECT * FROM T2

[二月十六]

用两个集合的关联列进行关联即可，上边的语句是通过订单号关联的

[繁花尽流年]

引用 楼主 qq_39681334 的回复:

select '销售订单' as [Type1],a.FInterID 订单号,c.FName 客户,d.FName 部门,sum(b.金额) 金额,sum(b.数量) 数量 from [SEOrder] a join(select FItemID,FInterID,sum(FAmount) 金额,sum(FQty) 数量 from [SEOrderEntry] group by FItemID,FInterID) b on a.FInter=b.FInterID join t_Organization c on c.FItemID=a.FCustID join t_Department d on a.FDeptID=d.FItemID group by a.FInterID,b.FItemID,c.FName,a.FCustID,d.FName,d.FItemID select '采购订单' [Type2],a.FInterID 订单号,sum(b.金额) 采购金额,sum(b.数量) 采购数量,null,null from [POOrder] a join (select FItemID,FInterID,sum(FAmount) 金额 ,sum(FQty) 数量 from [POOrderEntry] group by FItemID,FInterID) b on a.FInterID=b.FInterID join t_Department d on a.FDeptID=d.FItemID group by a.FInterID,b.FItemID,d.FName,d.FItemID ID 例如 t_table1 id name 1 a t_table2 id name number 1 a 123 查询出来后 table id name number 1 a 123

select a.id,a.name,b.number from t_table1 a left join t_table2 b on a.id =b.id and a.name =b.name

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(21))
Insert #T1
select 1,N'a'
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[name] nvarchar(21),[number] int)
Insert #T2
select 1,N'a',123
Go
--测试数据结束
SELECT  *
FROM    #T1
        JOIN #T2 ON #T2.id = #T1.id;