mysql - 如何使用选择值表从列值中动态选择后缀值

Question

主要问题:我无法将两个 MySQL 语句重新创建到 MS Sql 中 - 因为例如 MS SQL 不会将像“1g”这样的值转换为整数值 1，尽管 MySQL 似乎与我创建的 MySql 语句一起使用。

我的情况:我有一个表中的数据列，其中包含数字 + 后缀字符值的组合。我有另一个关于允许的后缀值的信息表。我正在努力完成这些事情:

1. 选择包含特定后缀值和相关数据字段值的 primary_data 行(使用外部表和 where 子句)
2. 将清理后的数字值显示为整数(清理后缀)以进行排序
3. 以每行 10 列的形式返回 26 到 1000 之间“缺失”数字的列表(每列代表每个数字 0-9，数字 1-25 始终不可用)

4. 已经使用的号码(“不可用”)用波浪号“~”表示，那些“可用”的号码在各自的列中显示为数字

   -上面的第 3 项和第 4 项在这里没有问题，但正是我需要第 1 项和第 2 项的原因

显式数据限制:

• 任何数字(数字部分)都不会超出(1-999)的范围
• 允许的后缀值都包含在外部表中

我尝试过的:

我有一个可以按我想要的方式工作的 MySQL 版本，但我需要将它转换为 MS Sql。当我尝试手动将 MySQL 语句转换为 MS Sql 时，出现无法转换为 int 错误(即，将“1g”转换为 int)。我有每个数据表的 MS SQL 实例，如下所示作为 MySQL 模式，但由于数据发生转换错误，我无法弄清楚如何将 MySQL 语句重新创建到 MS SQL 中。

重要的问题是 [primary_data].[No] 字段包含不同的长度后缀值(所以我不能只看最后一个结束字符。)

我在找什么:

我真正想要的是一种简单的方法，可以使用在另一个表中找到的值(即 [suffix_values].[allowedvalue)从表(即 [primary_data].[No])中以“类似”方式选择列]).或者，我想要一些关于如何将 MySQL View 和结果查询重新创建为有效的 MS Sql 语句的指示。

---

MySQL 良好结果示例 - 显示数字 20-99 的剪辑 (csv):

Ten's,0,1,2,3,4,5,6,7,8,9
20's,~,~,~,~,~,~,~,~,28,~
30's,~,~,~,33,34,~,~,~,~,39
40's,~,~,~,43,~,~,46,47,~,49
50's,50,51,~,~,~,~,~,~,58,59
60's,60,61,62,63,~,~,~,67,68,69
70's,~,71,~,~,~,~,76,~,78,79
80's,80,81,82,83,84,85,86,87,~,~
90's,90,91,92,~,~,95,~,97,98,99

示例数据:primary_data(模拟 csv 数据)

Chapter,No,Additional1,Additional2,Additional8
South,96,,Name 1,2014
South,75od,One Day,Name 2,2014
South,88ee,South,Name 3,2014
South,77g,South,Name 4,2014
South,32g,South,Name 5,2014
South,34od,One Day,Name 6,2014
South,51ot,Old Timers,Name 7,2014
South,35e,South,Name 8,2014
South,42g,South,Name 9,2014

注意上面的示例行，预期结果是包含“g”、“e”、“ee”的行将在其各自的数字桶中产生波浪号。所有其他数字将显示在各自的桶中。

示例数据:后缀值: (csv)

Id,Chapter,Suffix,IsEnabled
1,South,g,1
2,Sierra,s,1
3,Bay,b,1
4,Reno,r,1
5,Valley,v,1
6,One Day,od,0
7,1st Eval,e,1
8,2nd Eval,ee,1
9,Old Timers,ot,0

---

表的 MySql 架构:primary_data

