将搜索参数显示为列

Question

目前，我面临一个问题，要将搜索参数显示在'Like‘语句中，作为一个新列。我的SQL代码如下：

 Select A.Description from table A Where A.Description like ('%Battery%')

产出：

Description 
12V Battery
 9V Battery
 5V Battery

我想要的输出是：

Description   Description_1
12V Battery   Battery
 9V Battery   Battery
 5V Battery   Battery

这是不可能的吗？

Answer 1

类似于：

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE table_name ( Description ) AS
          SELECT '9V Battery' FROM DUAL
UNION ALL SELECT '12V Battery' FROM DUAL
UNION ALL SELECT '24V Battery' FROM DUAL
UNION ALL SELECT 'Torch' FROM DUAL
UNION ALL SELECT 'Rope' FROM DUAL;

查询1

WITH search_terms AS (
            SELECT 'Battery' AS search_term FROM DUAL
  UNION ALL SELECT 'Torch' FROM DUAL
)
SELECT A.Description,
       s.search_term AS Description_1
FROM   table_name A
       INNER JOIN
       search_terms s
       ON ( A.Description LIKE '%' || search_term || '%' )

结果

| DESCRIPTION | DESCRIPTION_1 |
|-------------|---------------|
|  9V Battery |       Battery |
| 12V Battery |       Battery |
| 24V Battery |       Battery |
|       Torch |         Torch |

或者，如果只使用单个搜索项，则可以使用绑定变量替换命名子查询。

SELECT A.Description,
       :search_term AS Description_1
FROM   table_name A
WHERE  A.Description LIKE '%' || :search_term || '%'

Answer 2

把它包含在SELECT语句中怎么样？

SELECT  A.Description ,
       'Battery' AS Description_1
FROM    table A 
WHERE   A.Description LIKE '%Battery%'

Answer 3

这个查询如何：

with tab as
(select 'Battery' FILTER
   from dual)
Select A.Description, B.filter
  from table_name A, tab B 
 Where A.Description like '%'|| b.filter ||'%';

输出

| DESCRIPTION |  FILTER |
|-------------|---------|
|  9V Battery | Battery |
| 12V Battery | Battery |
| 24V Battery | Battery |

将筛选器放入查询select 'Battery' FILTER from dual中。