我有一个单元格,其中包含由双管道分隔的值。我正在尝试使用以下内容搜索此单元格的内容(其中 10 是要搜索的数字)%10、10%、%10% 和 10
我的查询似乎只返回 10。 没有其他变体。有人可以告诉我为什么它不起作用吗?
提前非常感谢。(您在下面看到的 SQL 查询是从准备好的 PDO 查询语句导出的内容)
SELECT `Hat`.`id` AS `Hat_id` , `Hat`.`hatCode` AS `Hat_hatCode` , `Hat`.`hatCodeOther` AS `Hat_hatCodeOther` , `Hat`.`name` AS `Hat_name` , `Hat`.`description` AS `Hat_description` , `Hat`.`colorId` AS `Hat_colorId` , `Hat`.`collectionId` AS `Hat_collectionId` , `Hat`.`mainPicture` AS `Hat_mainPicture` , `Hat`.`subPicture` AS `Hat_subPicture` , `Hat`.`type` AS `Hat_type` , `Hat`.`featured` AS `Hat_featured` , `Hat`.`published` AS `Hat_published` , `Hat`.`deleted` AS `Hat_deleted`
FROM `modx_hats` AS `Hat`
WHERE (
(
`Hat`.`published` =1
AND `Hat`.`collectionId` = '1'
)
AND `Hat`.`colorId` = '%||10||%'
OR `Hat`.`colorId` = '10||%'
OR `Hat`.`colorId` = '%||10'
OR `Hat`.`colorId` = '10'
)
LIMIT 0 , 30
你应该使用 LIKE 而不是 =:
AND `Hat`.`colorId` LIKE '%||10||%'
OR `Hat`.`colorId` LIKE '10||%'
OR `Hat`.`colorId` LIKE '%||10'
OR `Hat`.`colorId` = '10'
对于通配车,您应该使用 LIKE
试试这个:
SELECT `Hat`.`id` AS `Hat_id` , `Hat`.`hatCode` AS `Hat_hatCode` , `Hat`.`hatCodeOther` AS `Hat_hatCodeOther` , `Hat`.`name` AS `Hat_name` , `Hat`.`description` AS `Hat_description` , `Hat`.`colorId` AS `Hat_colorId` , `Hat`.`collectionId` AS `Hat_collectionId` , `Hat`.`mainPicture` AS `Hat_mainPicture` , `Hat`.`subPicture` AS `Hat_subPicture` , `Hat`.`type` AS `Hat_type` , `Hat`.`featured` AS `Hat_featured` , `Hat`.`published` AS `Hat_published` , `Hat`.`deleted` AS `Hat_deleted`
FROM `modx_hats` AS `Hat`
WHERE (
(
`Hat`.`published` =1
AND `Hat`.`collectionId` = '1'
)
AND `Hat`.`colorId` LIKE '%||10||%')
OR `Hat`.`colorId` LIKE '10||%'
OR `Hat`.`colorId` LIKE '%||10'
OR `Hat`.`colorId` LIKE '10'
)
LIMIT 0 , 30
你能试试这个吗, 另外,为了获得更好的结果 - 为所有OR
添加了()
,即分组
WHERE (
(
`Hat`.`published` =1
AND `Hat`.`collectionId` = '1'
)
AND ( `Hat`.`colorId` LIKE '%||10||%'
....^
OR `Hat`.`colorId` LIKE '10||%'
OR `Hat`.`colorId` LIKE '%||10'
OR `Hat`.`colorId` = '10'
)
..^
)
LIMIT 0 , 30
看看这个基于你的模式/数据/查询的运行sql小提琴:http://sqlfiddle.com/#!2/48da8/3
您应该使用 LIKE
运算符,并根据AND
和OR
优先级正确放置括号。另外,清理您的查询,它看起来像这样:
SELECT Hat.id AS Hat_id,
Hat.hatcode AS Hat_hatCode,
Hat.hatcodeother AS Hat_hatCodeOther,
Hat.name AS Hat_name,
Hat.description AS Hat_description,
Hat.colorid AS Hat_colorId,
Hat.collectionid AS Hat_collectionId,
Hat.mainpicture AS Hat_mainPicture,
Hat.subpicture AS Hat_subPicture,
Hat.type AS Hat_type,
Hat.featured AS Hat_featured,
Hat.published AS Hat_published,
Hat.deleted AS Hat_deleted
FROM modx_hats AS Hat
WHERE Hat.published = 1
AND Hat.collectionid = '1'
AND ( Hat.colorid LIKE '%||10||%'
OR Hat.colorid LIKE '10||%'
OR Hat.colorid LIKE '%||10'
OR Hat.colorid LIKE '10' )
LIMIT 0, 30
反引号不是必需的,只要不使用受限制的命名字符(如空格字符)。
你想要的是这个:
SELECT
id AS Hat_id,
hatCode AS Hat_hatCode,
hatCodeOther AS Hat_hatCodeOther,
name AS Hat_name,
description AS Hat_description,
colorId AS Hat_colorId,
collectionId AS Hat_collectionId,
mainPicture AS Hat_mainPicture,
subPicture AS Hat_subPicture,
`type` AS Hat_type,
featured AS Hat_featured,
published AS Hat_published,
deleted AS Hat_deleted
FROM modx_hats
WHERE
published = 1
AND
collectionId = '1'
AND (
colorId = '%||10||%'
OR colorId = '10||%'
OR colorId = '%||10'
OR colorId = '10'
)
LIMIT 0, 30
请注意,我从所有表和列定义中删除了反引号,除了保留字"type"。
您需要转义百分比符号
这个问题的答案应该告诉你如何去做。
您也可以创建这样的查询
SELECT * FROM HatTable
WHERE colorId LIKE '%10%'
这应该为您提供包含 10 个的所有选项。
更新
这将为您提供任何包含10
的内容。 所以这可能不是你要找的。
使用上述查询进行选择的示例
3210
||10||
||10
10||
10
1032
年435262 10 32456234