数据表大致如下:
ID 应支付额 应支付时间 实际支付额 实际支付时间
1 2000.00 2012-5-1 1000.00 2012-5-3
2 1000.00 2012-6-1 1000.00 2012-6-1
3 3000.00 2012-7-1 3000.00 2012-7-1现在需要SQL语句查询如下结果(只能用一条)查询某月某日为止需要支付的金额,以7月2日为例,则需要查询出
:
(A)1月 应支付额-实际支付额
(B)2月 应支付额-实际支付额
(C)3月 应支付额A+B+C 的金额需要用一条语句查询出。挠破头皮也没构思出来。大神们指教。
ID 应支付额 应支付时间 实际支付额 实际支付时间
1 2000.00 2012-5-1 1000.00 2012-5-3
2 1000.00 2012-6-1 1000.00 2012-6-1
3 3000.00 2012-7-1 3000.00 2012-7-1现在需要SQL语句查询如下结果(只能用一条)查询某月某日为止需要支付的金额,以7月2日为例,则需要查询出
:
(A)1月 应支付额-实际支付额
(B)2月 应支付额-实际支付额
(C)3月 应支付额A+B+C 的金额需要用一条语句查询出。挠破头皮也没构思出来。大神们指教。
chargeamount money, chargedate datetime,
payment money, paydate datetime)insert into @tb values (1, 2000.00, '2012-5-1', 1000.00,'2012-5-3')
insert into @tb values (2, 1000.00, '2012-6-1', 1000.00, '2012-6-1')
insert into @tb values (3, 3000.00, '2012-7-1', 3000.00, '2012-7-1')
/*
ID 应支付额 应支付时间 实际支付额 实际支付时间
*/
select a.mn, sum(chargeamount) as chargeamount, sum(payment) as payment,
isnull(sum(chargeamount),0) - isnull(sum(payment) ,0) as rest
from (
select dateadd(month, datediff(month, 0, chargedate),0) as mn
from @tb
union
select dateadd(month, datediff(month, 0, paydate),0) as mn
from @tb
) as a
left join (
select dateadd(month, datediff(month, 0, chargedate),0) as mn, sum(chargeamount) as chargeamount
from @tb
group by dateadd(month, datediff(month, 0, chargedate),0)
) as b on b.mn = a.mn
left join (
select dateadd(month, datediff(month, 0, paydate),0) as mn, sum(payment) as payment
from @tb
group by dateadd(month, datediff(month, 0, paydate),0)
) as c on c.mn = a.mn
group by a.mn
with cube
-- mn,chargeamount,payment,rest
-- 2012-05-01 00:00:00.000,2000.0000,1000.0000,1000.0000
-- 2012-06-01 00:00:00.000,1000.0000,1000.0000,.0000
-- 2012-07-01 00:00:00.000,3000.0000,3000.0000,.0000
-- ,6000.0000,5000.0000,1000.0000
--
-- (所影响的行数为 4 行)
--
chargeamount money, chargedate datetime,
payment money, paydate datetime)insert into @tb values (1, 2000.00, '2012-5-1', 1000.00,'2012-5-3')
insert into @tb values (2, 1000.00, '2012-6-1', 1000.00, '2012-6-1')
insert into @tb values (3, 3000.00, '2012-7-1', 3000.00, '2012-7-1')
insert into @tb values (11, 2000.00, '2012-5-1', 1000.00,'2012-6-3')
insert into @tb values (12, 1000.00, '2012-6-1', 1000.00, '2012-7-2')
insert into @tb values (13, 3000.00, '2012-7-1', Null, Null)
/*
ID 应支付额 应支付时间 实际支付额 实际支付时间
*/
select isnull(convert(varchar(10),a.mn,121),'Summary:'), sum(chargeamount) as chargeamount, sum(payment) as payment,
isnull(sum(chargeamount),0) - isnull(sum(payment) ,0) as rest
from (
select dateadd(month, datediff(month, 0, chargedate),0) as mn
from @tb where chargedate is not null
union
select dateadd(month, datediff(month, 0, paydate),0) as mn
from @tb where paydate is not null
) as a
left join (
select dateadd(month, datediff(month, 0, chargedate),0) as mn, sum(chargeamount) as chargeamount
from @tb
group by dateadd(month, datediff(month, 0, chargedate),0)
) as b on b.mn = a.mn
left join (
select dateadd(month, datediff(month, 0, paydate),0) as mn, sum(payment) as payment
from @tb
group by dateadd(month, datediff(month, 0, paydate),0)
) as c on c.mn = a.mn
group by a.mn
with cube
-- ,chargeamount,payment,rest
-- 2012-05-01,4000.0000,1000.0000,3000.0000
-- 2012-06-01,2000.0000,2000.0000,.0000
-- 2012-07-01,6000.0000,4000.0000,2000.0000
-- Summary:,12000.0000,7000.0000,5000.0000