SQL – WHERE子句中的uniqueidentifier

huangapple
go评论97阅读模式
英文:

SQL - uniqueidentifier in where clause

问题

我正在使用SSMS v18。

SQL Server版本：Microsoft SQL Azure RTM 12.0.200.8

我有3500个唯一标识符需要在where子句中进行比较或保留。

select *
from tableName
where columnName in ('6B29FC40-CA47-1067-B31D-00DD010662DA',
'7C59FC31-SA87-1667-B31D-00DD010772DA'
.
.
.
)

columnName 在这里是唯一标识符。

就像这样，我有3500个唯一标识符要在where子句中比较。

我遇到了这个错误：

> 转换失败，无法将字符转换为唯一标识符

当我在查询中使用 convert(nvarchar(36, columnName)) 时，查询会运行5分钟，但不返回任何结果。

英文:

I am using SSMS v18.

SQL Server version: Microsoft SQL Azure RTM 12.0.200.8

I have 3500 unique identifiers to compare or to keep in where clause.

  1. select * 
  2. from tableName
  3. where columnName in ('6B29FC40-CA47-1067-B31D-00DD010662DA',
  4.                      '7C59FC31-SA87-1667-B31D-00DD010772DA'
  5.                      .
  6.                      .
  7.                      .
  8.                     )

columnName is unique identifier here.

Like this I have 3500 unique identifiers to compare in where clause.

I am getting this error:

> Conversion failed when converting from a character to uniqueidentifier

when I use convert(nvarchar(36, columnName)) in query - the query keeps running for 5 minutes but doesn't return any results.

答案1

得分: 1

请尝试以下解决方案。

总体而言，缺少将数据转换为适当数据类型。

VARCHAR(...) 数据类型与 UNIQUEIDENTIFIER 数据类型不匹配。

SQL

  1. -- DDL 和示例数据填充，开始
  2. DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, col UNIQUEIDENTIFIER);
  3. INSERT INTO @tbl (col) VALUES
  4. (TRY_CAST('6B29FC40-CA47-1067-B31D-00DD010662DA' AS UNIQUEIDENTIFIER)),
  5. (TRY_CAST('C49CBB38-2C1E-43DE-8A9D-3DECB6B4F183' AS UNIQUEIDENTIFIER));
  6. -- DDL 和示例数据填充，结束
  8. -- 方法 #1
  9. SELECT * FROM @tbl
  10. WHERE TRY_CAST(col AS VARCHAR(40)) IN
  11. (
  12.     '6B29FC40-CA47-1067-B31D-00DD010662DA',
  13.     '7C59FC31-SA87-1667-B31D-00DD010772DA'
  14. );
  16. -- 方法 #2
  17. SELECT t.*
  18. FROM @tbl AS t INNER JOIN
  19.    (VALUES
  20.       ('6B29FC40-CA47-1067-B31D-00DD010662DA'),
  21.       ('7C59FC31-SA87-1667-B31D-00DD010772DA')
  22.    ) AS UniqueIDs(col)
  23. ON t.col = TRY_CAST(UniqueIDs.col AS UNIQUEIDENTIFIER);
英文:

Please try the following solution.

Overall, casting to a proper data type was missing.

VARCHAR(...) data type is not a UNIQUEIDENTIFIER data type.

SQL

  1. -- DDL and sample data population, start
  2. DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, col UNIQUEIDENTIFIER);
  3. INSERT INTO @tbl (col) VALUES
  4. (TRY_CAST('6B29FC40-CA47-1067-B31D-00DD010662DA' AS UNIQUEIDENTIFIER)),
  5. (TRY_CAST('C49CBB38-2C1E-43DE-8A9D-3DECB6B4F183' AS UNIQUEIDENTIFIER));
  6. -- DDL and sample data population, end
  8. -- Method #1
  9. SELECT * FROM @tbl
  10. WHERE TRY_CAST(col AS VARCHAR(40)) in
  11. (
  12. 	'6B29FC40-CA47-1067-B31D-00DD010662DA',
  13. 	'7C59FC31-SA87-1667-B31D-00DD010772DA'
  14. );
  16. -- Method #2
  17. SELECT t.*
  18. FROM @tbl AS t INNER JOIN
  19.    (VALUES
  20.       ('6B29FC40-CA47-1067-B31D-00DD010662DA'),
  21.       ('7C59FC31-SA87-1667-B31D-00DD010772DA')
  22.    ) AS UniqueIDs(col)
  23. ON t.col = TRY_CAST(UniqueIDs.col AS UNIQUEIDENTIFIER);

答案2

得分: 1

在那3500个值中，您可以为以下内容添加前缀：

  1. select * 
  2. from tableName
  3. where 
  4.    columnName in
  5.               (
  6.                 SELECT 
  7.                      CONVERT(uniqueidentifier,'6B29FC40-CA47-1067-B31D-00DD010662DA'),
  8.                SELECT 
  9.                     CONVERT(uniqueidentifier,'7C59FC31-SA87-1667-B31D-00DD010772DA')
  10. .
  11. .
  12. .
  13. )
英文: 
  1. in those 3500 values you can add a prefix for below
  1.     select * 
  2.     from tableName
  3.     where 
  4.        columnName in
  5.                   (
  6.                     SELECT 
  7.                          CONVERT(uniqueidentifier,'6B29FC40-CA47-1067-B31D-00DD010662DA'),
  8.                    SELECT 
  9.                         CONVERT(uniqueidentifier,'7C59FC31-SA87-1667-B31D-00DD010772DA')
  10.     .
  11.     .
  12.     .
  13.     )

答案3

得分: -1

我使用以下格式解决了它 -

  1. SELECT *
  2. FROM tableName
  3. WHERE columnName IN (
  4.     SELECT TRY_CONVERT(uniqueidentifier, '6B29FC40-CA47-1067-B31D-00DD010662DA') UNION ALL
  5.     SELECT TRY_CONVERT(uniqueidentifier, '7C59FC31-SA87-1667-B31D-00DD010772DA')
  6.     -- 为其余的唯一标识符添加更多的SELECT语句
  7. )
英文:

I resolved it using format below -

  1. SELECT *
  2. FROM tableName
  3. WHERE columnName IN (
  4.     SELECT TRY_CONVERT(uniqueidentifier, '6B29FC40-CA47-1067-B31D-00DD010662DA') UNION ALL
  5.     SELECT TRY_CONVERT(uniqueidentifier, '7C59FC31-SA87-1667-B31D-00DD010772DA')
  6.     -- Add more SELECT statements for the remaining unique identifiers
  7. )

huangapple
  • 本文由 发表于 2023年6月30日 01:06:57
  • 转载请务必保留本文链接：https://go.coder-hub.com/76583206.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定