我用的是ADOQuery,请指点下如何才能将下面两个查询左连接到一起,通过declare @sql varchar(8000) select 处理后如何再插入到一个表中呢
第一个查询
if OBJECT_ID('temp2')is not null
drop table temp2
goselect * into temp2 from
(SELECT jiagongdanhao, yppinming, SUM(实际面积) AS 实际, SUM(加工面积)
AS 加工面积
FROM (SELECT jiagongdanhao, yppinming = CASE WHEN zuoyouwubian = '-' OR
zuoyouwubian = '' THEN '普通' ELSE '装饰' END,
实际面积 = SUM(cast(jiagonggao * jiagongkuan / 1000000.00 AS decimal(18,
2))) * shuliang,
加工面积 = SUM(CASE WHEN jiagonggao * jiagongkuan < 1000000 AND
jiagonggao * jiagongkuan > 0 THEN 1.00 ELSE cast(jiagonggao * jiagongkuan / 1000000.00
AS decimal(18, 2)) END) * shuliang
FROM y_hetong_jiagongdanliushui
GROUP BY jiagongdanhao, yppinming, zuoyouwubian, shuliang, RIGHT(yppinming,
2), CASE WHEN zuoyouwubian = '-' OR
zuoyouwubian = '' THEN '普通' ELSE '装饰' END) a
GROUP BY jiagongdanhao, yppinming) a
go
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when yppinming='''+yppinming+''' then 实际面积 else 0 end) as ['+yppinming+'实际面积],'
+'sum(case when yppinming='''+yppinming+''' then 加工面积 else 0 end) as ['+yppinming+'加工面积]'
from
(select distinct yppinming from temp2) t
exec ('select jiagongdanhao,'+@sql+' from temp2 group by jiagongdanhao') 结果1
---------------------------------------------------------------------------
jiagongdanhao 普通实际面积 普通加工面积 装饰实际面积 装饰加工面积
02001 13.7 13.7 7.05 7.05
02008 9 10 0 0
02010 0 0 8.8 8.8 第二个查询
if OBJECT_ID('temp1')is not null
drop table temp1
select * into temp1 from
(SELECT LEFT(a.bh, 6) AS bh, d .leibie, a.ypzuixiaodanwei, b.ypid, b.shuliang,
b.caozuoriqi, c.jiagongdanhao, c.chejianming
FROM Y_baseinfo a INNER JOIN
Y_liushui b ON a.idno = b.ypid INNER JOIN
Y_hetong_jiagongdan c ON b.hetongbianhao = c.jiagongdanhao INNER JOIN
Y_leibie d ON LEFT(a.bh, 6) = d .bh
WHERE (b.caozuobiaoshi <> '材料入库') AND (LEFT(a.bh, 4) = '0102') AND
(LEN(a.bh) >= 4)) a
go
declare @str varchar(4000)
set @str=''
select @str=@str+','+leibie+
'=sum(case when leibie='+QUOTENAME(leibie,'''')+' then shuliang else 0 end)'
from temp1
group by leibie
print @str
set @str='select jiagongdanhao '+@str+' from temp1 group by jiagongdanhao'
print @str
exec(@str)
--------------------------------
jiagongdanhao 棉花 纤维
02001 11 15
02008 20 10
左连接后的表为
--------------------------------------------------------------------------------------------
jiagongdanhao 普通实际面积 普通加工面积 装饰实际面积 装饰加工面积 棉花 纤维
02001 13.7 13.7 7.05 7.05 11 15
02008 9 10 0 0 20 10
02010 0 0 8.8 8.8 0 0
第一个查询
if OBJECT_ID('temp2')is not null
drop table temp2
goselect * into temp2 from
(SELECT jiagongdanhao, yppinming, SUM(实际面积) AS 实际, SUM(加工面积)
AS 加工面积
FROM (SELECT jiagongdanhao, yppinming = CASE WHEN zuoyouwubian = '-' OR
zuoyouwubian = '' THEN '普通' ELSE '装饰' END,
实际面积 = SUM(cast(jiagonggao * jiagongkuan / 1000000.00 AS decimal(18,
2))) * shuliang,
加工面积 = SUM(CASE WHEN jiagonggao * jiagongkuan < 1000000 AND
jiagonggao * jiagongkuan > 0 THEN 1.00 ELSE cast(jiagonggao * jiagongkuan / 1000000.00
AS decimal(18, 2)) END) * shuliang
FROM y_hetong_jiagongdanliushui
GROUP BY jiagongdanhao, yppinming, zuoyouwubian, shuliang, RIGHT(yppinming,
2), CASE WHEN zuoyouwubian = '-' OR
zuoyouwubian = '' THEN '普通' ELSE '装饰' END) a
GROUP BY jiagongdanhao, yppinming) a
go
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when yppinming='''+yppinming+''' then 实际面积 else 0 end) as ['+yppinming+'实际面积],'
+'sum(case when yppinming='''+yppinming+''' then 加工面积 else 0 end) as ['+yppinming+'加工面积]'
from
(select distinct yppinming from temp2) t
exec ('select jiagongdanhao,'+@sql+' from temp2 group by jiagongdanhao') 结果1
---------------------------------------------------------------------------
jiagongdanhao 普通实际面积 普通加工面积 装饰实际面积 装饰加工面积
02001 13.7 13.7 7.05 7.05
02008 9 10 0 0
02010 0 0 8.8 8.8 第二个查询
if OBJECT_ID('temp1')is not null
drop table temp1
select * into temp1 from
(SELECT LEFT(a.bh, 6) AS bh, d .leibie, a.ypzuixiaodanwei, b.ypid, b.shuliang,
b.caozuoriqi, c.jiagongdanhao, c.chejianming
FROM Y_baseinfo a INNER JOIN
Y_liushui b ON a.idno = b.ypid INNER JOIN
Y_hetong_jiagongdan c ON b.hetongbianhao = c.jiagongdanhao INNER JOIN
Y_leibie d ON LEFT(a.bh, 6) = d .bh
WHERE (b.caozuobiaoshi <> '材料入库') AND (LEFT(a.bh, 4) = '0102') AND
(LEN(a.bh) >= 4)) a
go
declare @str varchar(4000)
set @str=''
select @str=@str+','+leibie+
'=sum(case when leibie='+QUOTENAME(leibie,'''')+' then shuliang else 0 end)'
from temp1
group by leibie
print @str
set @str='select jiagongdanhao '+@str+' from temp1 group by jiagongdanhao'
print @str
exec(@str)
--------------------------------
jiagongdanhao 棉花 纤维
02001 11 15
02008 20 10
左连接后的表为
--------------------------------------------------------------------------------------------
jiagongdanhao 普通实际面积 普通加工面积 装饰实际面积 装饰加工面积 棉花 纤维
02001 13.7 13.7 7.05 7.05 11 15
02008 9 10 0 0 20 10
02010 0 0 8.8 8.8 0 0
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货