内联与喜欢 [英] Inner join with like

问题描述

我有2个车辆表（年/制造/车型/车削/发动机气缸/变速箱）。

尝试使用内部连接加入2个表在年/制造/模型/传输，但气缸不是真的相等。在tb1中，圆柱体将是（8cyl。/ 4cyl。），在tb2圆柱体中将是字段值的一部分（coupe 4cyl.manaul）。

我试图使用LIKE但它没有虽然tb1中的圆柱体是tb2圆柱体场的一部分但是工作。



我有2个问题：

1-如何使用Like使用内部连接来获取具有相同柱面编号的所有行（我已尝试过！）

2-是否可以修剪tb2中的字段以仅获得柱面编号所以（coupe 4cyl.manaul）将是（4cyl）。

编辑：使用SQL Server 2008 R2



谢谢，

Samira



我的尝试：



这是我试图内部加入2个表，它们在没有柱面的情况下工作完美：

Hi,
I have 2 vehicles tables like (year/make/model/trim/EngineCylinder/transmission).
trying to join the 2 tables using inner join on year/make/model/transmission, but the cylinder is not really equal. In tb1 cylinder will be something like (8cyl. / 4cyl.), in tb2 cylinder will be part of the field value something like (coupe 4cyl. manaul).
I tried to use LIKE but it doesn't work although cylinder in tb1 is part of tb2 cylinder field vale.

I have 2 question:
1- How to use Like with inner join to get all the rows that has the same cylinder number (which i have tried!)
2- is it possible to trim the field in tb2 to get only the cylinder No. so (coupe 4cyl. manaul) will be just (4cyl).
Edit: using SQL server 2008 R2

thanks,
Samira

What I have tried:

Here is what I have tried to inner join the 2 tables which work perfect without cylinder:

Select table1.* tb1
inner join table2 tb2
on tb1.year=tb2.year
and tb1.make=tb2.make
and tb1.model=tb2.model 
and tb1.transmission=tb2.transmission
and (tb1.EngineCylinder like '%' + tb2.EngineCyliner + '%') --or use where 
-- also tried: and tb1.EngineCylinder in (select EngineCylinder from tb2)

推荐答案

如果我读得正确，tb2中的EngineCylinder比tb1更复杂。

LIKE将检查第一个字符串是否包含第二个字符串。



如果你从你的当前表达式

If I read correctly, the EngineCylinder in tb2 is more complex than tb1's.
LIKE will check whether the first string contains the second one.

If you switch from your current expression to

... and (tb2.EngineCyliner LIKE '%' + tb1.EngineCylinder + '%')

这应该有效。

或者您也可以使用patindex，它为您提供另一个文本模式的起始位置

That should work.
Or you can also use patindex which gives you the starting position of text pattern within another

... and PATINDEX('%' + tb1.EngineCylinder + '%', tb2.EngineCyliner) >= 1

编辑

来自我在MS SQL Server的最新研究;虽然这很有用，但在JOIN或WHERE子句中使用带有like或function的谓词是不方便的，因为数据库引擎无法从索引中受益，并且必须对表中的每一行执行该操作...对于巨大的表来说真的很糟糕，如果这是一个生产环境，例如每秒数百个用户，真的很糟糕。 MS SQL Server确实具有帮助这种搜索的功能，例如FULL TEXT SEARCH（google for it）。

EDIT
From my latest studies in MS SQL Server; Although this works it is inconvenient to use a predicate with a like or a function in a JOIN or the WHERE clause because the database engine can't benefit from indexes, and will have to execute that operation for every row in the table... Really bad for huge tables, and really bad if this is a production environment such as hundreds of users per second. MS SQL Server does have features to help this kind of search, for example FULL TEXT SEARCH (google for it).

这篇关于内联与喜欢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！