有这样一个SQL查询生成的结果:
单位 本期值 上期值
A公司 100 200
B公司 150 100
C公司 180 80
如何生成下面这张查询的内容
单位 本期值 上期值
A公司 100 200
B公司 150 100
C公司 180 80
合计 330 380我的第一个查询结果SQL语句这样写的,
select E.[NAME] ,(A.x)*10000/(bqxl.factvalue*0.2) AS BQZ ,(B.y)*10000/(sqxl.factvalue*0.2) AS SQZ
FROM ExpandAreaEx(9003,0) AS E
left join (select sum(factvalue)as x,corpid from factvalue where idate='2008-6-1' and typeid='S'
and (selfid='1503' OR selfid='1507' OR selfid='1508') group by corpid ) as A on A.corpid=E.self
left join (select sum(factvalue)as y,corpid from factvalue where idate='2008-6-1' and typeid='T'
and (selfid='1503' OR selfid='1507' OR selfid='1508') group by corpid ) as B on B.corpid=E.self
left join factvalue as bqxl on bqxl.corpid=E.self and bqxl.idate='2008-6-1' and bqxl.typeid='S' and bqxl.selfid='1530'
left join factvalue as sqxl on sqxl.corpid=E.self and sqxl.idate='2008-6-1' and sqxl.typeid='T' and sqxl.selfid='1530'
where E.self<>'9003'
group by E.[NAME] ,A.x,bqxl.factvalue,B.y,sqxl.factvalue
order by bqz其中ExpandAreaEx(9003,0)函数是我自己定义的,取9003下面所有单位的名称,ID等
单位 本期值 上期值
A公司 100 200
B公司 150 100
C公司 180 80
如何生成下面这张查询的内容
单位 本期值 上期值
A公司 100 200
B公司 150 100
C公司 180 80
合计 330 380我的第一个查询结果SQL语句这样写的,
select E.[NAME] ,(A.x)*10000/(bqxl.factvalue*0.2) AS BQZ ,(B.y)*10000/(sqxl.factvalue*0.2) AS SQZ
FROM ExpandAreaEx(9003,0) AS E
left join (select sum(factvalue)as x,corpid from factvalue where idate='2008-6-1' and typeid='S'
and (selfid='1503' OR selfid='1507' OR selfid='1508') group by corpid ) as A on A.corpid=E.self
left join (select sum(factvalue)as y,corpid from factvalue where idate='2008-6-1' and typeid='T'
and (selfid='1503' OR selfid='1507' OR selfid='1508') group by corpid ) as B on B.corpid=E.self
left join factvalue as bqxl on bqxl.corpid=E.self and bqxl.idate='2008-6-1' and bqxl.typeid='S' and bqxl.selfid='1530'
left join factvalue as sqxl on sqxl.corpid=E.self and sqxl.idate='2008-6-1' and sqxl.typeid='T' and sqxl.selfid='1530'
where E.self<>'9003'
group by E.[NAME] ,A.x,bqxl.factvalue,B.y,sqxl.factvalue
order by bqz其中ExpandAreaEx(9003,0)函数是我自己定义的,取9003下面所有单位的名称,ID等
CompanyName nvarchar(10),
ThisValue int,
LastValue int
)
INSERT INTO testinfo(CompanyName,ThisValue,LastValue)
SELECT 'A公司',100,200 UNION ALL
SELECT 'B公司',150,100 UNION ALL
SELECT 'C公司',180,80SELECT * FROM testinfo
UNION
SELECT '合计',SUM(ThisValue),SUM(LastValue) FROM testinfo CompanyName ThisValue LastValue
----------- ----------- -----------
A公司 100 200
B公司 150 100
C公司 180 80
合计 860 760(4 row(s) affected)
SELECT .... INTO #TB FROM ......
SELECT * FROM #TB
UNION ALL
SELECT 单位='合计',本期值=sum(本期值),上期值=sum(上期值) FROM #TB
DROP TABLE #TB
declare @charge table (company_ID char(4),
company_Name varchar(10),
charge1 numeric(15,2),
charge2 numeric(15,2),
charge3 numeric(15,2))
insert @charge select '0001','外國公司A', 1000.00,500.00,600.00
insert @charge select '0002','外國公司B', 2000.00,400.00,400.00
insert @charge select '0003','國內公司A', 500.00,300.00,300.00
insert @charge select '0004','國內公司B', 500.00,700.00,400.00
insert @charge select '0005','其他公司A', 200.00,200.00,200.00
insert @charge select '0006','其他公司B', 200.00,200.00,200.00
declare @company table(company_Type int, Type_Name varchar(10), company_ID char(4), company_Name varchar(10))
insert @company select 1,'國外公司', '0001', '外國公司A'
insert @company select 1,'國外公司', '0002', '外國公司B'
insert @company select 2,'國內公司', '0003', '國內公司A'
insert @company select 2,'國內公司', '0004', '國內公司B'
insert @company select 3,'其他公司', '0005', '其他公司A'
insert @company select 3,'其他公司', '0006', '其他公司B'
select
[company_Type]=
case when grouping(b.company_ID)=1 and grouping(b.Type_Name)=1 then '全部公司'
when grouping(b.company_ID)=1 then b.Type_Name else b.company_ID end,
[company_Name]=
case when grouping(b.company_ID)=1 and grouping(b.Type_Name)=1 then '總合計'
when grouping(b.company_ID)=1 then '小計' else
(select top 1 company_Name from @company where company_ID=b.company_ID)
end,
charge1=sum(charge1),charge2=sum(charge2),charge3=sum(charge3)
from @charge a join @company b
on a.company_ID=b.company_ID
group by b.Type_Name,b.company_ID with rollup
CompanyName nvarchar(10),
ThisValue int,
LastValue int
)
INSERT INTO testinfo(CompanyName,ThisValue,LastValue)
SELECT 'A公司',100,200 UNION ALL
SELECT 'B公司',150,100 UNION ALL
SELECT 'C公司',180,80 SELECT CASE WHEN GROUPING(CompanyName)=1 THEN '合計' ELSE CompanyName END CompanyName,
SUM(ThisValue) AS ThisValue,
SUM(LastValue) AS LastValue
FROM testinfo
GROUP BY CompanyName
WITH ROLLUPDROP TABLE testInfo