这是我遇到的问题
例：
总价值：£1550.00
每月付款12
计算得出的值为129.16666666666666667，但是您不会显示这样的货币值，它将显示为129.17英镑，等于1550.04英镑，这是错误的
题
是否可以从12期中的11期中删除浮点数/十进制值，而仅在第一笔或最后一笔付款中显示？
结果示例
PaymentNumber Value
1 £129.00
2 £129.00
3 £129.00
4 £129.00
5 £129.00
6 £129.00
7 £129.00
8 £129.00
9 £129.00
10 £129.00
11 £129.00
12 £131.00
感谢您的帮助，非常感谢我提出其他建议。
我已经包含了用于测试此表的代码...并还添加了以下数据。现在使用update语句将是可以接受的，而我将在以后尝试使代码更高效。我确实有一个程序正在运行，该程序以编程方式插入了每月的细目分类。
我正在使用的表信息
CREATE TABLE CustomerFinance (CustomerFinanceID int identity (1,1) not null,
TotalValueOwed decimal(12,4), --1550.00
LengthOfContract int) --LENGTGH IN MONTHS, i.e. 12
CREATE TABLE CustomerFinanceLine (CustomerFinanceLineID int identity (1,1) not null,
CustomerFinanceID int, --FOREIGN KEY LINK
PaymentNumber int, --1, 2, 3 AND SO ON
PaymentValue decimal(12,4)) --THE MONTHLY BREAKDOWN COSTS
--KEYS
alter table CustomerFinance add constraint CustomerFinanceID_PK PRIMARY KEY (CustomerFinanceID)
alter table CustomerFinanceLine add constraint CustomerFinanceLineID_PK PRIMARY KEY (CustomerFinanceLineID)
alter table CustomerFinanceLine add constraint CustomerFinanceID_FK FOREIGN KEY (CustomerFinanceID) REFERENCES CustomerFinance(CustomerFinanceID)
--PaymentNumber COUNTER (RUNS IN A PROCEDURE)
CREATE PROCEDURE FinanceCounter AS
;WITH MyCTE AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY CustomerFinanceID ORDER BY CustomerFinanceLineID) AS NewVariation
FROM CustomerFinanceLine
)
UPDATE MyCTE
SET PaymentNumber = NewVariation
WHERE PaymentNumber IS NULL
数据
--inserted PaymentValue as null for now, ideally i will
-- have a procedure to do this and insert the breakdowns programmatically
--for now an update statement will do fine unless its easier to insert it
INSERT INTO CustomerFinance VALUES (1550.00, 12)
INSERT INTO CustomerFinanceLine VALUES (1, 1, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 2, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 3, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 4, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 5, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 6, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 7, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 8, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 9, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 10, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 11, NULL)
INSERT INTO CustomerFinanceLine VALUES (1, 12, NULL)
最佳答案
您只需要做类似的事情
WITH T(PaymentNumber) AS
(
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12
)
SELECT CASE WHEN PaymentNumber < 12 THEN FLOOR(1550.00/12)
WHEN PaymentNumber = 12 THEN 1550.00 - 11*FLOOR(1550.00/12)
END
FROM T
关于sql - SQL Server-正确分解付款，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34267365/