sql 代码:
select step_title,(case when cust_apply_duedate>GetDate() then '逾期' else '正常' end ) as status,count(*) as amount
from l_loan_info inner join L_step_info on l_loan_info.step_id=L_step_info.step_id
GROUP BY L_step_info.step_title,cust_apply_duedate
-------------------------------------
step_title status amount
申请 逾期 2
申请 正常 3
审核 逾期 6
审核 正常 4
-------------------------------------
我想在后面加 字段 “sum” step_title status amount sum
申请 逾期 2 5
申请 正常 3 5
审核 逾期 6 10
审核 正常 4 10
-----------------------------------
请问这样的sql 语句怎么写
select step_title,(case when cust_apply_duedate>GetDate() then '逾期' else '正常' end ) as status,count(*) as amount
from l_loan_info inner join L_step_info on l_loan_info.step_id=L_step_info.step_id
GROUP BY L_step_info.step_title,cust_apply_duedate
-------------------------------------
step_title status amount
申请 逾期 2
申请 正常 3
审核 逾期 6
审核 正常 4
-------------------------------------
我想在后面加 字段 “sum” step_title status amount sum
申请 逾期 2 5
申请 正常 3 5
审核 逾期 6 10
审核 正常 4 10
-----------------------------------
请问这样的sql 语句怎么写
(select sum(amount) from
(
select step_title,
(case when cust_apply_duedate>GetDate() then '逾期' else '正常' end ) as status,
count(*) as amount
from l_loan_info inner join L_step_info on l_loan_info.step_id=L_step_info.step_id
GROUP BY L_step_info.step_title,cust_apply_duedate
) t2 where t1.step_title = t2.step_title) [sum]
from
(
select step_title,
(case when cust_apply_duedate>GetDate() then '逾期' else '正常' end ) as status,
count(*) as amount
from l_loan_info inner join L_step_info on l_loan_info.step_id=L_step_info.step_id
GROUP BY L_step_info.step_title,cust_apply_duedate
) t1
;with f as
(
select step_title,(case when cust_apply_duedate>GetDate() then '逾期' else '正常' end ) as status,count(*) as amount
from l_loan_info inner join L_step_info on l_loan_info.step_id=L_step_info.step_id
GROUP BY L_step_info.step_title,cust_apply_duedate )
select
step_title,status,amount,sum(amount) as amount
from
f
group by
step_title,status,amount
t1.status ,
count(1) amount,
[sum] = (select sum(amount) from
(
select step_title,
(case when cust_apply_duedate>GetDate() then '逾期' else '正常' end ) as status
from l_loan_info inner join L_step_info on l_loan_info.step_id=L_step_info.step_id
) t2 where t2.step_title = t1.step_title)
from
(
select step_title,
(case when cust_apply_duedate>GetDate() then '逾期' else '正常' end ) as status
from l_loan_info inner join L_step_info on l_loan_info.step_id=L_step_info.step_id
) t1