A
Date Debit Credit Balance Condition
2008-01-20 10 20 10 Y
2009-01-20 10 20 10 Y
2009-01-21 20 25 5 Y
2009-02-01 35 30 -5 Y
2009-02-09 10 35 25 Y
2009-02-15 10 40 30 N
2009-03-20 5 40 35 Y
2009-06-12 50 45 -5 Y
2009-06-18 5 50 45 Y
如何得到如下的结果:
year=2009 conditon=Y sum sum sum
Year Month Debit Credit Balance
2009 1 30 45 15
2009 2 45 65 20
2009 3 5 40 35
2009 4 0 0 0
2009 5 0 0 0
2009 6 55 95 40
2009 7 0 0 0
2009 8 0 0 0
2009 9 0 0 0
2009 10 0 0 0
2009 11 0 0 0
2009 12 0 0 0
Date Debit Credit Balance Condition
2008-01-20 10 20 10 Y
2009-01-20 10 20 10 Y
2009-01-21 20 25 5 Y
2009-02-01 35 30 -5 Y
2009-02-09 10 35 25 Y
2009-02-15 10 40 30 N
2009-03-20 5 40 35 Y
2009-06-12 50 45 -5 Y
2009-06-18 5 50 45 Y
如何得到如下的结果:
year=2009 conditon=Y sum sum sum
Year Month Debit Credit Balance
2009 1 30 45 15
2009 2 45 65 20
2009 3 5 40 35
2009 4 0 0 0
2009 5 0 0 0
2009 6 55 95 40
2009 7 0 0 0
2009 8 0 0 0
2009 9 0 0 0
2009 10 0 0 0
2009 11 0 0 0
2009 12 0 0 0
不是很理解啊,能详细点吗?谢谢A
Date Debit Credit Balance Condition
2008-01-20 10 20 10 Y
2009-01-20 10 20 10 Y
2009-01-21 20 25 5 Y
2009-02-01 35 30 -5 Y
2009-02-09 10 35 25 Y
2009-02-15 10 40 30 N
2009-03-20 5 40 35 Y
2009-06-12 50 45 -5 Y
2009-06-18 5 50 45 Y
如何得到如下的结果:
year=2009 conditon=Y sum sum sum
Year Month Debit Credit Balance
2009 1 30 45 15
2009 2 45 65 20
2009 3 5 40 35
2009 4 0 0 0
2009 5 0 0 0
2009 6 55 95 40
2009 7 0 0 0
2009 8 0 0 0
2009 9 0 0 0
2009 10 0 0 0
2009 11 0 0 0
2009 12 0 0 0
set @t='2009-01-01'
select datepart(yy,[Date]),[month]=cast(right(convert(varchar(7),dif,120),2) as int),sum(t.[Debit]),sum(t.[Credit]),sum(t.[Balance])
from
(
select dif=dateadd(month,number,@t) from master..spt_values where type='p'
) m,tb t
where dif<'2009-12-31' and [Condition]='Y' and datepart(yy,[Date])='2009'
group by
datepart(yy,[Date]),cast(right(convert(varchar(7),dif,120),2) as int)
order by 2
学习 sql77的
if object_ID('A') is not null
drop table a
go
create table A([Date] Datetime,Debit int,Credit int,Balance int,Condition nvarchar(2))
go
insert into A
Select '2008-01-20',10,20,10,'Y' union all
Select '2009-01-20',10,20,10,'Y' union all
Select '2009-01-21',20,25,5,'Y' union all
Select '2009-02-01',35,30,-5,'Y' union all
Select '2009-02-09',10,35,25,'Y' union all
Select '2009-02-15',10,40,30,'N' union all
Select '2009-03-20',5,40,35,'Y' union all
Select '2009-06-12',50,45,-5,'Y' union all
Select '2009-06-18',5,50,45,'Y'
goSelect '2009' year,B.NUMBER Month,IsNull(A.Debit,0) Debit,IsNull(A.Credit,0) Credit,IsNull(A.Balance,0) Balance from
(
SELECT Distinct NUMBER FROM MASTER..SPT_VALUES WHERE NUMBER BETWEEN 1 AND 12
)
B left join
(
Select year(Date) as year,Month(Date) as Month,Sum(Debit) as Debit,Sum(Credit) as Credit,Sum(Balance) as Balance
from A where year(Date)='2009'and Condition='Y'
Group by year(Date),Month(Date)
) A on B.NUMBER = A.Month
if object_id('tb')is not null drop table tb
go
CREATE TABLE tb(Date datetime,Debit int, Credit int, Balance int, Condition varchar(2 ))
INSERT tb SELECT
'2008-01-20', 10 ,20, 10, 'N' UNION ALL SELECT ---前2条记录应该有一个为N,才和结果一致
'2009-01-20', 10 ,20, 10, 'Y' UNION ALL SELECT
'2009-01-21', 20, 25, 5 ,'Y' UNION ALL SELECT
'2009-02-01', 35, 30, -5, 'Y' UNION ALL SELECT
'2009-02-09', 10, 35, 25, 'Y' UNION ALL SELECT
'2009-02-15', 10, 40, 30, 'N' UNION ALL SELECT
'2009-03-20', 5 ,40 ,35 ,'Y' UNION ALL SELECT
'2009-06-12', 50, 45, -5, 'Y' UNION ALL SELECT
'2009-06-18', 5, 50 ,45 ,'Y'
declare @year int
set @year=2009--Year Month Debit Credit Balance
--2009 1 30 45 15
select
year(s.date) as [year],
month(s.date) as [month],
isnull(sum(Debit),0) as Debit,
isnull(sum(Credit),0) as Credit,
isnull(sum(Balance),0) as Balance
from
(select cast(ltrim(@year)+'-'+ltrim(number)+'-01' as datetime) as date
from master..spt_values
where type='p'
and number between 1 and 12
)s
left join
tb t
on datediff(mm,s.date,t.date)=0 and year(t.date)=@year and Condition='Y'group by
year(s.date),month(s.date)
year month Debit Credit Balance
----------- ----------- ----------- ----------- -----------
2009 1 30 45 15
2009 2 45 65 20
2009 3 5 40 35
2009 4 0 0 0
2009 5 0 0 0
2009 6 55 95 40
2009 7 0 0 0
2009 8 0 0 0
2009 9 0 0 0
2009 10 0 0 0
2009 11 0 0 0
2009 12 0 0 0
警告: 聚合或其他 SET 操作消除了空值。(12 行受影响)