任务描述

1. 问题描述分析

erp web 日志记录 执行sql错误，一天100多次错误，数据转换时出现溢出错误 


2 .解决方案

 将 Score DECIMAL(16, 14) NOT NULL 范围改大一点：Score DECIMAL(18, 14) NOT NULL, 执行成功，1秒出结果

3. 错误sql

DECLARE @AlikeResultTable TABLE
  (
     SKU   VARCHAR(50) NOT NULL,
     Score DECIMAL(16, 14) NOT NULL
  );

INSERT INTO @AlikeResultTable
            (SKU,
             Score)
VALUES     ('14001849',
            119.385635375977),
            ('7129380',
             6.07243728637695),
            ('5939285',
             5.90719270706177),
            ('9688237',
             5.47023868560791),
            ('9688284',
             5.47023868560791),
            ('11029430',
             5.46894073486328),
            ('11029669',
             5.46894073486328),
            ('11029097',
             5.46894073486328),
            ('11027761',
             5.46894073486328),
            ('11305338',
             5.4216947555542),
            ('6470545',
             5.41932678222656),
            ('10647278',
             5.05120420455933),
            ('6587087',
             5.04551601409912),
            ('5883071',
             5.01710844039917),
            ('12176472',
             4.93030500411987),
            ('6587295',
             4.85857105255127),
            ('13251741',
             4.84732818603516),
            ('6571671',
             4.84063625335693),
            ('7131492',
             4.7950177192688),
            ('7131538',
             4.7950177192688),
            ('8949879',
             4.78534364700317),
            ('6908527',
             4.78420782089233),
            ('3989909',
             4.73950242996216),
            ('7461708',
             4.65147829055786),
            ('6915398',
             4.63383769989014),
            ('5404644',
             4.61791753768921),
            ('6299772',
             4.61118412017822),
            ('4004788',
             4.57469463348389),
            ('6524358',
             4.51312303543091),
            ('14029159',
             4.50455236434937),
            ('11198597',
             4.48838186264038),
            ('10930835',
             4.46613550186157),
            ('14029439',
             4.46613550186157),
            ('10931025',
             4.379310131073),
            ('6568990',
             4.36816167831421),
            ('3873885',
             4.35601043701172),
            ('4976583',
             4.3469705581665),
            ('6587086',
             4.3340048789978),
            ('2873811',
             4.24939393997192),
            ('6564885',
             4.11433076858521);

SELECT a.Id,
       a.SKU,
       a.ProductProperty,
       a.ImageUrl,
       a.ProductGroupSKU,
       a.ProductState,
       b.SupplierPrice,
       c.DevelopAdminName,
       c.DevelopTime,
       c.OnlineTime,
       b.WebProductUrl,
       b.SupplierID
FROM   dbo.RB_Product a
       INNER JOIN @AlikeResultTable l
               ON a.SKU = l.SKU
       LEFT JOIN dbo.RB_Product_SupplierPrice b
              ON a.CustomerID = b.CustomerID
                 AND a.SKU = b.SKU
                 AND b.IsDefault = 1
       LEFT JOIN dbo.RB_Product_Admin c
              ON a.CustomerID = c.CustomerID
                 AND a.SKU = c.SKU
WHERE  a.CustomerID = 1
ORDER  BY l.Score DESC

--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.

