两个数据表统计，合成一条数据的SQL 刷新视图 sp_refreshview viewname

SELECT SUM
( oneCount ) oneCount,
SUM ( twoCount ) twoCount,
SUM ( threeCount ) threeCount,
SUM ( fourCount ) fourCount,
SUM ( fiveCount ) fiveCount,
SUM ( sixCount ) sixCount,
SUM ( severnCount ) severnCount,
SUM ( eightCount ) eightCount,
SUM ( nineCount ) nineCount
FROM
(
SELECT COALESCE
( SUM ( CASE WHEN p.new_node_code = ‘xx01’ THEN 1 ELSE 0 END ), 0 ) oneCount,
SUM ( CASE WHEN p.new_node_code = ‘xx02’ THEN 1 ELSE 0 END ) twoCount,
SUM ( CASE WHEN p.new_node_code = ‘xx03’ THEN 1 ELSE 0 END ) threeCount,
SUM ( CASE WHEN p.new_node_code = ‘xx04’ THEN 1 ELSE 0 END ) fourCount,
SUM ( CASE WHEN p.new_node_code = ‘xx05’ THEN 1 ELSE 0 END ) fiveCount,
SUM ( CASE WHEN p.new_node_code = ‘xx06’ THEN 1 ELSE 0 END ) sixCount,
SUM ( CASE WHEN p.new_node_code = ‘xx07’ THEN 1 ELSE 0 END ) severnCount,
SUM ( CASE WHEN p.new_node_code = ‘xx08’ THEN 1 ELSE 0 END ) eightCount,
0 nineCount
FROM
X_RepairProject p
LEFT JOIN X_RepairSolution s ON p.repairSolutionCode= s.code
WHERE
p.isDelete= ‘0’ UNION ALL
SELECT
0 oneCount,
0 twoCount,
0 threeCount,
0 fourCount,
0 fiveCount,
0 sixCount,
0 severnCount,
0 eightCount,
COALESCE ( SUM ( CASE WHEN d.NODE_CODE= ‘111’ THEN 1 ELSE 0 END ), 0 ) nineCount
FROM
Damage d
WHERE
d.ISDELETE= 0
AND d.PATROL_TYPE_CODE= ‘X01’
) t