问题描述:表a1和表a2的表结构相同。说明:一个单位有多种税,同一单位在表a1中出现的tax不会在a2出现(例:表a1中单位1的税1和税2在表a2的单位1里不会出现)
表名:a1
ID Unit tax price date
1 单位1 税1 2 2009-3-1
2 单位1 税2 4 2009-3-1
3 单位2 税2 15 2009-3-1
4 单位2 税3 15 2009-3-1
5 单位3 税1 22 2009-3-1
6 单位1 税1 21 2008-3-1
7 单位2 税3 13 2008-3-1 表名:a2
ID Unit tax price date
1 单位1 税3 5 2009-3-1
2 单位1 税4 6 2009-3-1
3 单位2 税1 7 2009-3-1
4 单位3 税3 13 2009-3-1
5 单位4 税5 18 2009-3-1
6 单位2 税3 14 2008-3-1
7 单位4 税5 16 2008-3-1表名:a3
ID Unit
1 单位1
2 单位2表名:a4
ID Unit class
1 单位1 城南
2 单位2 城南
3 单位3 城北
4 单位4 城北问题1:得到表a3中的单位统计表:统计同期的对比表(考虑税列可增加)
ID Unit 税1[08-03] 税1[09-03] 税1[同比%] 税2[08-03] 税2[09-03] 税2[同比%]
1 单位1 21 2 2/21后的值 0 4 空
2 单位2 0 7 空 0 15 空问题2:如果得到表a1、a2中所有单位的统计表,SQL怎么写?问题3:统计表a4中所有城南的统计表:结构和问题1相同
ID Unit 税1[08-03] 税1[09-03] 税1[同比%] 税2[08-03] 税2[09-03] 税2[同比%]
1 城南 。
1 城北 。
表名:a1
ID Unit tax price date
1 单位1 税1 2 2009-3-1
2 单位1 税2 4 2009-3-1
3 单位2 税2 15 2009-3-1
4 单位2 税3 15 2009-3-1
5 单位3 税1 22 2009-3-1
6 单位1 税1 21 2008-3-1
7 单位2 税3 13 2008-3-1 表名:a2
ID Unit tax price date
1 单位1 税3 5 2009-3-1
2 单位1 税4 6 2009-3-1
3 单位2 税1 7 2009-3-1
4 单位3 税3 13 2009-3-1
5 单位4 税5 18 2009-3-1
6 单位2 税3 14 2008-3-1
7 单位4 税5 16 2008-3-1表名:a3
ID Unit
1 单位1
2 单位2表名:a4
ID Unit class
1 单位1 城南
2 单位2 城南
3 单位3 城北
4 单位4 城北问题1:得到表a3中的单位统计表:统计同期的对比表(考虑税列可增加)
ID Unit 税1[08-03] 税1[09-03] 税1[同比%] 税2[08-03] 税2[09-03] 税2[同比%]
1 单位1 21 2 2/21后的值 0 4 空
2 单位2 0 7 空 0 15 空问题2:如果得到表a1、a2中所有单位的统计表,SQL怎么写?问题3:统计表a4中所有城南的统计表:结构和问题1相同
ID Unit 税1[08-03] 税1[09-03] 税1[同比%] 税2[08-03] 税2[09-03] 税2[同比%]
1 城南 。
1 城北 。
解决方案 »
- 关于数据类型转换。
- MSSQL高手进来!!!!!!!!!!!!
- 如何在SQL SERVER 的函数中抛出错误?注意是在函数中
- 数据查询 行转列 问题求助
- 问一个很菜的问题
- Sql Server CLR函数 可以访问本地文件,却不能访问web共享文件.
- 得到指定结点的所有子结点怎么办?
- 如何解决此错误:7405 16 异类查询要求为连接设置 ANSI_NULLS 和 ANSI_WARNINGS 选项。这将确保一致的查询语义。请启用这些选项,然后重新
- 紧急求助....在这种情况下,几个程序对一个表进行操作,会不会出现冲突。
- sqlserver如何在一张表中引用另一张表与表中字段相同数据所对应的值呢?
- 关于DATEADD的问题
- sqlvserver2000个人版能装在vista下吗?没装前先问问,小走弯路。
create table a1
(id int,
unit varchar(10),
tax varchar(10),
price float,
date datetime)
create table a2
(id int,
unit varchar(10),
tax varchar(10),
price float,
date datetime)insert into a1
select 1,'单位1','税1',2 ,'2009-3-1' union all
select 2, '单位1', '税2 ', 4 , '2009-3-1' union all
select 3, '单位2', '税2' , 15 , '2009-3-1' union all
select 4, '单位2' , '税3' , 15, '2009-3-1' union all
select 5, '单位3' , '税1' , 22 , '2009-3-1' union all
select 6,'单位1' ,'税1',21,'2008-3-1' union all
select 7, '单位2' , '税3' , 13 , '2008-3-1'
insert into a2
select 1,'单位1','税3',5 ,'2009-3-1' union all
select 2, '单位1', '税4 ', 6 , '2009-3-1' union all
select 3, '单位2', '税1' , 7, '2009-3-1' union all
select 4, '单位3' , '税3' , 13, '2009-3-1' union all
select 5, '单位4' , '税5' , 18 , '2009-3-1' union all
select 6,'单位2' ,'税3',14,'2008-3-1' union all
select 7, '单位4' , '税5' , 16 , '2008-3-1' create table a3
(id int,
unit varchar(10))
create table a4
(id int,
unit varchar(10),
class varchar(10))insert into a3
select 1,'单位1' union
select 2,'单位2' insert into a4
select 1,'单位1','城南' union
select 2,'单位2','城南' union
select 3,'单位3','城北' union
select 4,'单位4','城北'1 select a.id,a.unit,a.a as'税1[08-03]',a.b as'税1[09-03]',
(case when(a.a=0) then null else a.a/a.b end) as '税1[同比%]',
a.c as'税2[08-03]',a.d as'税2[09-03]',
(case when(a.c=0) then null else a.c/a.d end) as '税2[同比%]'
from
(select b.*,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税1') then price else 0 end ) as a,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税1') then price else 0 end ) as b,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税2') then price else 0 end ) as c,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税2') then price else 0 end ) as d
from
(select * from a1
union
select * from a2) a, a3 b
where a.unit=b.unit
group by b.id,b.unit) a
---------------------------------------------------------
1 单位1 21 2 10.5 0 4 NULL
2 单位2 0 7 NULL 0 15 NULL
2
select a.id,a.unit,a.a as'税1[08-03]',a.b as'税1[09-03]',
(case when(a.a=0) then null else a.a/a.b end) as '税1[同比%]',
a.c as'税2[08-03]',a.d as'税2[09-03]',
(case when(a.c=0) then null else a.c/a.d end) as '税2[同比%]'
from
(select b.id,b.unit,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税1') then price else 0 end ) as a,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税1') then price else 0 end ) as b,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税2') then price else 0 end ) as c,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税2') then price else 0 end ) as d
from
(select * from a1
union
select * from a2) a, a4 b
where a.unit=b.unit
group by b.id,b.unit) a
--------------------------------------------------
1 单位1 21 2 10.5 0 4 NULL
2 单位2 0 7 NULL 0 15 NULL
3 单位3 0 22 NULL 0 0 NULL
4 单位4 0 0 NULL 0 0 NULL3select a.id,a.class,a.a as'税1[08-03]',a.b as'税1[09-03]',
(case when(a.a=0) then null else a.a/a.b end) as '税1[同比%]',
a.c as'税2[08-03]',a.d as'税2[09-03]',
(case when(a.c=0) then null else a.c/a.d end) as '税2[同比%]'
from
(select b.id,b.class,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税1') then price else 0 end ) as a,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税1') then price else 0 end ) as b,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税2') then price else 0 end ) as c,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税2') then price else 0 end ) as d
from
(select * from a1
union
select * from a2) a, a4 b
where a.unit=b.unit and b.class='城南'
group by b.id,b.class) a
---------------------------------------------------------
1 城南 21 2 10.5 0 4 NULL
2 城南 0 7 NULL 0 15 NULL
select a.id,a.unit,a.a as'税1[08-03]',a.b as'税1[09-03]',
(case when(a.a=0) then null else a.a/a.b end) as '税1[同比%]',
a.c as'税2[08-03]',a.d as'税2[09-03]',
(case when(a.c=0) then null else a.c/a.d end) as '税2[同比%]'
from
(select b.id,b.unit,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税1') then price else 0 end ) as a,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税1') then price else 0 end ) as b,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税2') then price else 0 end ) as c,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税2') then price else 0 end ) as d
from
(select * from a1
union
select * from a2) a, a4 b
where a.unit=b.unit
group by b.id,b.unit) a
--------------------------------------------------
1 单位1 21 2 10.5 0 4 NULL
2 单位2 0 7 NULL 0 15 NULL
3 单位3 0 22 NULL 0 0 NULL
4 单位4 0 0 NULL 0 0 NULL3select a.id,a.class,a.a as'税1[08-03]',a.b as'税1[09-03]',
(case when(a.a=0) then null else a.a/a.b end) as '税1[同比%]',
a.c as'税2[08-03]',a.d as'税2[09-03]',
(case when(a.c=0) then null else a.c/a.d end) as '税2[同比%]'
from
(select b.id,b.class,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税1') then price else 0 end ) as a,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税1') then price else 0 end ) as b,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0803' and a.tax='税2') then price else 0 end ) as c,
sum(case when (substring(convert(char(6),a.date,112),3,4)='0903' and a.tax='税2') then price else 0 end ) as d
from
(select * from a1
union
select * from a2) a, a4 b
where a.unit=b.unit and b.class='城南'
group by b.id,b.class) a
---------------------------------------------------------
1 城南 21 2 10.5 0 4 NULL
2 城南 0 7 NULL 0 15 NULL