SQL Server更新不更新小数

Question

我试图用DECIMAL（5,2）数据类型更新列。但是，SQL Server似乎正在四舍五入，而不是存储小数位。

例如，其中一个值为1.53，并且其在表中存储为1.00。

这里的查询

DECLARE @MaxLike DECIMAL (5,2), @MaxComment DECIMAL (5,2), @MaxFavourite DECIMAL (5,2) 
SET @MaxLike = 1.0*100/(SELECT MAX(z.MaxLike) FROM (SELECT(COUNT(COALESCE(Liked,0))) AS MaxLike FROM Design_LikeRating GROUP BY DesignID) z) 
SET @MaxComment = 1.0*100/(SELECT MAX(z.MaxComment) FROM (SELECT(COUNT(COALESCE(CommentID,0))) AS MaxComment FROM Comment_CommentDetail WHERE IsDeleted = 0 GROUP BY OverallParentGUID) z) 
SET @MaxFavourite = 1.0*100/(SELECT MAX(z.MaxFavourite) FROM (SELECT (COUNT(COALESCE(DesignID,0))) AS MaxFavourite FROM Design_Favourite GROUP BY DesignID) z) 


-- VoteCount and Rate 
DECLARE @table1 TABLE (DesignID INT PRIMARY KEY, Rate DECIMAL(5,2)) 
INSERT INTO @table1 
SELECT DesignID, CAST (Rate AS DECIMAL(5,2)) AS Rate 
FROM 
(
SELECT DesignID, COUNT(COALESCE(Liked,0.0)) AS Rate 
FROM Design_LikeRating WITH (NOLOCK) 
GROUP BY DesignID 
) z 

-- FavCount 
DECLARE @table2 TABLE (DesignID INT PRIMARY KEY, FavCount DECIMAL(5,2)) 
INSERT INTO @table2 
SELECT DesignID, CAST (FavCount AS DECIMAL (5,2)) AS FavCount 
FROM 
(
SELECT DesignID, COUNT(COALESCE(DesignID,0.0)) AS FavCount 
FROM Design_Favourite WITH (NOLOCK) 
GROUP BY DesignID 
) x 

-- CommentCount 
DECLARE @table3 TABLE (DesignGUID UNIQUEIDENTIFIER PRIMARY KEY, CommentCount DECIMAL(5,2)) 
INSERT INTO @table3 
SELECT OverallParentGUID, CAST (CommentCount AS DECIMAL(5,2)) AS CommentCount 
FROM 
(
SELECT OverallParentGUID, COUNT(DISTINCT COALESCE(x.DesignID,0.0)) AS CommentCount 
FROM Comment_CommentDetail z WITH (NOLOCK) 
    INNER JOIN Design_DesignDetail x WITH (NOLOCK) ON x.DesignGUID = z.OverallParentGUID 
WHERE z.IsDeleted = 0 AND x.UserIDInt != z.UserIDInt 
GROUP BY OverallParentGUID 
) y 
; 

WITH CTE AS 
      (
      SELECT (COALESCE((1.0 * z.Rate*@MaxLike),0.0) + COALESCE((1.0 * y.FavCount*@MaxFavourite),0.0) + COALESCE((1.0 * x.CommentCount*@MaxFavourite),0.0)) AS PopularityScore, a.DesignID 
      FROM Design_DesignDetail a 
       LEFT JOIN @table1 AS z ON z.DesignID = a.DesignID 
       LEFT JOIN @table2 AS y ON y.DesignID = a.DesignID 
       LEFT JOIN @table3 AS x ON x.DesignGUID = a.DesignGUID 
      WHERE DesignID > 300000 and DesignID = 444409 
      ) 

UPDATE Design_DesignDetail 
SET  PopularityScore = z.PopularityScore 
FROM Design_DesignDetail a 
     INNER JOIN CTE z on z.DesignID = a.DesignID 

UPDATE 我会停下巧言令色并张贴在这里整个查询。正如你所看到的，它得到了所有人的答案，但它仍然没有工作！

Answer 1

这种表达必须给出一个int或有刻度0的十进制（无十二月地方）

(ISNULL((z.Rate*@MaxLike),0) + ISNULL((y.FavCount*@MaxFavourite),0) + ISNULL((x.CommentCount*@MaxFavourite),0)) 

我猜你需要投浮动每个ISNULL里面不知道数据类型

请不要使用NOLOCK像

编辑，完整的代码更新

你COUNT聚集将返回INT经过v所以你的小数点（5,2）值可能有xxx.00。

一些评论添加到@ table1的负载

INSERT INTO @table1 
SELECT DesignID, 
-- gbn The CAST changes 2 to 2.00 
CAST (Rate AS DECIMAL(5,2)) AS Rate 
FROM 
(
SELECT DesignID, 
    -- gbn COALESCE does nothing. COUNT gives int and simply counts non-null values 
    COUNT(COALESCE(Liked,0.0)) AS Rate 
FROM Design_LikeRating WITH (NOLOCK) 
GROUP BY DesignID 
) z 

看到这里Count(*) vs Count(1)我的回答更多的解释

Answer 2

您probablly从某处整数运算的痛苦。

而且，除非您喜欢四舍五入错误，否则在进行呼吸时不要使用float。

Answer 3

什么是z.Rate，@MaxLike，y.FavCount，@MaxFavourite和x.CommentCount的类型？

我敢打赌y.FavCount和x.CommentCount是整数。

Answer 4

SELECT ISNULL(1.0 * z.Rate*@MaxLike, 0.0) + 
     ISNULL(1.0 * y.FavCount*@MaxFavourite, 0.0) + 
     ISNULL(1.0 * x.CommentCount*@MaxFavourite, 0.0) AS PopularityScore, a.DesignID