2023年7月13日发(作者：)

mysqlin和like_mysql-⼀起使⽤SQLLIKE和INmysql-⼀起使⽤SQL LIKE和IN有没有办法同时使⽤LIKE和IN？我想实现这样的⽬标。SELECT * FROM tablename WHERE column IN ('M510%', 'M615%', 'M515%', 'M612%');因此，基本上，我希望能够⽤⼀堆不同的字符串来匹配该列。 是否有另⼀种⽅法可以对⼀个查询执⾏此操作，还是我必须遍历要查找的字符串数组？Nick asked 2020-06-26T17:09:21Z10个解决⽅案58 votes如何在IN中使⽤⼦字符串。select * from tablename where substring(column,1,4) IN ('M510','M615','M515','M612')tvanfosson answered 2020-06-26T17:10:10Z54 votes您可以通过在⼀个查询中将各个LIKE与OR串在⼀起来完成此操作：SELECT * FROM tablenameWHERE column LIKE 'M510%'OR column LIKE 'M615%'OR column LIKE 'M515%'OR column LIKE 'M612%';请注意，像LIKE和per-row函数之类的东西并不总是能够很好地扩展。 如果表可能会变⼤，则可能需要考虑向表中添加另⼀列以独⽴存储字段的前四个字符。这将复制数据，但是您可以通过使⽤插⼊和更新触发器来保证数据保持⼀致。 然后在该新列上添加索引，您的查询将变为：SELECT * FROM tablename WHERE newcolumn IN ('M510','M615','M515','M612');这会将计算成本移到了必要的程度(数据更改时)，⽽不是每次读取时都如此。 实际上，您甚⾄可以⾛得更远，将新列作为布尔值表⽰它是四种特殊类型之⼀(如果那组特殊类型很少更改)。 然后查询将更快：SELECT * FROM tablename WHERE is_special = 1;这种对速度的存储需求的权衡是⼤型数据库的⼀个有⽤技巧-通常，磁盘空间便宜，CPU占⽤⼤量资源，并且读取数据的频率远⾼于写⼊数据。 通过将计算成本移⾄写⼊阶段，可以在所有读取中摊销成本。paxdiablo answered 2020-06-26T17:09:50Z8 votes您需要使⽤多个LIKE词，再加上OR。Ignacio Vazquez-Abrams answered 2020-06-26T17:10:30Z5 votes使⽤较长的IN，即⼀堆OR。SELECT * FROM tablenameWHERE column LIKE 'M510%'OR column LIKE 'M615%'OR column LIKE 'M515%'OR column LIKE 'M612%';Yada answered 2020-06-26T17:10:50Z2 votessubstr([column name],[desired starting position (numeric)],[# characters to include (numeric)]) in ([complete as usual])例substr([column name],1,4) in ('M510','M615', 'M515', 'M612')MissPeri30 answered 2020-06-26T17:11:10Z2 votesSELECT * FROM tablenameWHERE column IN(select column from tablenamewhere column like 'M510%'or column like 'M615%'OR column like 'M515%'or column like'M612%')matt answered 2020-06-26T17:11:26Z1 votes我尝试了另⼀种⽅式说表有值1 M5102 M6153 M5154 M6125 M510MM6 M615NN7 M515OO8 M612PP9 A10 B11 C12 D这⾥第1列到第8列有效，⽽其余的则⽆效SELECT COL_VALFROM SO_LIKE_TABLE SLTWHERE (SELECT DECODE(SUM(CASEWHEN INSTR(_VAL, COLUMN_VALUE) > 0 THEN1ELSE0END),0,'FALSE','TRUE')FROM TABLE(_DEBUG_VC2COLl('M510', 'M615', 'M515', 'M612'))) ='TRUE'我所做的是使⽤INSTR函数，我试图找到的是表中的值与任何输⼊值匹配。 如果确实如此，它将返回其索引，即⼤于零。 如果表的值与任何输⼊都不匹配，则它将返回零。 我加总了这个索引，以表明匹配成功。它似乎正在⼯作。希望能帮助到你。Rohan answered 2020-06-26T17:12:08Z1 votes您可以对通配符使⽤⼦查询：SELECT 'Valid Expression'WHERE 'Source Column' LIKE (SELECT '%Column' --FROM TABLE)或者，您可以使⽤单个字符串：SELECT 'Valid Expression'WHERE 'Source Column' LIKE ('%Source%' + '%Column%')Dustin Buschow answered 2020-06-26T17:12:32Z1 votes你甚⾄可以尝试这个功能CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20))RETURNS @Strings TABLE(position int IDENTITY PRIMARY KEY,value varchar(8000))ASBEGINDECLARE @index intSET @index = -1WHILE (LEN(@text) > 0)BEGINSET @index = CHARINDEX(@delimiter , @text)IF (@index = 0) AND (LEN(@text) > 0)BEGININSERT INTO @Strings VALUES (@text)BREAKENDIF (@index > 1)BEGININSERT INTO @Strings VALUES (LEFT(@text, @index - 1))SET @text = RIGHT(@text, (LEN(@text) - @index))ENDELSESET @text = RIGHT(@text, (LEN(@text) - @index))ENDRETURNEND询问select * from my_table inner join (select value from fn_split('M510', 'M615', 'M515', 'M612',','))as split_table on my__name like '%'+split_+'%';SimarjeetSingh Panghlia answered 2020-06-26T17:13:01Z0 votes对于完美的动态解决⽅案，可以通过组合光标和临时表来实现。 使⽤此解决⽅案，您⽆需知道起始位置或长度，并且它是可扩展的，⽽⽆需在您的SQL查询中添加任何OR。对于此⽰例，假设您要从某个表中选择某个ID，详细信息和创建⽇期，该表中的某些⽂本列表位于“详细信息”中。⾸先，在名为Search的列中创建带有搜索字符串的表FilterTable。按照问题初学者的要求：insert into [DATABASE].Tableselect 'M510' unionselect 'M615' unionselect 'M515' unionselect 'M612'然后，您可以按以下⽅式过滤数据：DECLARE @DATA NVARCHAR(MAX)CREATE TABLE #Result (ID uniqueIdentifier, Details nvarchar(MAX), Created datetime)DECLARE DataCursor CURSOR local forward_only FORSELECT '%' + Search + '%'FROM [DATABASE].TableOPEN DataCursorFETCH NEXT FROM DataCursor INTO @DATAWHILE @@FETCH_STATUS = 0BEGINinsert into #Resultselect ID, Details, Createdfrom [DATABASE]. (nolock)where Details like @DATAFETCH NEXT FROM DataCursor INTO @DATAENDCLOSE DataCursorDEALLOCATE DataCursorselect * from #Resultdrop table #Result希望这对您有所帮助Digital Competitive answered 2020-06-26T17:13:43Z