sql中的数字总和 [英] sum of digits in sql

问题描述

数据库方案由四个表组成:

The database scheme consists of four tables:

Product(maker, model, type)    
PC(code, model, speed, ram, hd, cd, price)    
Laptop(code, model, speed, ram, hd, screen, price)    
Printer(code, model, color, type, price)

• 产品表包含有关制造商、型号和产品类型(PC"、笔记本电脑"或打印机")的数据.假设产品表中的型号对于所有制造商和产品类型都是唯一的.

  • The Product table contains data on the maker, model number, and type of product ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all makers and product types.

    PC 表中的每台个人计算机都由唯一代码明确标识，另外还通过其型号(外键指产品表)、处理器速度(以 MHz 为单位)-速度字段、RAM 容量来表征(以 Mb 为单位)- ram、硬盘驱动器容量(以 Gb 为单位)- hd、CD-ROM 速度(例如4x")- cd 及其价格.

    Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, '4x') - cd, and its price.

    计算产品表中每个型号的 ID(型号列)中的数字总和.结果集:模型，数字总和

    Calculate the sum of digits in each model's ID (model column) from Product table. Result set: model, sum of digits

    请告诉我如何解决它.我是中级sql技能，无法解决这个问题.

    Please tell me how to solve it. I am of intermediate sql skill and cant solve this.

    推荐答案

    好的，借助两个函数，我们可以将您的型号解析为数字，然后得到数字的总和.

    OK, with the help of two functions, we can parse your model numbers into digits and then get the sum of digits.

    Select [dbo].[udf-Stat-Sum-of-Digits](12345)     -- Returns 15
    Select [dbo].[dbo].[udf-Str-Numbers]('AF567-56') -- Returns 56756
    

    好消息是我们可以将这些组合起来，如下图所示

    The good news is that we can combine these as illustrated below

    Declare @Table table (model varchar(50))
    Insert into @Table values
    ('AF567-56'),
    ('25-a-467'),
    ('11156 25')
    
    Select Model
          ,Digits = [dbo].[udf-Str-Numbers](Model)
          ,SumOfDigits = [dbo].[udf-Stat-Sum-of-Digits]([dbo].[udf-Str-Numbers](Model))
     From  @Table
    

    退货

    Model       Digits  SumOfDigits
    AF567-56    56756   29
    25-a-467    25467   24
    11156 25    1115625 21
    

    两个 UDF

    CREATE Function [dbo].[udf-Stat-Sum-of-Digits](@Val int)
    Returns Int
    As
    Begin
    
    Declare @RetVal as int
    
    ;with i AS (
        Select @Val / 10 n, @Val % 10 d
        Union ALL
        Select n / 10, n % 10
        From i
        Where n > 0
    )
    Select @RetVal = SUM(d) FROM i;
    
    Return @RetVal
    
    END
    

    第二个函数

    CREATE FUNCTION [dbo].[udf-Str-Numbers](@String varchar(250))
    Returns Varchar(250)
    As
    Begin
        Declare @RetVal varchar(250) = @String
        ;with cteChar as (Select Cnt=1,Str=Char(1) Union All Select Cnt=B.Cnt+1,Str=Char(B.Cnt+1) From cteChar as B Where B.Cnt <= 255)
        Select @RetVal = Replace(@RetVal,Str,'') From cteChar where str not like '[0-9]' Option (maxrecursion 256)
        Return case when IsNull(@RetVal,'')='' then @String else @RetVal end
    END
    

    这篇关于sql中的数字总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！