时间戳

首页 > 解决方案 > 如何截断计算列中的小数？

sql-server - 如何截断计算列中的小数？

问题描述

我需要创建一个查询，以显示编辑过一本书以上的编辑的平均生产力，但他们的第一本书出版的精度为 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-serverprecisioncalculated-columns

解决方案

您需要将数据类型更改为DECIMAL(X, 2). 该ROUND函数实际上并不更改数据类型，只是截断或舍入特定位置的值。

CONVERT(
    DECIMAL(10, 2),
    round(NoOfPages * 1.0 /datediff(
            day, 
            lag(b.DateOfPublication) over(partition by b.EditorID order by b.DateOfPublication),
            DateOfPublication),
        2))

另请注意，在转换为 时DECIMAL，该值会自动四舍五入，因此您实际上不必调用该ROUND函数（除非您想截断小数）。

;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

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

推荐阅读