我有一个存储过程,我加入了两个查询.我的疑问是
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
