MySql 查询用 % 检查字符串

Question

我有两个表，即 queues和indexqueuemaps .

表格问题:

• Qid
• 姓名

表indexqueuemaps

• 物理队列ID
• 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 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'

我正在用硬编码字符串检查它。但我想用表中包含文本 before % 和 after % 的匹配值来检查它。简单我想从包含 % 符号的 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')