交叉表问题，有例子，高手进来
select re2_result_rsi_azo.job_no,
re2_module_rsi_azo.amine_name,
(case composite_id when 1 then 'A+C ' end ) as res1,
(case composite_id when 2 then 'B+D ' end ) as res2,
(case composite_id when 3 then 'E+F ' end ) as res3
from re2_result_rsi_azo, re2_module_rsi_azo
where re2_result_rsi_azo.amine_id = re2_module_rsi_azo.amine_id
group by re2_result_rsi_azo.job_no,
re2_module_rsi_azo.amine_name,
composite_id
上面是sql，下面是结果
job_noamine_nameres1res2res3
J00047879 kkkkllkA+C
J00047879 kkkkllkB+D
J00047879 kkkkllk
J00047879 ooooooooooasdfA+C
J00047879 ooooooooooasdfB+D
J00047879 ooooooooooasdf
为什么都不出现在一行？
group 中如果没有这一列的话，sqlserver 报错 composite_i
应该怎么弄？
[解决办法]
--update
Select re2_result_rsi_azo.job_no, re2_module_rsi_azo.amine_name,
max(case composite_id when 1 then 'A+C ' end ) as res1,
max(case composite_id when 2 then 'B+D ' end ) as res2,
max(case composite_id when 3 then 'E+F ' end ) as res3
from re2_result_rsi_azo, re2_module_rsi_azo
where re2_result_rsi_azo.amine_id = re2_module_rsi_azo.amine_id
group by re2_result_rsi_azo.job_no, re2_module_rsi_azo.amine_name
[解决办法]
加上max處理即可
[解决办法]
整理下，加上別名
select
A.job_no,
B.amine_name,
Max(case composite_id when 1 then 'A+C ' end ) as res1,
Max(case composite_id when 2 then 'B+D ' end ) as res2,
Max(case composite_id when 3 then 'E+F ' end ) as res3
from re2_result_rsi_azo A, re2_module_rsi_azo B
where A.amine_id = B.amine_id
group by A.job_no, B.amine_name, composite_id
[解决办法]
select
A.job_no,
B.amine_name,
Max(case composite_id when 1 then 'A+C ' end ) as res1,
Max(case composite_id when 2 then 'B+D ' end ) as res2,
Max(case composite_id when 3 then 'E+F ' end ) as res3
from
re2_result_rsi_azo A,
re2_module_rsi_azo B
where
A.amine_id = B.amine_id
group by
A.job_no, B.amine_name
