34,588
社区成员
发帖
与我相关
我的任务
分享
SELECT A.sale_user as '统一认证号',
d.username as '姓名' ,
E.branch_name as '网点名',
A.sale_num as '金额' ,
A.sale_kh as '卡号' , A.sale_typecust as '帐户名' ,
B.account_no as '卡号', B.ckkh_name as '帐户' ,(select cast(round(B.ye/10000,2,1) as decimal(18,2))) as '余额' ,
c.hz as '汇总', D.userrole ,
( CASE WHEN C.hz >70 THEN '完成'
WHEN D.userrole ='部门用户' and C.hz >80 then '完成'
WHEN B.ye IS NULL THEN '无记录' ELSE '未完成'
END ) 结果 ,
( CASE WHEN A.sale_num <= (select cast(round(B.ye/10000,2,1) as decimal(18,2))) then A.sale_num
WHEN A.sale_num > (select cast(round(B.ye/10000,2,1) as decimal(18,2))) then (select cast(round(B.ye/10000,2,1) as decimal(18,2))) end ) as sj,
A.sale_spzt as '审批状态',
A.sale_bizid as '业务号', A.sale_id as '编号' ,A.sale_typeid as '产品号',A.sale_branch as '网点号',
A.sale_sdate as '日期' ,A.sale_wdate as '时间'
FROM salemx AS A
LEFT JOIN ckkh AS B ON A.sale_kh = B.account_no
LEFT JOIN ( SELECT A.sale_user , SUM(B.ye) AS hz
FROM salemx AS A
LEFT JOIN ckkh AS B ON A.sale_kh = B.account_no
GROUP BY A.sale_user
) AS C ON A.sale_user = C.sale_user
lEFT JOIN users AS D ON A.sale_user = D.usernum
LEFT JOIN branch AS E ON D.userbranch = E.branch_num
where a.sale_bizid='25'
order by A.sale_user
( CASE WHEN A.sale_num <= (select cast(round(B.ye/10000,2,1) as decimal(18,2))) then A.sale_num
WHEN A.sale_num > (select cast(round(B.ye/10000,2,1) as decimal(18,2))) then (select cast(round(B.ye/10000,2,1) as decimal(18,2))) end ) as sj,
LEFT JOIN ( SELECT A.sale_user , SUM(B.ye) AS hz
FROM salemx AS A
LEFT JOIN ckkh AS B ON A.sale_kh = B.account_no
GROUP BY A.sale_user
) AS C ON A.sale_user = C.sale_user
SUM(CASE WHEN A.sale_num <= ( CAST(ROUND(B.ye / 10000, 2, 1) AS DECIMAL(18,
2)) )
THEN A.sale_num
WHEN A.sale_num > ( CAST(ROUND(B.ye / 10000, 2, 1) AS DECIMAL(18,
2)) )
THEN ( SELECT CAST(ROUND(B.ye / 10000, 2, 1) AS DECIMAL(18,
2))
)
END) OVER ( PARTITION BY A.sale_user ) AS sj ,
的 意思吗
特别是 OVER ( PARTITION BY A.sale_user )SELECT A.sale_user AS '统一认证号' ,
D.username AS '姓名' ,
E.branch_name AS '网点名' ,
A.sale_num AS '金额' ,
A.sale_kh AS '卡号' ,
A.sale_typecust AS '帐户名' ,
B.account_no AS '卡号' ,
B.ckkh_name AS '帐户' ,
( SELECT CAST(ROUND(B.ye / 10000, 2, 1) AS DECIMAL(18, 2))
) AS '余额' ,
C.hz AS '汇总' ,
D.userrole ,
( CASE WHEN C.hz > 70 THEN '完成'
WHEN D.userrole = '部门用户'
AND C.hz > 80 THEN '完成'
WHEN B.ye IS NULL THEN '无记录'
ELSE '未完成'
END ) 结果 ,
SUM(CASE WHEN A.sale_num <= ( CAST(ROUND(B.ye / 10000, 2, 1) AS DECIMAL(18,
2)) )
THEN A.sale_num
WHEN A.sale_num > ( CAST(ROUND(B.ye / 10000, 2, 1) AS DECIMAL(18,
2)) )
THEN ( SELECT CAST(ROUND(B.ye / 10000, 2, 1) AS DECIMAL(18,
2))
)
END) OVER ( PARTITION BY A.sale_user ) AS sj ,
A.sale_spzt AS '审批状态' ,
A.sale_bizid AS '业务号' ,
A.sale_id AS '编号' ,
A.sale_typeid AS '产品号' ,
A.sale_branch AS '网点号' ,
A.sale_sdate AS '日期' ,
A.sale_wdate AS '时间'
FROM salemx AS A
LEFT JOIN ckkh AS B ON A.sale_kh = B.account_no
LEFT JOIN ( SELECT A.sale_user ,
SUM(B.ye) AS hz
FROM salemx AS A
LEFT JOIN ckkh AS B ON A.sale_kh = B.account_no
GROUP BY A.sale_user
) AS C ON A.sale_user = C.sale_user
LEFT JOIN users AS D ON A.sale_user = D.usernum
LEFT JOIN branch AS E ON D.userbranch = E.branch_num
WHERE A.sale_bizid = '25'
ORDER BY A.sale_user;