【SQL Server】SQL语句生成百分比

SELECT  CAST(
           CAST(
               1. * a.tjCount / (
                   SELECT cnum = CASE COUNT(b.id)
                                      WHEN 0 THEN 1
                                      ELSE COUNT(b.id)
                                 END
                   FROM   B AS b
                   WHERE  1 = 1
                          AND DATA_TIME >= CONVERT(VARCHAR(100), GETDATE() -3, 23)
                          AND DATA_TIME < CONVERT(VARCHAR(100), GETDATE() -2, 23)
               ) * 100 AS DECIMAL(5, 2)
           ) AS VARCHAR(50)
       ) + '%' AS 'percents'

FROM  A  AS a ....

如果：a.tjCount是SUM()聚合函数的话，需要使用ISNULL(SUM())

另外时间条件也可以使用 FORMAT(column_name,format) 进行处理，如：SELECT FORMAT(Now(),'YYYY-MM-DD')，显示为：2010-11-16

本文转自xudayu 51CTO博客，原文链接：http://blog.51cto.com/xudayu/424573，如需转载请自行联系原作者