sql-server – 如何在存储过程中将两个查询联合起来以获得一行结果

我有一个存储过程,我加入了两个查询.我的疑问是

alter PROCEDURE test

@SDate datetime,@EDate datetime

如

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
(Select count(quoteid) as TotalQuote,sum(totalamount) as QuoteAmount from dbo.QuoteBase
where CreatedOn BETWEEN @SDate AND @EDate) 
union All

(select  count(salesorderid)as TotalOrders,sum(totalamount) as OrderAmount from dbo.SalesOrderBase Where
CreatedOn BETWEEN @SDate AND @EDate)

我得到了结果列

Total Quote    Quote Amount
17                   700
118                 5000

但我想要结果

Total Quote    Quote Amount    Total Orders   Order Amount
  17               700              118           5000

如果有人有想法请与我分享

解决方法

试试这个 –

ALTER PROCEDURE dbo.usp_test

      @SDate DATETIME,@EDate DATETIME

AS BEGIN

    SET NOCOUNT ON;

    SELECT 
          t.TotalQuote,t.QuoteAmount,t2.TotalOrders,t2.OrderAmount
    FROM (SELECT a = 1) a
    CROSS JOIN (
        SELECT 
              TotalQuote = COUNT(quoteid),QuoteAmount = SUM(totalamount)
        FROM dbo.QuoteBase
        WHERE CreatedOn BETWEEN @SDate AND @EDate
    ) t
    CROSS JOIN (
        SELECT 
              TotalOrders = COUNT(salesorderid),OrderAmount = SUM(totalamount) 
        FROM dbo.SalesOrderBase
        WHERE CreatedOn BETWEEN @SDate AND @EDate
    ) t2

END

更新：

SELECT 
      t.TotalQuote,t2.OrderAmount
FROM (
    SELECT 
          TotalQuote = COUNT(quoteid),QuoteAmount = SUM(totalamount)
    FROM dbo.QuoteBase
    WHERE CreatedOn BETWEEN @SDate AND @EDate
) t
FULL OUTER JOIN
(
    SELECT 
          TotalOrders = COUNT(salesorderid),OrderAmount = SUM(totalamount) 
    FROM dbo.SalesOrderBase
    WHERE CreatedOn BETWEEN @SDate AND @EDate
) t2 ON 1 = 1

sql-server – 如何在存储过程中将两个查询联合起来以获得一行结果

解决方法

猜你在找的MsSQL相关文章