日期:2014-05-18  浏览次数:20498 次

sqlserver 一条SQL语句解决两种统计方式的问题
数据表大致如下:
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 的金额需要用一条语句查询出。

挠破头皮也没构思出来。

大神们指教。

------解决方案--------------------
select month(实际支付时间),total=sum(应支付额)-sum(实际支付额) from 表 where year(实际支付时间)=year(getdate()) group by month(实际支付时间)
------解决方案--------------------
SQL code
declare @tb table (id int, 
    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 行)
--