如何截断计算列中的小数点？-解网

问：

我需要创建一个查询，以显示编辑人员的平均工作效率，这些编辑工作过不止一本书，除了他们的第一本书以 0.01 页/天的精度出版。

我现在显示了正确的列，但我需要减少列中显示的零数量。AverageProductivity

要显示的列包括

EditorName

BookName

计算列AverageProductivity

以下是表格及其列

AGENT  AgentID (PK,varchar(11), not null)
       AgentName (varchar(25), not null)

BOOK   BookName (PK, varchar(45), not null)
       Genre (varchar(25), not null)
       DateOfPublication (date, not null)
       NoOfPages (int, not null)
       WriterID (PK, FK,, varchar(11), not null)
       EditorID (FK, varchar(11), not null)

EDITOR EditorID (PK, varchar(11), not null)
       EditorName (varchar(25), not null)
       Mentors_EditorID (FK, varchar(11), null)

WRITER WriterID (PK, varchar(11), not null)
       WriterName (varchar(25), not null)
       AgentID (FK, varchar(11), not null)

示例数据

insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('Valley of Heroes','10','Fiction','2010-01-12',874,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('The Ruler''s Return','11','Fantasy','2012-03-14',765,'22');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('eRobot','11','Fantasy','2011-04-15',264,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('An Uncle''s Letters','12','Fiction','2012-06-12',258,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('Pretty flowers','13','Album','2013-01-31',148,'22');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('A Tale of Lions','12','Fantasy','2012-08-17',301,'21');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('eRobot','13','Sci Fi','2012-10-04',465,'23');

我尝试使用格式语法来更改精度，但我不确定它的正确语法。

这是查询...

use SignumLibri_01
select * from (
    select e.EditorName, b.BookName,
       round(NoOfPages * 1.0
                 / datediff(day, lag(b.DateOfPublication)
                                   over (partition by b.EditorID
                                           order by b.DateOfPublication
                                         )
                               ,DateOfPublication
                            )
               , 2) AverageProductivity       
   from book b
   inner join editor e on e.EditorID = b.EditorID 
) x
where AverageProductivity is not null

有没有办法截断所有额外的零，所以我必须达到 0.00 的精度？

结果。。。

Melanie eRobot  0.580000000000
Melanie An Uncle's Letters  0.610000000000
George  Pretty flowers  0.460000000000

SQL-Server 精度计算列

;WITH ValueWithDecimals AS
(
    SELECT
        Value = 50 * 1.0 / 19
)
SELECT
    Original = V.Value,
    RoundedAt2 = ROUND(V.Value, 2),
    RoundedAt4 = ROUND(V.Value, 4),
    ConvertedToDecimal2 = CONVERT(DECIMAL(10, 2), V.Value),
    ConvertedToDecimal4 = CONVERT(DECIMAL(10, 4), V.Value)
FROM
    ValueWithDecimals AS V

结果：

Original    RoundedAt2  RoundedAt4  ConvertedToDecimal2 ConvertedToDecimal4
2.631578    2.630000    2.631600    2.63                2.6316

您可以看到舍入值和十进制值匹配（不是在比例和精度上，而是在内容上）。

上一个：对 SQL Server 浮点数和 SQLCMD 返回的误解

下一个：如何最小化此求和中的数值误差？

如何截断计算列中的小数点？

How do you truncate decimals in a computed column?

评论