MBTI测试 - Sanlomy/MBTI GitHub Wiki

--以“人力资源”为例进行运算 select 序号,MBTI,最佳优度, case when 最大适合度>最佳优度 then 最大适合度 else 平均适合度 end as 适合度 from (select yy02.序号,MBTI,平均适合度,最小适合度,最大适合度,最佳优度 from (select 序号,convert(nvarchar,cast(avg((qunt01+qunt02+qunt03+qunt04)*3.5+EFGH) as int))+'%' as 适合度, convert(nvarchar,cast(avg((qunt01+qunt02+qunt03+qunt04)*3.5+EFGH) as int))+'%' as 平均适合度, convert(nvarchar,cast(min((qunt01+qunt02+qunt03+qunt04)*3.5+EFGH) as int))+'%' as 最小适合度, convert(nvarchar,cast(max((qunt01+qunt02+qunt03+qunt04)*3.5+EFGH) as decimal(9,2)))+'%' as 最大适合度 from(select *, case when MBTI like '%I%' then a else 5-a end as qunt01, case when MBTI like '%S%' then b else 5-b end as qunt02, case when MBTI like '%T%' then c else 5-c end as qunt03, case when MBTI like '%P%' then d else 5-d end as qunt04, case when MBTI='ISTJ' then (e+3-f+g+3-h)*2.5 when MBTI='ISFJ' then (e+f+g+3-h)*2.5 when MBTI='INFJ' then (e+f+g+3-h)*2.5 when MBTI='INTJ' then (e+3-f+g+3-h)*2.5 when MBTI='ISTP' then (e+3-f+g+3-h)*2.5 when MBTI='ISFP' then (e+3-f+g+3-h)*2.5 when MBTI='INFP' then (3-e+f+g+3-h)*2.5 when MBTI='INTP' then (e+3-f+g+3-h)*2.5 when MBTI='ESTP' then (3-e+f+g+3-h)*2.5 when MBTI='ENTJ' then (e+f+g+3-h)*2.5 when MBTI='ENTP' then (e+f+g+3-h)*2.5 when MBTI='ENFJ' then (e+3-f+g+3-h)*2.5 when MBTI='ENFP' then (3-e+f+g+3-h)*2.5 when MBTI='ESTJ' then (e+3-f+g+3-h)*2.5 when MBTI='ESFJ' then (e+3-f+g+3-h)*2.5 else (3-e+3-f+g+3-h)*2.5 end as EFGH from (select * from (select *,(a1+a2+a3+a4+a5)-5 as a,(b1+b2+b3+b4+b5)-5 as b,(c1+c2+c3+c4+c5)-5 as c,(d1+d2+d3+d4+d5)-5 as d, (e1+e2+e3)-3 as e,(f1+f2+f3)-3 as f, ABS((g1+g2+g3)-6) as g,(h1+h2+h3)-3 as h from test01) as t001 cross join (select MBTI from test02 where 职业 like '%人力资源%') as MBTI) as ttt) as tttt group by 序号) as yy01 join (select *,cast(ABCD+y as varchar)+'%' as 最佳优度 from (select *, case when z.MBTI='ISTJ' then (e+3-f+g+3-h)*2.5 when z.MBTI='ISFJ' then (e+f+g+3-h)*2.5 when z.MBTI='INFJ' then (e+f+g+3-h)*2.5 when z.MBTI='INTJ' then (e+3-f+g+3-h)*2.5 when z.MBTI='ISTP' then (e+3-f+g+3-h)*2.5 when z.MBTI='ISFP' then (e+3-f+g+3-h)*2.5 when z.MBTI='INFP' then (3-e+f+g+3-h)*2.5 when z.MBTI='INTP' then (e+3-f+g+3-h)*2.5 when z.MBTI='ESTP' then (3-e+f+g+3-h)*2.5 when z.MBTI='ENTJ' then (e+f+g+3-h)*2.5 when z.MBTI='ENTP' then (e+f+g+3-h)*2.5 when z.MBTI='ENFJ' then (e+3-f+g+3-h)*2.5 when z.MBTI='ENFP' then (3-e+f+g+3-h)*2.5 when z.MBTI='ESTJ' then (e+3-f+g+3-h)*2.5 when z.MBTI='ESFJ' then (e+3-f+g+3-h)*2.5 else (3-e+3-f+g+3-h)*2.5 end as y from (select n.序号,MBTI,qunt1,qunt2,qunt3,qunt4,e,f,g,h,(qunt1+qunt2+qunt3+qunt4)*3.5 as ABCD from (select *, case when a<=2 then 5-a else a end as qunt1, case when b<=2 then 5-b else b end as qunt2, case when c<=2 then 5-c else c end as qunt3, case when d<=2 then 5-d else d end as qunt4 from (select *,(a1+a2+a3+a4+a5)-5 as a,(b1+b2+b3+b4+b5)-5 as b,(c1+c2+c3+c4+c5)-5 as c,(d1+d2+d3+d4+d5)-5 as d, (e1+e2+e3)-3 as e,(f1+f2+f3)-3 as f, ABS((g1+g2+g3)-6) as g,(h1+h2+h3)-3 as h from test01) as tt) as m join (select 序号,tt.number1+tt.number2+tt.number3+tt.number4 as MBTI from (select 序号, case when t.a>=8 then 'I' else 'E' end as number1, case when t.b>=8 then 'S' else 'N' end as number2, case when t.c>=8 then 'T' else 'F' end as number3, case when t.d>=8 then 'P' else 'J' end as number4 from (select *,(a1+a2+a3+a4+a5) as a,(b1+b2+b3+b4+b5) as b,(c1+c2+c3+c4+c5) as c,(d1+d2+d3+d4+d5)as d, (e1+e2+e3) as e,(f1+f2+f3) as f, (g1+g2+g3) as g,(h1+h2+h3) as h from test01) as t) as tt) as n on m.序号=n.序号) as z) as y) as yy02 on yy01.序号=yy02.序号) as yyy01 order by 序号 --更新表 delete test01 insert into test01 select * from Sheet1$ select * from test01 where number like '二肥' --查询MBTI和职业领域和职业 select * from test02 where MBTI like 'INFP' 号) as yyy01