CREATE TABLE `primary_data` (
  `Chapter` varchar(50) NOT NULL,
  `No` varchar(50) NOT NULL,
  `Additional1` varchar(50) DEFAULT NULL,
  `Additional2` varchar(50) DEFAULT NULL,
  `Additional8` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表的 MySql 架构:suffix_values

CREATE TABLE `suffix_values` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Chapter` varchar(50) DEFAULT '',
  `allowed_suffix` char(5) NOT NULL DEFAULT '',
  `IsEnabled` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

表的 MySql 架构:allowednumbers(值 1 到 999)

CREATE TABLE `allowednumbers` (
  `Number` int(11) NOT NULL,
  PRIMARY KEY (`Number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

用于 View 的 MySql 架构:vnumbers(需要在 MS Sql 中重新创建)

SELECT  `primary_data`.`Chapter` AS `Owner`,
    `primary_data`.`Additional1` AS `Chapter`,
    `primary_data`.`Additional8` AS `ThisYear`,
    CAST(`primary_data`.`No` AS UNSIGNED) AS `scrubbed_number`
FROM    `primary_data`
WHERE   (
        (`primary_data`.`Additional8` <> '')
        AND `primary_data`.`Additional1` IN (   
        SELECT `suffix_values`.`Chapter`
        FROM    `suffix_values`
        WHERE ((`suffix_values`.`isenabled` = 1)
        AND (`suffix_values`.`allowed_suffix` NOT IN ('e' , 'ee'))
        )
    )
)    
ORDER BY `primary_data`.`Additional1` , CAST(`primary_data`.`No` AS UNSIGNED)

MySql for result query statement(我在这篇文章的 where 子句中放置了常量值，还需要在 MS Sql 中重新创建)

Select Tens,
CASE col0
 WHEN 0 Then '~'
 ELSE col0
 END '0',
CASE col1
 WHEN 0 Then '~'
 ELSE col1
 END '1',
CASE col2
 WHEN 0 Then '~'
 ELSE col2
 END '2',
CASE col3
 WHEN 0 Then '~'
 ELSE col3
 END '3',
CASE col4
 WHEN 0 Then '~'
 ELSE col4
 END '4',
CASE col5
 WHEN 0 Then '~'
 ELSE col5
 END '5',  
CASE col6
 WHEN 0 Then '~'
 ELSE col6
 END '6',
CASE col7
 WHEN 0 Then '~'
 ELSE col7
 END '7',
CASE col8
 WHEN 0 Then '~'
 ELSE col8
 END '8',
CASE col9
 WHEN 0 Then '~'
 ELSE col9
 END '9' 
From (
    Select Concat('',x.row,'0''s') as Tens,  
    sum(x.Col0) 'col0', 
    sum(x.Col1) 'col1', 
    sum(x.Col2) 'col2', 
    sum(x.Col3) 'col3', 
    sum(x.Col4) 'col4', 
    sum(x.Col5) 'col5', 
    sum(x.Col6) 'col6', 
    sum(x.Col7) 'col7', 
    sum(x.Col8) 'col8', 
    sum(x.Col9) 'col9' 
From (
Select Left(cast(a.Number as char(3)),Length(cast(a.Number as char(3)))-1) 'Row',  
CASE  Right(cast(a.Number as char(3)),1)
WHEN '0' then a.number
 Else 0
END 'Col0',
CASE  Right(cast(a.Number as char(3)),1)
WHEN '1' then a.number
 Else 0
END 'Col1',
CASE  Right(cast(a.Number as char(3)),1)
WHEN '2' then a.number 
 Else 0
END 'Col2',
CASE  Right(cast(a.Number as char(3)),1)
WHEN '3' then a.number 
 Else 0
END 'Col3',
CASE  Right(cast(a.Number as char(3)),1)
WHEN '4' then a.number 
 Else 0
END 'Col4',
CASE  Right(cast(a.Number as char(3)),1)
WHEN '5' then a.number 
 Else 0
END 'Col5',
CASE  Right(cast(a.Number as char(3)),1)
WHEN '6' then a.number 
 Else 0
END 'Col6',
CASE  Right(cast(a.Number as char(3)),1)
WHEN '7' then a.number 
 Else 0
END 'Col7',
CASE  Right(cast(a.Number as char(3)),1)  
WHEN '8' then a.number 
 Else 0
END 'Col8',
CASE  Right(cast(a.Number as char(3)),1)  
WHEN '9' then a.number 
 Else 0
END 'Col9'
From  allowednumbers as a
Where (a.Number Not Between 1 and 25) and a.Number NOT IN (
Select scrubbed_number 
From vnumbers as b 
Where (b.Owner='South' and b.Chapter='South') and b.ThisYear= '2014')
Order by Cast(Left(cast(a.Number as char(3)), Length(cast(a.Number as char(3)))-1) as unsigned) 
) as x
Group by x.Row
Order by Cast(x.Row as unsigned)
) as z

Answer 1

我之前的回答有问题...我发现有些数据行不符合我的预期...所以我用一个函数解决了这个问题:

CREATE FUNCTION dbo.getnumberonly
(
    @p1 nvarchar(50)
)
RETURNS int
AS
BEGIN   
Return
    case (patindex('%[a-z]%',@p1)) 
        when 0 then @p1 
        else 
            case (patindex('%[0-9]%',@p1)) 
                when 1 then  -- Numbers first
                    substring(@p1, patindex('%[0-9]%',@p1), len(@p1) - patindex('%[0-9]%',reverse(@p1)) + patindex('%[0-9]%',@p1)) 
                else 
                 dbo.getnumberonly(substring(@p1, patindex('%[0-9]%',@p1), len(@p1) - patindex('%[0-9]%',@p1)))
            end
    end
END