历史记录  
  1. 2022-05-19 15:57:40, 由 陈少军 创建。
  2. 2022-05-19 15:58:26, 由 陈少军 编辑。
    修改了 任务名称，区别为：
    001- 【SQL 错误】
    001+ 【SQL 数据类型范围错误】数据转换时出现溢出错误
    001- 【SQL 错误】
    001+ 【SQL 数据类型范围错误】数据转换时出现溢出错误
    修改了 任务描述，区别为：
    006-
    006+ 3. 错误sql
    007- 3. 错误sql
    007+ DECLARE @AlikeResultTable TABLE ( SKU VARCHAR(50) NOT NULL, Score DECIMAL(18, 14) NOT NULL );INSERT INTO @AlikeResultTable (SKU, Score)VALUES ('14001849', 119.385635375977), ('7129380', 6.07243728637695), ('5939285', 5.90719270706177), ('9688237', 5.47023868560791), ('9688284', 5.47023868560791), ('11029430', 5.46894073486328), ('11029669', 5.46894073486328), ('11029097', 5.46894073486328), ('11027761', 5.46894073486328), ('11305338', 5.4216947555542), ('6470545', 5.41932678222656), ('10647278', 5.05120420455933), ('6587087', 5.04551601409912), ('5883071', 5.01710844039917), ('12176472', 4.93030500411987), ('6587295', 4.85857105255127), ('13251741', 4.84732818603516), ('6571671', 4.84063625335693), ('7131492', 4.7950177192688), ('7131538', 4.7950177192688), ('8949879', 4.78534364700317), ('6908527', 4.78420782089233), ('3989909', 4.73950242996216), ('7461708', 4.65147829055786), ('6915398', 4.63383769989014), ('5404644', 4.61791753768921), ('6299772', 4.61118412017822), ('4004788', 4.57469463348389), ('6524358', 4.51312303543091), ('14029159', 4.50455236434937), ('11198597', 4.48838186264038), ('10930835', 4.46613550186157), ('14029439', 4.46613550186157), ('10931025', 4.379310131073), ('6568990', 4.36816167831421), ('3873885', 4.35601043701172), ('4976583', 4.3469705581665), ('6587086', 4.3340048789978), ('2873811', 4.24939393997192), ('6564885', 4.11433076858521);SELECT a.Id, a.SKU, a.ProductProperty, a.ImageUrl, a.ProductGroupSKU, a.ProductState, b.SupplierPrice, c.DevelopAdminName, c.DevelopTime, c.OnlineTime, b.WebProductUrl, b.SupplierIDFROM dbo.RB_Product a INNER JOIN @AlikeResultTable l ON a.SKU = l.SKU LEFT JOIN dbo.RB_Product_SupplierPrice b ON a.CustomerID = b.CustomerID AND a.SKU = b.SKU AND b.IsDefault = 1 LEFT JOIN dbo.RB_Product_Admin c ON a.CustomerID = c.CustomerID AND a.SKU = c.SKUWHERE a.CustomerID = 1ORDER BY l.Score DESC--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.
    008- DECLARE @AlikeResultTable TABLE ( SKU VARCHAR(50) NOT NULL, Score DECIMAL(18, 14) NOT NULL );INSERT INTO @AlikeResultTable (SKU, Score)VALUES ('14001849', 119.385635375977), ('7129380', 6.07243728637695), ('5939285', 5.90719270706177), ('9688237', 5.47023868560791), ('9688284', 5.47023868560791), ('11029430', 5.46894073486328), ('11029669', 5.46894073486328), ('11029097', 5.46894073486328), ('11027761', 5.46894073486328), ('11305338', 5.4216947555542), ('6470545', 5.41932678222656), ('10647278', 5.05120420455933), ('6587087', 5.04551601409912), ('5883071', 5.01710844039917), ('12176472', 4.93030500411987), ('6587295', 4.85857105255127), ('13251741', 4.84732818603516), ('6571671', 4.84063625335693), ('7131492', 4.7950177192688), ('7131538', 4.7950177192688), ('8949879', 4.78534364700317), ('6908527', 4.78420782089233), ('3989909', 4.73950242996216), ('7461708', 4.65147829055786), ('6915398', 4.63383769989014), ('5404644', 4.61791753768921), ('6299772', 4.61118412017822), ('4004788', 4.57469463348389), ('6524358', 4.51312303543091), ('14029159', 4.50455236434937), ('11198597', 4.48838186264038), ('10930835', 4.46613550186157), ('14029439', 4.46613550186157), ('10931025', 4.379310131073), ('6568990', 4.36816167831421), ('3873885', 4.35601043701172), ('4976583', 4.3469705581665), ('6587086', 4.3340048789978), ('2873811', 4.24939393997192), ('6564885', 4.11433076858521);SELECT a.Id, a.SKU, a.ProductProperty, a.ImageUrl, a.ProductGroupSKU, a.ProductState, b.SupplierPrice, c.DevelopAdminName, c.DevelopTime, c.OnlineTime, b.WebProductUrl, b.SupplierIDFROM dbo.RB_Product a INNER JOIN @AlikeResultTable l ON a.SKU = l.SKU LEFT JOIN dbo.RB_Product_SupplierPrice b ON a.CustomerID = b.CustomerID AND a.SKU = b.SKU AND b.IsDefault = 1 LEFT JOIN dbo.RB_Product_Admin c ON a.CustomerID = c.CustomerID AND a.SKU = c.SKUWHERE a.CustomerID = 1ORDER BY l.Score DESC--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.
    006- <p></p>
    006+ <p>3. 错误sql</p>
    007- <p>3. 错误sql</p>
    007+ <p>DECLARE @AlikeResultTable TABLE<br /> (<br /> SKU VARCHAR(50) NOT NULL,<br /> Score DECIMAL(18, 14) NOT NULL<br /> );<br /><br />INSERT INTO @AlikeResultTable<br /> (SKU,<br /> Score)<br />VALUES ('14001849',<br /> 119.385635375977),<br /> ('7129380',<br /> 6.07243728637695),<br /> ('5939285',<br /> 5.90719270706177),<br /> ('9688237',<br /> 5.47023868560791),<br /> ('9688284',<br /> 5.47023868560791),<br /> ('11029430',<br /> 5.46894073486328),<br /> ('11029669',<br /> 5.46894073486328),<br /> ('11029097',<br /> 5.46894073486328),<br /> ('11027761',<br /> 5.46894073486328),<br /> ('11305338',<br /> 5.4216947555542),<br /> ('6470545',<br /> 5.41932678222656),<br /> ('10647278',<br /> 5.05120420455933),<br /> ('6587087',<br /> 5.04551601409912),<br /> ('5883071',<br /> 5.01710844039917),<br /> ('12176472',<br /> 4.93030500411987),<br /> ('6587295',<br /> 4.85857105255127),<br /> ('13251741',<br /> 4.84732818603516),<br /> ('6571671',<br /> 4.84063625335693),<br /> ('7131492',<br /> 4.7950177192688),<br /> ('7131538',<br /> 4.7950177192688),<br /> ('8949879',<br /> 4.78534364700317),<br /> ('6908527',<br /> 4.78420782089233),<br /> ('3989909',<br /> 4.73950242996216),<br /> ('7461708',<br /> 4.65147829055786),<br /> ('6915398',<br /> 4.63383769989014),<br /> ('5404644',<br /> 4.61791753768921),<br /> ('6299772',<br /> 4.61118412017822),<br /> ('4004788',<br /> 4.57469463348389),<br /> ('6524358',<br /> 4.51312303543091),<br /> ('14029159',<br /> 4.50455236434937),<br /> ('11198597',<br /> 4.48838186264038),<br /> ('10930835',<br /> 4.46613550186157),<br /> ('14029439',<br /> 4.46613550186157),<br /> ('10931025',<br /> 4.379310131073),<br /> ('6568990',<br /> 4.36816167831421),<br /> ('3873885',<br /> 4.35601043701172),<br /> ('4976583',<br /> 4.3469705581665),<br /> ('6587086',<br /> 4.3340048789978),<br /> ('2873811',<br /> 4.24939393997192),<br /> ('6564885',<br /> 4.11433076858521);<br /><br />SELECT a.Id,<br /> a.SKU,<br /> a.ProductProperty,<br /> a.ImageUrl,<br /> a.ProductGroupSKU,<br /> a.ProductState,<br /> b.SupplierPrice,<br /> c.DevelopAdminName,<br /> c.DevelopTime,<br /> c.OnlineTime,<br /> b.WebProductUrl,<br /> b.SupplierID<br />FROM dbo.RB_Product a<br /> INNER JOIN @AlikeResultTable l<br /> ON a.SKU = l.SKU<br /> LEFT JOIN dbo.RB_Product_SupplierPrice b<br /> ON a.CustomerID = b.CustomerID<br /> AND a.SKU = b.SKU<br /> AND b.IsDefault = 1<br /> LEFT JOIN dbo.RB_Product_Admin c<br /> ON a.CustomerID = c.CustomerID<br /> AND a.SKU = c.SKU<br />WHERE a.CustomerID = 1<br />ORDER BY l.Score DESC<br /><br />--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.</p>
    008- <p>DECLARE @AlikeResultTable TABLE<br /> (<br /> SKU VARCHAR(50) NOT NULL,<br /> Score DECIMAL(18, 14) NOT NULL<br /> );<br /><br />INSERT INTO @AlikeResultTable<br /> (SKU,<br /> Score)<br />VALUES ('14001849',<br /> 119.385635375977),<br /> ('7129380',<br /> 6.07243728637695),<br /> ('5939285',<br /> 5.90719270706177),<br /> ('9688237',<br /> 5.47023868560791),<br /> ('9688284',<br /> 5.47023868560791),<br /> ('11029430',<br /> 5.46894073486328),<br /> ('11029669',<br /> 5.46894073486328),<br /> ('11029097',<br /> 5.46894073486328),<br /> ('11027761',<br /> 5.46894073486328),<br /> ('11305338',<br /> 5.4216947555542),<br /> ('6470545',<br /> 5.41932678222656),<br /> ('10647278',<br /> 5.05120420455933),<br /> ('6587087',<br /> 5.04551601409912),<br /> ('5883071',<br /> 5.01710844039917),<br /> ('12176472',<br /> 4.93030500411987),<br /> ('6587295',<br /> 4.85857105255127),<br /> ('13251741',<br /> 4.84732818603516),<br /> ('6571671',<br /> 4.84063625335693),<br /> ('7131492',<br /> 4.7950177192688),<br /> ('7131538',<br /> 4.7950177192688),<br /> ('8949879',<br /> 4.78534364700317),<br /> ('6908527',<br /> 4.78420782089233),<br /> ('3989909',<br /> 4.73950242996216),<br /> ('7461708',<br /> 4.65147829055786),<br /> ('6915398',<br /> 4.63383769989014),<br /> ('5404644',<br /> 4.61791753768921),<br /> ('6299772',<br /> 4.61118412017822),<br /> ('4004788',<br /> 4.57469463348389),<br /> ('6524358',<br /> 4.51312303543091),<br /> ('14029159',<br /> 4.50455236434937),<br /> ('11198597',<br /> 4.48838186264038),<br /> ('10930835',<br /> 4.46613550186157),<br /> ('14029439',<br /> 4.46613550186157),<br /> ('10931025',<br /> 4.379310131073),<br /> ('6568990',<br /> 4.36816167831421),<br /> ('3873885',<br /> 4.35601043701172),<br /> ('4976583',<br /> 4.3469705581665),<br /> ('6587086',<br /> 4.3340048789978),<br /> ('2873811',<br /> 4.24939393997192),<br /> ('6564885',<br /> 4.11433076858521);<br /><br />SELECT a.Id,<br /> a.SKU,<br /> a.ProductProperty,<br /> a.ImageUrl,<br /> a.ProductGroupSKU,<br /> a.ProductState,<br /> b.SupplierPrice,<br /> c.DevelopAdminName,<br /> c.DevelopTime,<br /> c.OnlineTime,<br /> b.WebProductUrl,<br /> b.SupplierID<br />FROM dbo.RB_Product a<br /> INNER JOIN @AlikeResultTable l<br /> ON a.SKU = l.SKU<br /> LEFT JOIN dbo.RB_Product_SupplierPrice b<br /> ON a.CustomerID = b.CustomerID<br /> AND a.SKU = b.SKU<br /> AND b.IsDefault = 1<br /> LEFT JOIN dbo.RB_Product_Admin c<br /> ON a.CustomerID = c.CustomerID<br /> AND a.SKU = c.SKU<br />WHERE a.CustomerID = 1<br />ORDER BY l.Score DESC<br /><br />--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.<br /></p>
  3. 2022-05-19 15:58:41, 由 陈少军 指派给 何建宝。
    修改了 预计剩余，旧值为 "0"，新值为 "2"。


    参考解决方案 修改

  4. 2022-05-21 17:49:28, 由 何建宝 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "2"。
    修改了 指派给 ，旧值为 "hejianbao"，新值为 "chenshaojun"。
    修改了 实际完成，旧值为 ""，新值为 "2022-05-21 17:48:44"。
    修改了 任务状态，旧值为 "wait"，新值为 "done"。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-05-21 17:49:28"。
    修改了 预计剩余，旧值为 "2"，新值为 "0"。
    修改了 由谁完成，旧值为 ""，新值为 "hejianbao"。

    处理方案：

     将 Score DECIMAL(16, 14) NOT NULL 范围改大一点：Score DECIMAL(18, 14) NOT NULL

    代码已调整，并且已发布

  5. 2022-05-23 15:46:42, 由 陈少军 编辑。
    修改了 任务描述，区别为：
    007- DECLARE @AlikeResultTable TABLE ( SKU VARCHAR(50) NOT NULL, Score DECIMAL(18, 14) NOT NULL );INSERT INTO @AlikeResultTable (SKU, Score)VALUES ('14001849', 119.385635375977), ('7129380', 6.07243728637695), ('5939285', 5.90719270706177), ('9688237', 5.47023868560791), ('9688284', 5.47023868560791), ('11029430', 5.46894073486328), ('11029669', 5.46894073486328), ('11029097', 5.46894073486328), ('11027761', 5.46894073486328), ('11305338', 5.4216947555542), ('6470545', 5.41932678222656), ('10647278', 5.05120420455933), ('6587087', 5.04551601409912), ('5883071', 5.01710844039917), ('12176472', 4.93030500411987), ('6587295', 4.85857105255127), ('13251741', 4.84732818603516), ('6571671', 4.84063625335693), ('7131492', 4.7950177192688), ('7131538', 4.7950177192688), ('8949879', 4.78534364700317), ('6908527', 4.78420782089233), ('3989909', 4.73950242996216), ('7461708', 4.65147829055786), ('6915398', 4.63383769989014), ('5404644', 4.61791753768921), ('6299772', 4.61118412017822), ('4004788', 4.57469463348389), ('6524358', 4.51312303543091), ('14029159', 4.50455236434937), ('11198597', 4.48838186264038), ('10930835', 4.46613550186157), ('14029439', 4.46613550186157), ('10931025', 4.379310131073), ('6568990', 4.36816167831421), ('3873885', 4.35601043701172), ('4976583', 4.3469705581665), ('6587086', 4.3340048789978), ('2873811', 4.24939393997192), ('6564885', 4.11433076858521);SELECT a.Id, a.SKU, a.ProductProperty, a.ImageUrl, a.ProductGroupSKU, a.ProductState, b.SupplierPrice, c.DevelopAdminName, c.DevelopTime, c.OnlineTime, b.WebProductUrl, b.SupplierIDFROM dbo.RB_Product a INNER JOIN @AlikeResultTable l ON a.SKU = l.SKU LEFT JOIN dbo.RB_Product_SupplierPrice b ON a.CustomerID = b.CustomerID AND a.SKU = b.SKU AND b.IsDefault = 1 LEFT JOIN dbo.RB_Product_Admin c ON a.CustomerID = c.CustomerID AND a.SKU = c.SKUWHERE a.CustomerID = 1ORDER BY l.Score DESC--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.
    007+ DECLARE @AlikeResultTable TABLE ( SKU VARCHAR(50) NOT NULL, Score DECIMAL(16, 14) NOT NULL );INSERT INTO @AlikeResultTable (SKU, Score)VALUES ('14001849', 119.385635375977), ('7129380', 6.07243728637695), ('5939285', 5.90719270706177), ('9688237', 5.47023868560791), ('9688284', 5.47023868560791), ('11029430', 5.46894073486328), ('11029669', 5.46894073486328), ('11029097', 5.46894073486328), ('11027761', 5.46894073486328), ('11305338', 5.4216947555542), ('6470545', 5.41932678222656), ('10647278', 5.05120420455933), ('6587087', 5.04551601409912), ('5883071', 5.01710844039917), ('12176472', 4.93030500411987), ('6587295', 4.85857105255127), ('13251741', 4.84732818603516), ('6571671', 4.84063625335693), ('7131492', 4.7950177192688), ('7131538', 4.7950177192688), ('8949879', 4.78534364700317), ('6908527', 4.78420782089233), ('3989909', 4.73950242996216), ('7461708', 4.65147829055786), ('6915398', 4.63383769989014), ('5404644', 4.61791753768921), ('6299772', 4.61118412017822), ('4004788', 4.57469463348389), ('6524358', 4.51312303543091), ('14029159', 4.50455236434937), ('11198597', 4.48838186264038), ('10930835', 4.46613550186157), ('14029439', 4.46613550186157), ('10931025', 4.379310131073), ('6568990', 4.36816167831421), ('3873885', 4.35601043701172), ('4976583', 4.3469705581665), ('6587086', 4.3340048789978), ('2873811', 4.24939393997192), ('6564885', 4.11433076858521);SELECT a.Id, a.SKU, a.ProductProperty, a.ImageUrl, a.ProductGroupSKU, a.ProductState, b.SupplierPrice, c.DevelopAdminName, c.DevelopTime, c.OnlineTime, b.WebProductUrl, b.SupplierIDFROM dbo.RB_Product a INNER JOIN @AlikeResultTable l ON a.SKU = l.SKU LEFT JOIN dbo.RB_Product_SupplierPrice b ON a.CustomerID = b.CustomerID AND a.SKU = b.SKU AND b.IsDefault = 1 LEFT JOIN dbo.RB_Product_Admin c ON a.CustomerID = c.CustomerID AND a.SKU = c.SKUWHERE a.CustomerID = 1ORDER BY l.Score DESC--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.
    007- <p>DECLARE @AlikeResultTable TABLE<br /> (<br /> SKU VARCHAR(50) NOT NULL,<br /> Score DECIMAL(18, 14) NOT NULL<br /> );<br /><br />INSERT INTO @AlikeResultTable<br /> (SKU,<br /> Score)<br />VALUES ('14001849',<br /> 119.385635375977),<br /> ('7129380',<br /> 6.07243728637695),<br /> ('5939285',<br /> 5.90719270706177),<br /> ('9688237',<br /> 5.47023868560791),<br /> ('9688284',<br /> 5.47023868560791),<br /> ('11029430',<br /> 5.46894073486328),<br /> ('11029669',<br /> 5.46894073486328),<br /> ('11029097',<br /> 5.46894073486328),<br /> ('11027761',<br /> 5.46894073486328),<br /> ('11305338',<br /> 5.4216947555542),<br /> ('6470545',<br /> 5.41932678222656),<br /> ('10647278',<br /> 5.05120420455933),<br /> ('6587087',<br /> 5.04551601409912),<br /> ('5883071',<br /> 5.01710844039917),<br /> ('12176472',<br /> 4.93030500411987),<br /> ('6587295',<br /> 4.85857105255127),<br /> ('13251741',<br /> 4.84732818603516),<br /> ('6571671',<br /> 4.84063625335693),<br /> ('7131492',<br /> 4.7950177192688),<br /> ('7131538',<br /> 4.7950177192688),<br /> ('8949879',<br /> 4.78534364700317),<br /> ('6908527',<br /> 4.78420782089233),<br /> ('3989909',<br /> 4.73950242996216),<br /> ('7461708',<br /> 4.65147829055786),<br /> ('6915398',<br /> 4.63383769989014),<br /> ('5404644',<br /> 4.61791753768921),<br /> ('6299772',<br /> 4.61118412017822),<br /> ('4004788',<br /> 4.57469463348389),<br /> ('6524358',<br /> 4.51312303543091),<br /> ('14029159',<br /> 4.50455236434937),<br /> ('11198597',<br /> 4.48838186264038),<br /> ('10930835',<br /> 4.46613550186157),<br /> ('14029439',<br /> 4.46613550186157),<br /> ('10931025',<br /> 4.379310131073),<br /> ('6568990',<br /> 4.36816167831421),<br /> ('3873885',<br /> 4.35601043701172),<br /> ('4976583',<br /> 4.3469705581665),<br /> ('6587086',<br /> 4.3340048789978),<br /> ('2873811',<br /> 4.24939393997192),<br /> ('6564885',<br /> 4.11433076858521);<br /><br />SELECT a.Id,<br /> a.SKU,<br /> a.ProductProperty,<br /> a.ImageUrl,<br /> a.ProductGroupSKU,<br /> a.ProductState,<br /> b.SupplierPrice,<br /> c.DevelopAdminName,<br /> c.DevelopTime,<br /> c.OnlineTime,<br /> b.WebProductUrl,<br /> b.SupplierID<br />FROM dbo.RB_Product a<br /> INNER JOIN @AlikeResultTable l<br /> ON a.SKU = l.SKU<br /> LEFT JOIN dbo.RB_Product_SupplierPrice b<br /> ON a.CustomerID = b.CustomerID<br /> AND a.SKU = b.SKU<br /> AND b.IsDefault = 1<br /> LEFT JOIN dbo.RB_Product_Admin c<br /> ON a.CustomerID = c.CustomerID<br /> AND a.SKU = c.SKU<br />WHERE a.CustomerID = 1<br />ORDER BY l.Score DESC<br /><br />--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.</p>
    007+ <p>DECLARE @AlikeResultTable TABLE<br /> (<br /> SKU VARCHAR(50) NOT NULL,<br /> Score DECIMAL(16, 14) NOT NULL<br /> );<br /><br />INSERT INTO @AlikeResultTable<br /> (SKU,<br /> Score)<br />VALUES ('14001849',<br /> 119.385635375977),<br /> ('7129380',<br /> 6.07243728637695),<br /> ('5939285',<br /> 5.90719270706177),<br /> ('9688237',<br /> 5.47023868560791),<br /> ('9688284',<br /> 5.47023868560791),<br /> ('11029430',<br /> 5.46894073486328),<br /> ('11029669',<br /> 5.46894073486328),<br /> ('11029097',<br /> 5.46894073486328),<br /> ('11027761',<br /> 5.46894073486328),<br /> ('11305338',<br /> 5.4216947555542),<br /> ('6470545',<br /> 5.41932678222656),<br /> ('10647278',<br /> 5.05120420455933),<br /> ('6587087',<br /> 5.04551601409912),<br /> ('5883071',<br /> 5.01710844039917),<br /> ('12176472',<br /> 4.93030500411987),<br /> ('6587295',<br /> 4.85857105255127),<br /> ('13251741',<br /> 4.84732818603516),<br /> ('6571671',<br /> 4.84063625335693),<br /> ('7131492',<br /> 4.7950177192688),<br /> ('7131538',<br /> 4.7950177192688),<br /> ('8949879',<br /> 4.78534364700317),<br /> ('6908527',<br /> 4.78420782089233),<br /> ('3989909',<br /> 4.73950242996216),<br /> ('7461708',<br /> 4.65147829055786),<br /> ('6915398',<br /> 4.63383769989014),<br /> ('5404644',<br /> 4.61791753768921),<br /> ('6299772',<br /> 4.61118412017822),<br /> ('4004788',<br /> 4.57469463348389),<br /> ('6524358',<br /> 4.51312303543091),<br /> ('14029159',<br /> 4.50455236434937),<br /> ('11198597',<br /> 4.48838186264038),<br /> ('10930835',<br /> 4.46613550186157),<br /> ('14029439',<br /> 4.46613550186157),<br /> ('10931025',<br /> 4.379310131073),<br /> ('6568990',<br /> 4.36816167831421),<br /> ('3873885',<br /> 4.35601043701172),<br /> ('4976583',<br /> 4.3469705581665),<br /> ('6587086',<br /> 4.3340048789978),<br /> ('2873811',<br /> 4.24939393997192),<br /> ('6564885',<br /> 4.11433076858521);<br /><br />SELECT a.Id,<br /> a.SKU,<br /> a.ProductProperty,<br /> a.ImageUrl,<br /> a.ProductGroupSKU,<br /> a.ProductState,<br /> b.SupplierPrice,<br /> c.DevelopAdminName,<br /> c.DevelopTime,<br /> c.OnlineTime,<br /> b.WebProductUrl,<br /> b.SupplierID<br />FROM dbo.RB_Product a<br /> INNER JOIN @AlikeResultTable l<br /> ON a.SKU = l.SKU<br /> LEFT JOIN dbo.RB_Product_SupplierPrice b<br /> ON a.CustomerID = b.CustomerID<br /> AND a.SKU = b.SKU<br /> AND b.IsDefault = 1<br /> LEFT JOIN dbo.RB_Product_Admin c<br /> ON a.CustomerID = c.CustomerID<br /> AND a.SKU = c.SKU<br />WHERE a.CustomerID = 1<br />ORDER BY l.Score DESC<br /><br />--PARAMS:@CustomerID = 1 ,MESSAGE:Arithmetic overflow error converting numeric TO data type numeric.</p>
  6. 2022-07-16 17:09:18, 由 zhengqiao 关闭。
    修改了 任务状态，旧值为 "done"，新值为 "closed"。
    修改了 指派给 ，旧值为 "chenshaojun"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "zhengqiao"。
    修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2022-07-16 17:09:18"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
返回
所属迭代 系统管理部 - 系统服务优化
所属模块 /
相关需求 暂无
指派给 Closed 于 2022-07-16 17:09:18
任务类型 后端
任务标签 初始
开发组
任务状态 已关闭
进度 100%
优先级 P2
关键词
抄送给
由谁创建 陈少军 于 2022-05-19 15:57:40
由谁完成 何建宝 于 2022-05-21 17:48:44
由谁取消 暂无
由谁关闭 zhengqiao 于 2022-07-16 17:09:18
关闭原因 已完成
最后编辑 zhengqiao 于 2022-07-16 17:09:18
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 0工时
总计消耗 2工时
预计剩余 0工时
预计开始 2022-05-19
实际开始 2022-05-21
截止日期 2022-05-26