使用%检查字符串的MySql查询

Question

我有两张桌子，即queues和indexqueuemaps。

表解题

• Qid
• 名字

表indexqueuemaps

• PhysicalQueueId
• ConditionFieldValue

来自queses的数据

Id     Name
1      Pricing
2      Return
3      EDI

来自indexqueuemaps的数据

PhysicalQueueId      ConditionFieldValue
1                    Member not on file.
2                    Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 17 long and the Max Length is 8
3                    Data is too long for MC/400 Field in XML Element: 0001008077. Data is % long and the Max Length is 9

这是我的查询

SELECT `Name` FROM queues WHERE Id  IN 
(SELECT PhysicalQueueId FROM indexqueuemaps 
WHERE ConditionFieldValue = 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 17 long and the Max Length is 8');

当我运行这个查询时，它会返回名称= Return。我想在这里做的是如果我检查CoditionFieldValue = Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 171 long and the Max Length is 8

而不是

Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 17 long and the Max Length is 8

当我运行查询时，它不会返回任何值，因为表中没有任何ConditionFieldValue。我要检查的是，这种情况是否发生，如果存在Data is too long for MC/400 Field in XML Element: 0001008077. Data is % long and the Max Length is 9，表查询中的此值应返回EDI。

假设我的查询是

SELECT `Name` FROM queues WHERE Id  IN 
(SELECT PhysicalQueueId FROM indexqueuemaps 
WHERE ConditionFieldValue = 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 171 long and the Max Length is 8');

查询的结果应该是名称= EDI

我如何实现这一点？

编辑

我尝试过这样的查询：

Set @msg = 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is 171 long and the Max Length is 8';

SELECT q.名称FROM queues q inner join indexqueuemaps iq on q.Id = iq.PhysicalQueueId WHERE (iq.ConditionFieldValue = @msg) OR (iq.ConditionFieldValue != @msg AND @msg like 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is %' and iq.ConditionFieldValue like '% long and the Max Length is 8');

在这个查询中，我要检查

@msg样的数据对于XML元素中的MC/400Field来说太长了: 20111109-20111113。数据为%，iq.ConditionFieldValue为'%长，最大长度为8‘

我正在用硬编码字符串检查它。但是，我想用表中的匹配值来检查它，该表在%之前包含文本，在%之后包含。简单地说，我想从包含ConditionFieldValue %符号的列中拆分字符串。

Answer 1

我认为您正在寻找Id列和PhysicalQueueId列上的两个表之间的连接。

SELECT q.`Name` FROM queues q inner join indexqueuemaps iq on q.Id = iq.PhysicalQueueId 
WHERE 
(iq.ConditionFieldValue = @msg) OR 
(iq.ConditionFieldValue != @msg AND @msg like 'Data is too long for MC/400 Field in XML Element: 20111109-20111113. Data is %' 
and @msg like '% long and the Max Length is 8')