如何在SQL Server中将带单位的数量合并为单行字符串？

数据库结构 #

CREATE TABLE Unit (
  id INT primary key,
  UnitName nvarchar(10) 
);
INSERT INTO Unit (id,UnitName) VALUES (1,'Ton');
INSERT INTO Unit (id,UnitName) VALUES (2,'Kg');
INSERT INTO Unit (id,UnitName) VALUES (3,'g');

CREATE  TABLE UnitGroup (
  id INT primary key,
  UnitGroupName nvarchar(50) 
);
INSERT INTO UnitGroup (id,UnitGroupName) VALUES (1,'1Ton = 1000kg = 1000000g');

CREATE TABLE UnitGroupDetail (
  id INT primary key,
  UnitGroupId int,
  UnitId int,
  ConversionRate decimal(19,5),
  FOREIGN KEY (UnitGroupId) REFERENCES UnitGroup(id),
  FOREIGN KEY (UnitId) REFERENCES Unit(id),
);

CREATE TABLE Item (
  id INT primary key,
  ItemName nvarchar(50) ,
  UnitGroupId int,
  FOREIGN KEY (UnitGroupId) REFERENCES UnitGroup(id),
);

INSERT INTO Item (id,ItemName,UnitGroupId) VALUES (1,'Item 1',1);

INSERT INTO UnitGroupDetail (id,UnitGroupId,UnitId,ConversionRate) VALUES (1,1,1,1.0);
INSERT INTO UnitGroupDetail (id,UnitGroupId,UnitId,ConversionRate) VALUES (2,1,2,1000.0);
INSERT INTO UnitGroupDetail (id,UnitGroupId,UnitId,ConversionRate) VALUES (3,1,3,1000000.0);

需求说明 #

创建函数，传入商品ID和基础单位（转换率为1）下的数量值，输出类似105Ton, 823kg, 490g的单行组合格式，将数量拆分到对应单位组的各个单位中。

现有尝试代码 #

DECLARE @ItemId int =1
DECLARE @Quantity decimal(19,5)=105.82349  -- 基础单位下的数值
DECLARE @UnitDetailId smallint = null

SELECT *,
       @Quantity / conversionrate,
       @Quantity - ( @Quantity / conversionrate )
FROM   unitgroupdetail
       INNER JOIN item ON ( item.unitgroupid = unitgroupdetail.unitgroupid )
       INNER JOIN unit ON ( unitgroupdetail.unitid = unit.id )
WHERE  item.id = @ItemId
ORDER  BY unitgroupdetail.conversionrate 

现有代码无法直接得到目标格式的单行输出，需要调整逻辑实现数量拆分与字符串拼接。

解决方案 #

标量函数实现 #

创建一个标量函数，通过遍历单位组内的单位，逐步拆分数量并拼接结果：

CREATE FUNCTION dbo.ConvertQuantityToMultiUnit(@ItemId INT, @Quantity DECIMAL(19,5))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX) = ''
    DECLARE @RemainingQty DECIMAL(19,5) = @Quantity
    DECLARE @UnitName NVARCHAR(10)
    DECLARE @ConversionRate DECIMAL(19,5)
    DECLARE @CurrentQty INT

    -- 按转换率升序遍历单位（从大单位到小单位）
    DECLARE UnitCursor CURSOR FOR
        SELECT u.UnitName, ugd.ConversionRate
        FROM UnitGroupDetail ugd
        JOIN Item i ON i.UnitGroupId = ugd.UnitGroupId
        JOIN Unit u ON ugd.UnitId = u.Id
        WHERE i.Id = @ItemId
        ORDER BY ugd.ConversionRate ASC

    OPEN UnitCursor
    FETCH NEXT FROM UnitCursor INTO @UnitName, @ConversionRate

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 取当前单位的整数数量
        SET @CurrentQty = FLOOR(@RemainingQty)
        -- 仅添加非零数量的单位
        IF @CurrentQty > 0
        BEGIN
            IF @Result <> '' SET @Result += ', '
            SET @Result += CAST(@CurrentQty AS NVARCHAR) + @UnitName
        END
        -- 计算剩余量并转换为下一个单位的数值
        SET @RemainingQty = (@RemainingQty - @CurrentQty) * @ConversionRate

        FETCH NEXT FROM UnitCursor INTO @UnitName, @ConversionRate
    END

    CLOSE UnitCursor
    DEALLOCATE UnitCursor

    RETURN @Result
END
GO

调用函数 #

DECLARE @ItemId INT = 1
DECLARE @Quantity DECIMAL(19,5) = 105.82349
SELECT dbo.ConvertQuantityToMultiUnit(@ItemId, @Quantity) AS ConvertedQuantity

输出结果 #

105Ton, 823kg, 490g

逻辑说明 #

1. 按转换率升序遍历单位组内的单位（从大单位到小单位，如Ton→Kg→g）
2. 每次取剩余数量的整数部分作为当前单位的数值
3. 剩余数量减去整数部分后，乘以当前单位的转换率，得到下一个小单位的总数量
4. 将所有非零的单位数值与名称拼接成最终字符串

内容的提问来源于stack exchange，提问作者Shehab Mekhlafy