如何从表中获取多个值

发布于 2024-12-18 07:04:16 字数 309 浏览 1 评论 0原文

如何分割字符串值

我想获取id....

Table1

ID 

001
002
003

查询

SELECT id FROM Table1 WHERE (id IN ('" & Eid & "'))

If Eid = 001 means It is displaying id 001
If Eid = 001, 002 means id is not displaying

如何进行查询以获取多个id。

需要查询帮助

原文

How to split the String value

I want to get the id....

Table1

ID 

001
002
003

Query

SELECT id FROM Table1 WHERE (id IN ('" & Eid & "'))

If Eid = 001 means It is displaying id 001
If Eid = 001, 002 means id is not displaying

How to make a query for getting multiple id.

Need Query Help

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最后的乘客 2024-12-25 07:04:16

CREATE FUNCTION [dbo].[fnGetIDasTable] (
    @ids varchar(MAX), 
    @separator varchar(20)
) RETURNS @retTable TABLE(ID bigint)
BEGIN
    DECLARE @firstComma bigint
    IF (@ids IS NOT NULL)
    BEGIN
        WHILE @ids != ''
        BEGIN
            SET @firstComma = CHARINDEX(@separator, @ids)
            IF @firstComma = 0
            BEGIN
                INSERT INTO @retTable 
                    VALUES (@ids)
                RETURN
            END
            INSERT INTO @retTable 
                VALUES (CONVERT(bigint, SUBSTRING(@ids, 1, @firstComma - 1)))
            SET @ids = SUBSTRING(@ids, @firstComma + 1, LEN(@ids))
        END
    END
    RETURN
END
GO

DECLARE @IdList varchar(MAX) = '1,3'
SELECT id FROM Table1 
    INNER JOIN [dbo].[fnGetIDasTable] (@IdList,',') il ON Table1.id = il.ID
GO

CREATE FUNCTION [dbo].[fnGetIDasTable] (
    @ids varchar(MAX), 
    @separator varchar(20)
) RETURNS @retTable TABLE(ID bigint)
BEGIN
    DECLARE @firstComma bigint
    IF (@ids IS NOT NULL)
    BEGIN
        WHILE @ids != ''
        BEGIN
            SET @firstComma = CHARINDEX(@separator, @ids)
            IF @firstComma = 0
            BEGIN
                INSERT INTO @retTable 
                    VALUES (@ids)
                RETURN
            END
            INSERT INTO @retTable 
                VALUES (CONVERT(bigint, SUBSTRING(@ids, 1, @firstComma - 1)))
            SET @ids = SUBSTRING(@ids, @firstComma + 1, LEN(@ids))
        END
    END
    RETURN
END
GO

DECLARE @IdList varchar(MAX) = '1,3'
SELECT id FROM Table1 
    INNER JOIN [dbo].[fnGetIDasTable] (@IdList,',') il ON Table1.id = il.ID
GO

回复收藏 0 原文

寄离 2024-12-25 07:04:16

删除 ' 引号：

SELECT id FROM Table1 WHERE (Personid IN (" & Eid & "))

您的版本正在生成

... WHERE (PersonID IN ('001,002'))

，这意味着设置的括号内有一个 SINGLE 值。没有单引号：

... WHERE (PersonID IN (001,002))

括号内有 TWO 值。

但请注意，前导 0 可能会导致问题。它们可以被解释为八进制值，因此 009 将被解释为“十进制 10”。您可能需要预处理您的值才能将其转换为

... WHERE (PersonID IN ('001', '002))

。

remove the ' quotes:

SELECT id FROM Table1 WHERE (Personid IN (" & Eid & "))

Your version was generating

... WHERE (PersonID IN ('001,002'))

which means there's a SINGLE value inside the set brackets. Without the single quotes:

... WHERE (PersonID IN (001,002))

you have TWO values in the set brackets.

However, note that the leading 0's may cause problems. They could be interepreted as octal values, so 009 would be interpreted as "10 decimal". You may have to pre-process your values to turn them into

... WHERE (PersonID IN ('001', '002))

instead.

回复收藏 0 原文

凉城 2024-12-25 07:04:16

就像 Marc B 的答案，但我的解决方案更简单一些。

SELECT id FROM Table1 WHERE id IN (
                                   replace(
                                           replace('" & Eid & "',' ','')
                                           ,',',''','''
                                          )
                                  )

第一个替换是删除空格（如果没有空格，可以删除此替换）

第二个替换是将 , 替换为 ','

Like Marc B's answer but my solution is a bit easier.

SELECT id FROM Table1 WHERE id IN (
                                   replace(
                                           replace('" & Eid & "',' ','')
                                           ,',',''','''
                                          )
                                  )

First replace is removing space (if you have no space, you can remove this replace)

Second replace is replacing , to ','

回复收藏 0 原文

~没有更多了~