表formtable_main_20中有xz1，xz2，xz3....xz50 五十個用作填寫標準答案的text欄位
現要將表formtable_main_20_dt1中的st1，st2，st3,.....st50 五十個試題答案和上表中的五十個答案進行對比，正確的得兩分，不正確不得分，并且輸出一個總分。
formtable_main_20_dt1中mainid欄位對應formtable_main_20id欄位，只在id和mainid相同時進行對應打分。
求各位大佬解惑。
uj5u.com熱心網友回復：
簡單說下思路好了，將列轉行，參考下邊這個陳述句with t as (
select 1 as id,1 as xz1,2 as xz2,3 as xz3
)
select * from t
unpivot(xz for col in (xz1,xz2,xz3)) p
列轉行后，將兩個表按照格式進行關聯，比如試卷id，題號等，即，1號試卷第一題(xz1)對應學生1，試卷1，st1，
xz的值與st的值相等即為答對，統計答對數量*2即得分
uj5u.com熱心網友回復：
with formtable_main_20 as (
select 1 as id,1 as xz1,2 as xz2,3 as xz3,4 as xz4
),formtable_main_20_dt1 as (
select 1 as mainid,1 as 學號,1 as st1,2 as st2,3 as st3,3 as st4
union all select 1 as mainid,2 as 學號,1 as st1,2 as st2,3 as st3,4 as st4
)
select a.id,b.學號,sum(case when a.xz=b.st then 2 else 0 end) as 得分 from (
select * from formtable_main_20
unpivot(xz for col in (xz1,xz2,xz3,xz4)) p
) a
left join (
select * from formtable_main_20_dt1
unpivot(st for col in (st1,st2,st3,st4)) p
) b on a.id=b.mainid and replace(a.col,'xz','')=replace(b.col,'st','')
group by a.id,b.學號
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8373.html
標籤：應用實例