SQL更新包含计数的多列

Question

我们目前有SQL更新语句，可以满足我们的需求并更新tbl_rprt表。以下是一些更新查询，还有一些类似的查询。我想知道是否还有其他方法可以做到这一点，也许可以将所有这些方法组合成一个SQL语句。CCD_ 2是针对每个类别和房间rm的wrk_tbl_flgs中的特定标志的所有计数的报告。

UPDATE #tbl_rprt
SET a_count = a.a_count
FROM (SELECT COUNT(*) a_count, new_c1, new_c2 FROM wrk_tbl_flgs WHERE a_flg = 'Y' GROUP BY new_c1, new_c2) a
WHERE a.new_c1 = #tbl_rprt.class AND a.new_c2 = #tbl_rprt.rm
UPDATE #tbl_rprt
SET b_count = a.b_count
FROM (SELECT COUNT(*) b_count, new_c1, new_c2 FROM wrk_tbl_flgs WHERE b_flg = 'Y' GROUP BY new_c1, new_c2) a
WHERE a.new_c1 = #tbl_rprt.class AND a.new_c2 = #tbl_rprt.rm
UPDATE #tbl_rprt
SET c_count = a.c_count
FROM (SELECT COUNT(*) c_count, new_c1, new_c2 FROM wrk_tbl_flgs WHERE c_flg = 'Y' GROUP BY new_c1, new_c2) a
WHERE a.new_c1 = #tbl_rprt.class AND a.new_c2 = #tbl_rprt.rm
UPDATE #tbl_rprt
SET d_count = a.d_count
FROM (SELECT COUNT(*) d_count, new_c1, new_c2 FROM wrk_tbl_flgs WHERE d_flg = 'Y' GROUP BY new_c1, new_c2) a
WHERE a.new_c1 = #tbl_rprt.class AND a.new_c2 = #tbl_rprt.rm
UPDATE #tbl_rprt
SET e_count = a.e_count
FROM (SELECT COUNT(*) e_count, new_c1, new_c2 FROM wrk_tbl_flgs WHERE e_flg = 'Y' GROUP BY new_c1, new_c2) a
WHERE a.new_c1 = #tbl_rprt.class AND a.new_c2 = #tbl_rprt.rm

更新wrk_tbl_flgs具有student_id和特定标志

student_id, class, rm, a_flg, b_fl, c_flg ....

Answer 1

目前，您可以使用以下条件和组合为一：

UPDATE R SET
a_count = a.a_count
, b_count = a.b_count
--... repeat for all columns
FROM #tbl_rprt R
INNER JOIN (
SELECT
new_c1, new_c2
, SUM(CASE WHEN a_flg = 'Y' THEN 1 ELSE 0 END) a_count
, SUM(CASE WHEN b_flg = 'Y' THEN 1 ELSE 0 END) b_count
--, ... repeat for all flags
FROM wrk_tbl_flgs
GROUP BY new_c1, new_c2
) a ON a.new_c1 = R.class AND a.new_c2 = R.rm;