在类似语句上加入 SQL Server 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10019557/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join SQL Server tables on a like statement
提问by divamatrix
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.
我希望这不是重复。我检查了搜索，我似乎无法找到明确的答案。
I have a table that has it's primary key set to be a UniqueIdentifier. I also have another table that has a varcharcolumn that basically contains a url with a query string that contains guids from my first table.
我有一个表，它的主键设置为UniqueIdentifier. 我还有另一个表，其中有一varchar列基本上包含一个带有查询字符串的 url，其中包含我的第一个表中的 guid。
So my 2 tables are like:
所以我的 2 个表是这样的：
StateTable
状态表
StateID StateName
EB06F84C-15B9-4397-98AD-4A63DA2A238E Active
URLTable
网址表
URL
page.aspx?id=EB06F84C-15B9-4397-98AD-4A63DA2A238E
What I'm trying to do is join together URLTableand StateTableON the value of StateIDis 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:
我想要做的是连接在一起URLTable，StateTableON 的值StateID包含在 URL 表的 URL 中。我还没有真正弄清楚加入。我什至尝试只选择一张表并尝试按StateTable. 我试过做这样的事情：
SELECT *
FROM URLTable
WHERE EXISTS
(SELECT *
FROM StateTable
WHERE URL LIKE '%' + StateID + '%')
Even that doesn't work because it says I'm comparing uniqueidentifierand varchar.
即使这样也行不通，因为它说我正在比较uniqueidentifier和varchar。
Is there any way to join 2 tables using a like command and where the like command isn't comparing 2 incompatible variables?
有没有办法使用 like 命令连接 2 个表，并且 like 命令不比较 2 个不兼容的变量？
Thank you!!
谢谢！！
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.
更新：让我添加一些我应该提到的其他内容。该查询用于构建分析报告。这些表是 CMS 分析包的一部分......所以更新或更改表结构不是一种选择。
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.
其次，这些表看到了非常高的流量，因为它们正在捕获站点分析……所以性能是一个非常大的问题。第三件事是，在我的示例中，我说的是 id= 但可能有多个值，例如id=guid&user=guid&date=date.
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
更新 2：我刚刚意识到的另一件事是，有时查询字符串会从 GUID 中删除破折号 .. 有时不会.. 所以除非我弄错了，否则我不能将子字符串转换为Uniqueidentifier. 任何人都可以确认？叹息。我确实使用
REPLACE('-','',CONVERT(varchar(50), a.AutomationStateId))
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.
但现在我非常担心这个的性能问题，因为 URL 的表非常大。不过，这可能是野兽的本性，除非我能做些什么。
回答by Phil
Cast StateID to a compatible type, e.g.
将 StateID 转换为兼容类型，例如
WHERE URL LIKE '%' + CONVERT(varchar(50), StateID) + '%'
or
或者
WHERE URL LIKE N'%' + CONVERT(nvarchar(50), StateID) + N'%'
if URL is nvarchar(...)
如果 URL 是 nvarchar(...)
EDIT
编辑
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 与 CONVERT 结合将导致表扫描。对于小表，这可能不是问题，但如果性能成为问题，您应该考虑将 URL 拆分为两列。一列将包含“page.aspx?id='，另一列包含 UNIQUEIDENTIFIER。然后可以更轻松地优化您的查询。
回答by Arion
Do you know that the =is always there and always is a UNIQUEIDENTIFIER. Then you can do this:
您是否知道=始终存在并且始终是UNIQUEIDENTIFIER。然后你可以这样做：
WHERE CAST(SUBSTRING(URL, CHARINDEX('=',URL)+1,LEN(URL)) AS UNIQUEIDENTIFIER)=StateID
EDIT
编辑
As part of the comment you can also so it with a JOIN. Like this:
作为评论的一部分，您也可以使用JOIN. 像这样：
select
u.*
from
urltable
join statetable s
on CAST(SUBSTRING(URL, CHARINDEX('=',URL)+1,LEN(URL)) AS UNIQUEIDENTIFIER)=StateID
回答by Tony Hopkinson
select u.* from urltable
join statetable s on url like N'%' + (convert(varchar(50),s.stateid) + N'%'
performance is likely to be awful
表现可能很糟糕
回答by Adriaan Davel
You may get a performance improvement if you build a temp table first, with the option to index the temp table. You could then also modify the schema (of your temp table) which could give you options on your join. Often when joining to BIG tables it helps to extract a subset of data to a temp table first, then join to it. Other times the overhead of the temp table is bigger than using an 'ugly' join
如果您首先构建临时表，并提供索引临时表的选项，您可能会获得性能改进。然后，您还可以修改（临时表的）架构，这可以为您提供连接选项。通常，在连接到 BIG 表时，首先将数据子集提取到临时表中，然后再连接到它会很有帮助。其他时候临时表的开销比使用“丑陋”连接更大
