新人求助，oracle转换成sql server
select examinedoctor as 技术员,sum(A+B+C+D) as 图像质控总数,
to_char(sum(A),'999,999,999' )as 甲片数,to_char(sum(B),'999,999,999' )as 乙片数,
to_char(sum(C),'999,999,999' )as 丙片数,to_char(sum(D),'999,999,999' )as 丁片数,
to_char(SUM(A) / SUM(A+B+C+D) * 100, '999.99') || '%' 甲片率,
to_char(SUM(B) / SUM(A+B+C+D) * 100, '999.99') || '%' 乙片率,
to_char(SUM(C) / SUM(A+B+C+D) * 100, '999.99') || '%' 丙片率,
to_char(SUM(D) / SUM(A+B+C+D) * 100, '999.99') || '%' 丁片率
from(SELECT t.examinedoctor,
CASE
WHEN image_qc = '0' THEN
1
ELSE
0
END AS A,
CASE
WHEN image_qc = '1' THEN
1
ELSE
0
END AS B,
CASE
WHEN image_qc = '2' THEN
1
ELSE
0
END AS C,
CASE
WHEN image_qc = '3' THEN
1
ELSE
0
END AS D
from ris_studies t
where BSTATE = 100
AND patienttype NOT IN ('体检', '结核门诊')
/* and prof =:prof
and REPORTDATE >= :date1
and REPORTDATE <= :date2*/)
group by examinedoctor
[解决办法]
select examinedoctor as 技术员,sum(A+B+C+D) as 图像质控总数,
cast(sum(A) as varchar)as 甲片数,cast(sum(B) as varchar)as 乙片数,
cast(sum(C) as varchar)as 丙片数,cast(sum(D) as varchar)as 丁片数,
cast(cast(SUM(A) / SUM(A+B+C+D) * 100 as numeric(10,2)) as varchar) + '%' 甲片率,
cast(cast(SUM(B) / SUM(A+B+C+D) * 100 as numeric(10,2)) as varchar) + '%' 乙片率,
cast(cast(SUM(C) / SUM(A+B+C+D) * 100 as numeric(10,2)) as varchar) + '%' 丙片率,
cast(cast(SUM(D) / SUM(A+B+C+D) * 100 as numeric(10,2)) as varchar) + '%' 丁片率
from(SELECT t.examinedoctor,
CASE
WHEN image_qc = '0' THEN
1
ELSE
0
END AS A,
CASE
WHEN image_qc = '1' THEN
1
ELSE
0
END AS B,
CASE
WHEN image_qc = '2' THEN
1
ELSE
0
END AS C,
CASE
WHEN image_qc = '3' THEN
1
ELSE
0
END AS D
from ris_studies t
where BSTATE = 100
AND patienttype NOT IN ('体检', '结核门诊')
/* and prof =:prof
and REPORTDATE >= :date1
and REPORTDATE <= :date2*/
)t
group by examinedoctor
