在查询中返回与WHERE子句匹配的WHERE子句的列名称
问题描述:
我正在构建一个查询来搜索数据库行中几个字段中的关键字,我想知道的是如果有任何方法返回的结果是在该领域在查询中返回与WHERE子句匹配的WHERE子句的列名称
例如,像一个查询:
SELECT *
FROM table
WHERE field1 LIKE "%keyword%"
OR field2 LIKE "%keyword%"
OR field3 LIKE "%keyword%"
如果关键字出现在field2
我想返回的行以及其他信息标识,这是发现在field2
。这可能吗?
答
但请注意,如果匹配多个字段只会返回第一个。
SELECT *,
CASE WHEN field1 LIKE "%keyword%" THEN 'field1'
WHEN field2 LIKE "%keyword%" THEN 'field2'
WHEN field3 LIKE "%keyword%" THEN 'field3'
ELSE 'no-match'
END as matchField
FROM table
WHERE field1 LIKE "%keyword%"
OR field2 LIKE "%keyword%"
OR field3 LIKE "%keyword%"
答
您可以添加一些列指示哪些字段(S)相匹配的where
条款。没有办法在不将它放入select
的情况下提取这些信息。
SELECT
*,
case when field1 LIKE "%keyword%" then 1 else 0 end as field1_found,
case when field2 LIKE "%keyword%" then 1 else 0 end as field2_found,
case when field3 LIKE "%keyword%" then 1 else 0 end as field3_found,
FROM table
WHERE field1 LIKE "%keyword%"
OR field2 LIKE "%keyword%"
OR field3 LIKE "%keyword%"
答
一个非常简单的方法是这样的:
SELECT 'field1', *
FROM table
WHERE field1 LIKE "%keyword%"
UNION
SELECT 'field2', *
FROM table
WHERE field2 LIKE "%keyword%"
UNION
SELECT 'field3', *
FROM table
WHERE field3 LIKE "%keyword%"
答
一种方法是一起将它们连接起来:
SELECT t.*,
concat_ws(',',
(case when field1 LIKE '%keyword%' then 'field1' end),
(case when field2 LIKE '%keyword%' then 'field2' end),
(case when field3 LIKE '%keyword%' then 'field3' end)
) as matchingFields
FROM table
WHERE field1 LIKE '%keyword%' OR
field2 LIKE '%keyword%' OR
field3 LIKE '%keyword%';
注:这是使用(ANSI标准是个好主意)字符串常量的单引号。
答
你也可以在一个领域做到这一点。
MariaDB []> SELECT
-> concat_ws(', ',
-> IF(field1 LIKE '%keyword%','field1',NULL),
-> IF(field2 LIKE '%keyword%','field2',NULL),
-> IF(field3 LIKE '%keyword%','field3',NULL)
-> ) AS found_in, s.*
-> FROM searchtable s
-> WHERE field1 LIKE '%keyword%'
-> OR field1 LIKE '%keyword%'
-> OR field1 LIKE '%keyword%';
+------------------------+----+---------+---------+---------+
| found_in | id | field1 | field2 | field3 |
+------------------------+----+---------+---------+---------+
| field1 | 1 | keyword | NULL | NULL |
| field1, field3 | 4 | keyword | NULL | keyword |
| field1, field2 | 6 | keyword | keyword | NULL |
| field1, field2, field3 | 7 | keyword | keyword | keyword |
+------------------------+----+---------+---------+---------+
4 rows in set (0.01 sec)
MariaDB []>
没有直接关系,但您可能对FULLTEXT搜索感兴趣 – Mihai