将SQL Server 2008中的多个SQL查询组合到ASP上的数据表中.NET使用C#

也许这会奏效。我正在加入子查询。

select  isnull(a.SALES_NUM4, '') SalesNo, 
        count(CUS_ID) NumOfCust, 
        count(distinct sales_num) NumOfRep,
        count(ORD_NUM) MTD_Ord, 
        convert(decimal(10, 2), sum(ORD_AMT)) MTD_Amt
from 
    (select SALES_NUM4, CUS_ID, SLS_TYPE, SALES_NUM 
     from   customer
     union
     select SALES_NUM4, CUS_ID, SLS_TYPE, SALES_NUM 
     from   omsdata7.dbo.customer) as a
    inner join
    (select SALES_NUM4, ORD_DT, ORD_NUM, ORD_AMT 
     from   orders
     union
     select SALES_NUM4, ORD_DT, ORD_NUM, ORD_AMT 
     from   omsdata7.dbo.orders) as b
    on a.SALES_NUM4 = b.SALES_NUM4
where   a.SALES_NUM4 != '' and SLS_TYPE = '1' 
        and DATEADD(day, ORD_DT-4, '1801-01-01') >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
        and DATEADD(day, ORD_DT-4, '1801-01-01') < getdate()
group by    a.SALES_NUM4
order by   a.SALES_NUM4

    -- QUERY 1 #tmpReport1 with respect the grouping and sort order    
        select
            isnull(SALES_NUM4, '') SalesNo, 
            count(CUS_ID) NumOfCust, 
            count(distinct sales_num) NumOfRep
        into #tmpReport1
        from 
            (select 
                 SALES_NUM4, CUS_ID, SLS_TYPE, SALES_NUM 
             from customer
             union
             select 
                 SALES_NUM4, CUS_ID, SLS_TYPE, SALES_NUM 
             from omsdata7.dbo.customer) a
        where 
            SALES_NUM4 != '' and SLS_TYPE = '1'
        group by 
            SALES_NUM4
        order by 
            SALES_NUM4
    -- QUERY 2 BUILD #tmpReport2 with respect the grouping and sort order
    select 
        isnull(SALES_NUM4, '') SalesNo, 
        count(ORD_NUM) MTD_Ord, 
        convert(decimal(10, 2), sum(ORD_AMT)) MTD_Amt
    into #tmpReport2
    from 
        (select 
             SALES_NUM4, ORD_DT, ORD_NUM, ORD_AMT 
         from orders
         union
         select 
             SALES_NUM4, ORD_DT, ORD_NUM, ORD_AMT 
         from omsdata7.dbo.orders) a
    where 
        DATEADD(day, ORD_DT-4, '1801-01-01') >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
        and DATEADD(day, ORD_DT-4, '1801-01-01') < getdate()
        and SALES_NUM4 != ''
    group by 
        SALES_NUM4
    order by 
        SALES_NUM4
-- combine both tables in here
select * from #tmpReport1
union all
select * from #tmpReport2
-- drop temps
drop table #tmpReport1
drop table #tmpReport2

希望我能正确理解你的问题？

编辑：水平连接，然后与上面的脚本相同，除外

-- combine both tables in here
select * from #tmpReport1
union all
select * from #tmpReport2

更改为

-- combine both tables horizontally here
select 
   tmp1.*
   ,tmp2.* 
from #tmpReport1 tmp1
full join #tmpReport2 tmp2
ON
   tmp1.SalesNo = tmp2.SalesNo

并简单地根据您的规范识别您的列。

select isnull(SALES_NUM4,'') SalesNo, count(CUS_ID) NumOfCust, count(distinct SALES_NUM) NumOfReps, MAX(b.MTD_Ord) MTD_Ord, MAX(b.MTD_Amt) MTD_Amt
from (
select SALES_NUM4, CUS_ID, SLS_TYPE, SALES_NUM from customer
union
select SALES_NUM4, CUS_ID, SLS_TYPE, SALES_NUM from omsdata7.dbo.customer
where SLS_TYPE = '1'
) a
Left join 
(select isnull(SALES_NUM4,'') SalesNo, count(ORD_NUM) MTD_Ord, sum(ORD_AMT) MTD_Amt
from (
select SALES_NUM4, ORD_DT, ORD_NUM, ORD_AMT from orders
union
select SALES_NUM4, ORD_DT, ORD_NUM, ORD_AMT from omsdata7.dbo.orders
) b1
where DATEADD(day, ORD_DT-4, '1801-01-01') >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
and DATEADD(day, ORD_DT-4, '1801-01-01') < getdate() 
group by SALES_NUM4 
) b on a.SALES_NUM4 = b.SalesNo 
where SALES_NUM4 != ''
group by SALES_NUM4
order by SALES_NUM4