表结构:
Id号 收入a 收入b 日期a
01 1000 1000 2005-1
01 2000 2000 2005-2
02 800 800 2005-1
03 900 700 2005-1
03 800 1200 2005-3
04 300 800 2005-2
01 1000 1000 2006-1
01 2000 2000 2006-2
03 900 700 2006-1
03 800 1200 2006-3
04 400 300 2006-1
很多数据想统计 2005-1同期的2006-1的
所有收入a和b的差额大于某个值的所有数据
即得到
条件是2005-1的统计数据
id 0501收入a 0501收入b 0601收入a 0601收入b 收入a差额 收入b差额 日期
01 1000 1000 1000 1000 0 0 2005-1
02 800 800 0 0 -800 -800 2005-1
03 900 700 900 700 0 0 2005-1
04 0 0 400 300 400 300 2005-1
。很多数据
最后再加条件 a差额<>0 数据
Id号 收入a 收入b 日期a
01 1000 1000 2005-1
01 2000 2000 2005-2
02 800 800 2005-1
03 900 700 2005-1
03 800 1200 2005-3
04 300 800 2005-2
01 1000 1000 2006-1
01 2000 2000 2006-2
03 900 700 2006-1
03 800 1200 2006-3
04 400 300 2006-1
很多数据想统计 2005-1同期的2006-1的
所有收入a和b的差额大于某个值的所有数据
即得到
条件是2005-1的统计数据
id 0501收入a 0501收入b 0601收入a 0601收入b 收入a差额 收入b差额 日期
01 1000 1000 1000 1000 0 0 2005-1
02 800 800 0 0 -800 -800 2005-1
03 900 700 900 700 0 0 2005-1
04 0 0 400 300 400 300 2005-1
。很多数据
最后再加条件 a差额<>0 数据
解决方案 »
- |zyciis| 如要求网站的每执行一次后台代码,都会去访问一个本地的Txt文件的话 大小为310K 那会有多大的效率损失
- 如何设计记录数不确定的字段?
- 如何根据参数来指定查询条件??
- 查询计划中 StmtText 什么意思
- SQL2000企业版安装在群集下安装的问题
- win98 数据源 (ODBC)里面没有sql server驱动。怎么办?
- 一个类似与行列转换的问题,清高手指点!!
- 怎样处理关系表中的一对多关系?
- SQL拼写........................................................
- 数据类型的转换?
- 什么是dsn?怎么建立dsn?
- 多用户使用SQL Server的问题!
Create Table TEST
(Id号 Varchar(10),
收入a Int,
收入b Int,
日期a Varchar(10))
--插入數據
Insert TEST Select '01', 1000, 1000, '2005-1'
Union All Select '01', 2000, 2000, '2005-2'
Union All Select '02', 800, 800, '2005-1'
Union All Select '03', 900, 700, '2005-1'
Union All Select '03', 800, 1200, '2005-3'
Union All Select '04', 300, 800, '2005-2'
Union All Select '01', 1000, 1000, '2006-1'
Union All Select '01', 2000, 2000, '2006-2'
Union All Select '03', 900, 700, '2006-1'
Union All Select '03', 800, 1200, '2006-3'
Union All Select '04', 400, 300, '2006-1'
--測試
Select
Id号 As ID,
SUM(Case When Year(日期a+'-1')=2005 And Month(日期a+'-1')=1 Then 收入a Else 0 End) As [0501收入a],
SUM(Case When Year(日期a+'-1')=2005 And Month(日期a+'-1')=1 Then 收入b Else 0 End) As [0501收入b],
SUM(Case When Year(日期a+'-1')=2006 And Month(日期a+'-1')=1 Then 收入a Else 0 End) As [0601收入a],
SUM(Case When Year(日期a+'-1')=2006 And Month(日期a+'-1')=1 Then 收入b Else 0 End) As [0601收入b],
SUM(Case When Year(日期a+'-1')=2005 And Month(日期a+'-1')=1 Then -收入a When Year(日期a+'-1')=2006 And Month(日期a+'-1')=1 Then 收入a Else 0 End) As 收入a差额,
SUM(Case When Year(日期a+'-1')=2005 And Month(日期a+'-1')=1 Then -收入b When Year(日期a+'-1')=2006 And Month(日期a+'-1')=1 Then 收入b Else 0 End) As 收入b差额,
'2005-1' As 日期
from TEST
Group By Id号
--再加条件 a差额<>0 数据
Select * from
(Select
Id号 As ID,
SUM(Case When Year(日期a+'-1')=2005 And Month(日期a+'-1')=1 Then 收入a Else 0 End) As [0501收入a],
SUM(Case When Year(日期a+'-1')=2005 And Month(日期a+'-1')=1 Then 收入b Else 0 End) As [0501收入b],
SUM(Case When Year(日期a+'-1')=2006 And Month(日期a+'-1')=1 Then 收入a Else 0 End) As [0601收入a],
SUM(Case When Year(日期a+'-1')=2006 And Month(日期a+'-1')=1 Then 收入b Else 0 End) As [0601收入b],
SUM(Case When Year(日期a+'-1')=2005 And Month(日期a+'-1')=1 Then -收入a When Year(日期a+'-1')=2006 And Month(日期a+'-1')=1 Then 收入a Else 0 End) As 收入a差额,
SUM(Case When Year(日期a+'-1')=2005 And Month(日期a+'-1')=1 Then -收入b When Year(日期a+'-1')=2006 And Month(日期a+'-1')=1 Then 收入b Else 0 End) As 收入b差额,
'2005-1' As 日期
from TEST
Group By Id号) A
Where 收入a差额<>0
--刪除測試環境
Drop Table TEST
--結果
/*
ID 0501收入a 0501收入b 0601收入a 0601收入b 收入a差额 收入b差额 日期
01 1000 1000 1000 1000 0 0 2005-1
02 800 800 0 0 -800 -800 2005-1
03 900 700 900 700 0 0 2005-1
04 0 0 400 300 400 300 2005-1--再加条件 a差额<>0 数据
ID 0501收入a 0501收入b 0601收入a 0601收入b 收入a差额 收入b差额 日期
02 800 800 0 0 -800 -800 2005-1
04 0 0 400 300 400 300 2005-1
*/
insert into @t
select '01', 1000, 1000, '2005-01' union
select '01', 2000, 2000, '2005-02' union
select '02', 800 , 800 , '2005-01' union
select '03', 900 , 700 , '2005-01' union
select '03', 800 , 1200, '2005-03' union
select '04', 300 , 800 , '2005-02' union
select '01', 1000, 1000, '2006-01' union
select '01', 2000, 2000, '2006-02' union
select '03', 900, 700 , '2006-01' union
select '03', 800, 1200, '2006-03' union
select '04', 400, 300 , '2006-01'
select id ,
sum(case when substring(date_a ,1,4) ='2005' then income_A else 0 end) as '0501收入a' ,
sum(case when substring(date_a ,1,4) ='2005' then income_B else 0 end) as '0501收入b' ,
sum(case when substring(date_a ,1,4) ='2006' then income_A else 0 end) as '0601收入a' ,
sum(case when substring(date_a ,1,4) ='2006' then income_B else 0 end) as '0601收入b'
from @t
where date_a in( '2005-01' , '2006-01')
group by substring(date_a,6,2),
idselect *,
[0601收入a] - [0501收入a] as 收入a差额,
[0601收入b] - [0501收入b] as 收入b差额
from
(
select id ,
sum(case when substring(date_a ,1,4) ='2005' then income_A else 0 end) as '0501收入a' ,
sum(case when substring(date_a ,1,4) ='2005' then income_B else 0 end) as '0501收入b' ,
sum(case when substring(date_a ,1,4) ='2006' then income_A else 0 end) as '0601收入a' ,
sum(case when substring(date_a ,1,4) ='2006' then income_B else 0 end) as '0601收入b'
from @t
where date_a in( '2005-01' , '2006-01')
group by substring(date_a,6,2),
id
)A
where [0601收入a] - [0501收入a] <> 0