SQL Server 2008 NOT IN() 似乎失败

发布于 2024-08-08 08:13:00 字数 653 浏览 3 评论 0原文

我正在构建一个暗示性标签系统（听起来很糟糕），我将一个字符串传递给数据库，如果有匹配，它会发回该标签。我想停止重复的标签，所以如果一个标签是 WEB，我只希望它返回一次。因此，如果我将 WEB 作为标签，然后添加 WEEKLY，则输入“WE”不会再次显示 WEB 作为建议。

但是，似乎不起作用。我发送当前标签并使用 NOT IN() 删除任何重复项。下面是 SP 代码：

SELECT  TOP 1 t.vTagName As vTagName
FROM        Tags t
WHERE       t.vTagName LIKE @vTagName+'%'
AND         t.nTagPortalId = @nPortalId
AND         t.vTagName NOT IN(@vCurrentTags)
ORDER BY    vTagName ASC

这就是 get 传入的内容：

EXEC GetTagSuggest 'We','Web,Print,Design,Advertising,Revenue'

该查询的响应是 vTagName = Web。显然，这不是正确的结果，因为它应该是 vTagName LIKE “We” NOT IN “Web, etc...”..

提前致谢！

原文

I am building a suggestive tagging system (sounds seedy), I pass in a string to the db and if there is a match it sends back that tag. I want to stop duplicate tags, so if one tag is WEB, I only want it to be returned once. So if I had WEB as a tag and then add WEEKLY, typing "WE" doesn't bring up WEB for a second time as a suggestion.

But, seem to be not working. I send the current tags and use NOT IN() to remove any duplicates. Below is the SP code:

SELECT  TOP 1 t.vTagName As vTagName
FROM        Tags t
WHERE       t.vTagName LIKE @vTagName+'%'
AND         t.nTagPortalId = @nPortalId
AND         t.vTagName NOT IN(@vCurrentTags)
ORDER BY    vTagName ASC

And this is what get's passed in:

EXEC GetTagSuggest 'We','Web,Print,Design,Advertising,Revenue'

The response to this query is vTagName = Web. Obviously, this is not a correct result as it should be vTagName LIKE "We" NOT IN "Web, etc..."..

Thanks in advance!

分享到QQ

分享到微博

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

发布评论

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

清风挽心 2024-08-15 08:13:00

IN 语句不是这样工作的。

您所要做的就是

t.vTagName not in ('Web','Print','Design','Advertising','Revenue')

将其放在一个变量中，在这种情况下将不起作用。

The IN statement doesn't work like that.

What you will have to do is

t.vTagName not in ('Web','Print','Design','Advertising','Revenue')

Having it in one variable won't work in that case.

回复收藏 0 原文

浸婚纱 2024-08-15 08:13:00

您需要将字符串拆分出来，或者将整个 SQL 语句转换为动态 SQL。

编辑：

根据其他示例拆分变量，或者执行动态 SQL。

这不是最佳实践，只是为了给您提供一个想法：

DECLARE @sql nvarchar(max) = 'SELECT  TOP 1 t.vTagName As vTagName '
SELECT @sql = @sql + 'FROM  Tags t '
SELECT @sql = @sql + 'WHERE t.vTagName LIKE ' + @vTagName + '% '
SELECT @sql = @sql + 'AND   t.nTagPortalId = ' + @nPortalId + ' '
SELECT @sql = @sql + 'AND   t.vTagName NOT IN(' + @vCurrentTags + ') '
SELECT @sql = @sql + 'ORDER BY  vTagName ASC'

EXEC sp_executesql @sql, @vTagName, @nPortalId, @vCurrentTags

You need to split out the strings, or convert the entire SQL statement to dynamic SQL.

EDIT:

Split out variables per the other example, or do dynamic SQL.

This is not best practice, but is simply to give you an idea:

DECLARE @sql nvarchar(max) = 'SELECT  TOP 1 t.vTagName As vTagName '
SELECT @sql = @sql + 'FROM  Tags t '
SELECT @sql = @sql + 'WHERE t.vTagName LIKE ' + @vTagName + '% '
SELECT @sql = @sql + 'AND   t.nTagPortalId = ' + @nPortalId + ' '
SELECT @sql = @sql + 'AND   t.vTagName NOT IN(' + @vCurrentTags + ') '
SELECT @sql = @sql + 'ORDER BY  vTagName ASC'

EXEC sp_executesql @sql, @vTagName, @nPortalId, @vCurrentTags

回复收藏 0 原文

~没有更多了~