sql中的数字总和

本文介绍了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