在like语句上联接SQL Server表 [英] Join SQL Server tables on a like statement
问题描述
我希望这不会重复.我检查了搜索结果,但似乎找不到明确的答案.
I am hoping this isn't a repeat. I've checked the searches and I can't seem to find a clear answer to this.
我有一个表,该表的主键设置为UniqueIdentifier
.我还有另一个表,该表的varchar
列基本上包含一个带有查询字符串的URL,该查询字符串包含来自我的第一个表的guid.
I have a table that has it's primary key set to be a UniqueIdentifier
. I also have another table that has a varchar
column that basically contains a url with a query string that contains guids from my first table.
所以我的2张桌子就像:
So my 2 tables are like:
状态表
StateID StateName
EB06F84C-15B9-4397-98AD-4A63DA2A238E Active
URLTable
URL
page.aspx?id=EB06F84C-15B9-4397-98AD-4A63DA2A238E
我想做的是将URLTable
和StateTable
连接在一起.StateID
的值包含在URL表的URL中.我还没有真正弄清楚这个加入.我什至尝试只选择一个表并尝试按StateTable
中的值进行过滤.我已经尝试做过这样的事情:
What I'm trying to do is join together URLTable
and StateTable
ON the value of StateID
is contained in URL of URL table. I haven't really figured out the join. I've even tried just selecting the one table and tried to filter by the values in StateTable
. I've tried doing something like this:
SELECT *
FROM URLTable
WHERE EXISTS
(SELECT *
FROM StateTable
WHERE URL LIKE '%' + StateID + '%')
即使这样也不起作用,因为它表示我正在比较uniqueidentifier
和varchar
.
Even that doesn't work because it says I'm comparing uniqueidentifier
and varchar
.
有什么方法可以使用like命令联接2个表,并且like命令没有比较2个不兼容的变量?
Is there any way to join 2 tables using a like command and where the like command isn't comparing 2 incompatible variables?
谢谢!!
更新:让我添加一些我应该提到的其他内容.该查询用于构建分析报告.表格是CMS分析软件包的一部分...因此,更新或更改表格结构不是一种选择.
UPDATE: Let me add some additional things I should have mentioned. The query is for the purposes of building analytics reports. The tables are part of a CMS analytics package... so updating or changing the table structure is not an option.
第二,由于这些表正在捕获站点分析,因此流量非常大……因此,性能非常成问题.第三件事是,在我的示例中,我说了id =,但可能有多个值,例如id=guid&user=guid&date=date
.
Secondly, these tables see a very high amount of traffic since they're capturing site analytics... so performance is very much an issue. The 3rd thing is that in my example, I said id= but there may be multiple values such as id=guid&user=guid&date=date
.
更新2:我刚刚意识到的另一件事是,有时查询字符串中的破折号已从GUID中删除..有时没有..因此,除非我弄错了,否则我无法将子字符串转换为Uniqueidentifier
.任何人都可以确认吗?叹气.我确实使用
UPDATE 2: One more thing I just realized to my horror is that sometimes the query string has the dashes removed from the GUID.. and sometimes not.. so unless I"m mistaken, I can't cast the substring to Uniqueidentifier
. Can anyone confirm? sigh. I did get it to work using
REPLACE('-','',CONVERT(varchar(50), a.AutomationStateId))
但是现在我非常担心与此相关的性能问题,因为URL的表很大.但是,除非我无能为力,否则这可能就是野兽的天性.
but now I'm very much worried about performance issues with this since the URL's table is very large. This might be the nature of the beast, though, unless there's anything I can do.
推荐答案
将StateID转换为兼容类型,例如
Cast StateID to a compatible type, e.g.
WHERE URL LIKE '%' + CONVERT(varchar(50), StateID) + '%'
或
WHERE URL LIKE N'%' + CONVERT(nvarchar(50), StateID) + N'%'
如果URL为nvarchar(...)
if URL is nvarchar(...)
编辑
正如另一个答案中指出的那样,这可能会导致大型表的性能下降. 将LIKE与CONVERT结合使用将进行表扫描.对于小型表来说,这可能不是问题,但如果性能出现问题,则应考虑将URL分为两列.一列将包含"page.aspx?id =",另一列将包含UNIQUEIDENTIFIER.这样,您的查询就可以更加轻松地优化.
As pointed out in another answer, this could result in poor performance on large tables. The LIKE combined with a CONVERT will result in a table scan. This may not be a problem for small tables, but you should consider splitting the URL into two columns if performance becomes a problem. One column would contain 'page.aspx?id=' and the other the UNIQUEIDENTIFIER. Your query could then be optimized much more easily.
这篇关于在like语句上联接SQL Server表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!