如何使用 MSSQL 查询更改数据类型

Question

--ADB sample data
student_id  gender__id  year_of_birth   month_of_birth  day_of_birth
333423              2           2001            2             4
333503              2           2001            11            5
333243              2           NULL            NULL          NULL
333536              1           2001            7             25
333199              1           2001            1             1
333455              1           2001            11            23
338866              1           2001            12            30
339365              1           NULL            NULL          NULL

. . .

--BDB sample data
ID         sex      birthday
AB332748    2   1999-08-01
AB332804    1   1989-01-15
AB333152    1   24591
AB333455    1   2001-11-23

. . .

--Expected result
student_id  gender__id    year_of_birth    month_of_birth    day_of_birth  time_of_birth          ID        sex          birthday
332748         2            2001            2                    4         NULL                332748       2         1999-08-01
332804         2            2001           11                    5         NULL                 332804      1         1989-01-15
333152         2            NULL           NULL                  NULL      NULL                333152       1         24591
333455         1           2001            7                    25         NULL                333455       1         2001-11-23
333593         1           2001            1                     1         NULL                333593       2         1943-05-26
333693         1           2001           11                    23         NULL                333693       2         1939-02-25
334304         1           2001           12                    30         NULL                334304       2         1956-03-07
334326        1            NULL           NULL                  NULL       NULL                334326       2         1963-04-04

我使用两个不同数据类型的表制作了一些sql查询代码。MSSQL 版本是 MSSQL 服务器 2017 开发人员版。

--problem sqlquery source code
SELECT ID,
sex,
birthday,
student_id,
gender_id,
year_of_birth,
month_of_birth,
day_of_birth,
time_of_birth
FROM ADB.dbo.PERSON
LEFT JOIN BDB.dbo.BASE_demo ON CAST(ADB.dbo.PERSON.student_id AS INT) = CAST(REPLACE(BDB.dbo.B_demo.ID, 'AB', '') AS int)
AND ADB.dbo.PERSON.student_id = BDB.dbo.BASE_demo.ID
WHERE BDB.dbo.BASE_demo.ID LIKE 'AB%'
AND ISNUMERIC(REPLACE(BDB.dbo.BASE_demo.ID, 'AB', '')) = 1;

调试顶级代码后，存在一些类似此消息的问题。

从nvarchar值'AB332748'到数据类型int没有变化。

您知道如何解决此问题吗？

Answer 1

我没有测试过这个，但是，这是我的猜测。这里没有预期的输出(OP 已经承认我要求(，他们也没有建议他们拥有什么版本的 SQL Server，所以我相信他们有 SQl Server 2012+(如果他们使用的是 2008 或更早版本，那么升级应该在"要做的事情"列表中排名很高(。

如果这不起作用，或提供预期的结果，请参阅abvoe段落：

SELECT ID, --Missing Table Alias
sex, --Missing Table Alias
birthday, --Missing Table Alias
student_id, --Missing Table Alias
gender_id, --Missing Table Alias
year_of_birth, --Missing Table Alias
month_of_birth, --Missing Table Alias
day_of_birth, --Missing Table Alias
time_of_birth --Missing Table Alias
FROM ADB.dbo.PERSON P
JOIN BDB.dbo.BASE_demo B ON P.student_id TRY_CONVERT(int,STUFF(B.ID,1,2,'')) --This is non-SARGable
WHERE B.ID LIKE 'AB%'
AND TRY_CONVERT(int,STUFF(B.ID,1,2,'')) IS NOT NULL;

请注意，我使用了INNER JOIN.使用LEFT JOIN，然后在WHERE中引用表而不应对NULL会将连接变成隐式INNER JOIN。

Answer 2

示例数据存在问题 -ADB.dbo.PERSON和BDB.dbo.BASE_demo中都只存在一条记录(ID：333455(，假设ADB.dbo.PERSON.student_id映射到BDB.dbo.BASE_demo.ID，只是在前面加上"AB"(根据OP的查询似乎是这种情况(。 因此，由于ADB.dbo.PERSON中没有 ID 333152的记录，因此无法在提供的预期结果中复制该行。

也就是说，我会使用 CTE 相应地操作BDB.ID，然后加入其中。 这是我的完整重现：

-- set up tables
Create Table ADB 
(
Student_ID Int
, Gender_ID TinyInt
, Year_of_Birth Int
, Month_of_Birth Int
, Day_of_Birth Int
)
Create Table BDB
(
ID Varchar(20)
, Sex TinyInt
, Birthday Varchar(20)
)
Insert Into dbo.ADB
(
Student_ID
, Gender_ID
, Year_of_Birth
, Month_of_Birth
, Day_of_Birth
)
Select 333423, 2, 2001, 2, 4
Union All Select 333503, 2, 2001, 11, 5
Union All Select 333243, 2, NULL, NULL, NULL
Union All Select 333536, 1, 2001, 7, 25
Union All Select 333199, 1, 2001, 1, 1
Union All Select 333455, 1, 2001, 11, 23
Union All Select 338866, 1, 2001, 12, 30
Union All Select 339365, 1, NULL, NULL, Null
Insert Into dbo.BDB
(
ID
, Sex
, Birthday
)
Select 'AB332748', 2, '1999-08-01'
Union All Select 'AB332804', 1, '1989-01-15'
Union All Select 'AB333152', 1, '24591'
Union All Select 'AB333455', 1, '2001-11-23'
-- here's the code.  I was on SQL 2012, so no Try_Cast()
;with CTE_bdb as
(
Select
Cast(Replace(ID, 'AB', '') as Int) As ID,  
sex,
birthday
From dbo.BDB
Where ID LIKE 'AB%'
And IsNumeric(Replace(ID, 'AB', '')) = 1
)
Select 
a.student_id,
a.gender_id,
a.year_of_birth,
a.month_of_birth,
a.day_of_birth,
Null as time_of_birth,
b.ID,
b.sex,
b.birthday
From dbo.ADB As a
Left Outer Join CTE_bdb as b
On A.student_id = b.ID

这是我的输出(对格式表示歉意 - 我不知道如何让它看起来像你在最初的帖子中所做的那样(：

student_id gender_id year_of_birth month_of_birth day_of_birth time_of_birth ID sex birthday 333423 2 2001 2 4 NULL NULL NULL NULL 333503 2 2001 11 5 NULL NULL NULL NULL 333243 2 NULL NULL NULL NULL NULL NULL NULL 333536 1 2001 7 25 NULL NULL NULL NULL 333199 1 2001 1 1 NULL NULL NULL NULL 333455 1 2001 11 23 NULL 333455 1 2001-11-23 338866 1 2001 12 30 NULL NULL NULL NULL 339365 1 NULL NULL NULL NULL NULL NULL NULL