如何将单个查询结果逐个插入一张表？

[fongkey]

各位大师，我要生成一个4列三行的结果表。如下。其中的数值都要从底下的查询里单独得出不能整行求出。请教如何逐个值把查询结果填入结果表。谢谢。


时间段 退回数 发货数 退回率
前3个月 6 100 6%
前8到9个月 7 140 5%
24个月前 20 200 10%

select count(distinct 序列号）--这个查出来是6
from Return
where time between 11月 and getdate() --这里不同

select count(distinct 序列号) --这个查出来是100
from Shipment
where time between 12月 and getdate()


select count(distinct 序列号）--这个查出来是7
from Return
where time between 前9 and 前8

select count(distinct 序列号) --这个查出来是140
from Shipment
where time between 前9 and 前8

[szm341]

;with cte as(
select (
select count(distinct 序列号) from Return
where time between 11月 and getdate()
) 退回数,
(select count(distinct 序列号) from Shipment
where time between 12月 and getdate()
) 发货数
union all
select (
select count(distinct 序列号）from Return
where time between 前9 and 前8
),
(select count(distinct 序列号) from Shipment
where time between 前9 and 前8
)
union all...
)
select *,cast(退回数*100.0/发货数 as varchar(20))+'%' from cte

[zhazhuzhao]

自己按照结构建立一个表变量，然后插入数据初始化，最后慢慢更新。

[fongkey]

请大师评点我的做法，这里只填第一行，我一直担心时间变量会在括号里失效，运行了一下好像不会。

create table XxxxResults
	(Indicators varchar(30),
	Returns int,
	Shipments int,
	Results float
	)

declare @Start datetime='2012-12-31'--这个时间参数
		,
		@ERIStart datetime
		,
		@ERIEnd datetime
set @ERIStart=cast(DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@Start)-6,0)) as datetime);
--select @ERIStart
set	@ERIEnd=cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Start)+1,0)) as datetime);
--select @ERIEnd
insert into XxxxResults
	(Indicators,Returns)
SELECT 'ERI',count(distinct t1.SerialNumber) AS ERICounts
FROM XxxxTblServiceReturns t1
	left join
	TblShipments t2
ON 	t1.SerialNumber=t2.SerialNumber
WHERE
	t2.TransactionDate between @ERIStart and @ERIEnd
--select * from XxxxResults	


--接着查询TblShipments在目标区间内出了多少货。
declare @Start datetime='2012-12-31'--这个时间参数
		,
		@ERIShipStart datetime
		,
		@ERIShipEnd datetime
set @ERIShipStart=cast(DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@Start)-6,0)) as datetime);
--select @ERIShipStart
set	@ERIShipEnd=cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Start),0)) as datetime);
--select @ERIShipEnd
update XxxxResults
set Shipments=
(SELECT 
	count(distinct t1.SerialNumber)
FROM TblShipments t1
	JOIN 
	XxxxTblMainFrames t2
ON 
	t1.PartNumber = t2.PartNumber
WHERE 
	t1.PartNumber IN 
		(SELECT 
			PartNumber 
		 FROM 
			 XxxxTblMainFrames) 
	 AND 
		 TransactionDate BETWEEN @ERIShipStart AND @ERIShipEnd
	 AND 
		 OrderType NOT LIKE '%RMA%' 
	 AND TransactionType != 'RMA' 
	 AND t1.ProductLine != 'UPGRADE')
where Indicators='ERI'