新人求助，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

[LongRui888]

试试这个：

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*/)
         group by examinedoctor

[萤火虫不高飞]

执行后会报 消息 102，级别 15，状态 1，第 4 行 '|' 附近有语法错误。 消息 156，级别 15，状态 1，第 39 行 关键字 'group' 附近有语法错误。 这两个错误，是sql server的格式有什么不同的要求么？

[LongRui888]

引用 2 楼 hnj19900121 的回复:

执行后会报 消息 102，级别 15，状态 1，第 4 行 '|' 附近有语法错误。 消息 156，级别 15，状态 1，第 39 行 关键字 'group' 附近有语法错误。 这两个错误，是sql server的格式有什么不同的要求么？

改了一下，你再试试：

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

[萤火虫不高飞]

成功了，谢谢