计算组百分比到小数点后1位-SQL

debugcn 发表于 Dev

用户名

我现在有这个SQL：

SELECT Count(create_weekday), 
   create_weekday, 
   Count(create_weekday) * 100 / (SELECT Count(*) 
                                  FROM   call_view 
                                  WHERE 
   ( create_month = Month(Now() - 
                    INTERVAL 1 month) ) 
   AND ( create_year = Year( 
         Now() - INTERVAL 1 month) ) 
   AND customer_company_name = "Company"
       ) AS Percentage
 FROM   call_view 
WHERE  ( create_month = Month(Now() - INTERVAL 1 month) ) 
   AND ( create_year = Year(Now() - INTERVAL 1 month) ) 
   AND customer_company_name = "Company" 
   GROUP BY CREATE_WEEKDAY
ORDER BY (CASE CREATE_WEEKDAY
WHEN 'Monday'   THEN 1
WHEN 'Tuesday'   THEN 2
WHEN 'Wednesday'   THEN 3
WHEN 'Thursday'   THEN 4
WHEN 'Friday'    THEN 5
WHEN 'Saturday'  THEN 6
WHEN 'Sunday' THEN 7
ELSE 100 END)

它正在工作，我收到了结果：

Count(create_weekday) | Create_Weekday | Percentage
225                       Monday          28.0899

如何舍入到小数点后一位？（例如28.1）

希望有帮助

苏迪伦

使用ROUND（百分比，1）：

SELECT Count(create_weekday), 
   create_weekday, 
   ROUND(Count(create_weekday) * 100 / (SELECT Count(*) 
                                  FROM   call_view 
                                  WHERE 
   ( create_month = Month(Now() - 
                    INTERVAL 1 month) ) 
   AND ( create_year = Year( 
         Now() - INTERVAL 1 month) ) 
   AND customer_company_name = "Company"
       ), 1) AS Percentage
 FROM   call_view 
WHERE  ( create_month = Month(Now() - INTERVAL 1 month) ) 
   AND ( create_year = Year(Now() - INTERVAL 1 month) ) 
   AND customer_company_name = "Company" 
   GROUP BY CREATE_WEEKDAY
ORDER BY (CASE CREATE_WEEKDAY
WHEN 'Monday'   THEN 1
WHEN 'Tuesday'   THEN 2
WHEN 'Wednesday'   THEN 3
WHEN 'Thursday'   THEN 4
WHEN 'Friday'    THEN 5
WHEN 'Saturday'  THEN 6
WHEN 'Sunday' THEN 7
ELSE 100 END)