假设我的表包含以下列
Roll_number numeric not null
Subject1 varchar(40) null
Subject2 varchar(40) null
. . .
Subject8 varchar(40) null
我必须使用SQL查询在所有这些主题1到8中搜索一个主题。在所有这些列中搜索子字符串搜索的最佳查询是什么,请记住,其中任何一个都可以具有无效的值。
我写了
select *
from students_data
where subject1="BIOLOGY" or subject2="BIOLOGY" . . . . or subject8="BIOLOGY"
但是我需要一个简单的查询,因为实际上我的表中有20列以上
查询
select * from table1 where "BIOLOGY" in (subject1,subject2,subject3)
在 " bio" 在该表字段
您可以像这样使用IN
谓词:
SELECT *
FROM students_data
WHERE 'BIOLOGY' IN (subject1, subject2, . . . , subject8);
怎么样?
SELECT *
FROM students_data
WHERE subject1 + ' ' + subject2 + ' ' + ..... + subject8 LIKE '%BIOLOGY%';
更新1
对于 null 值,在下面使用。
SELECT *
FROM students_data
WHERE ISNULL(subject1,'') + ' ' + ISNULL(subject2,'')
+ ' ' + ..... + ISNULL(subject8,'') LIKE '%BIOLOGY%';
用于使用Bio,您可以使用LIKE '%BIO%';
这可能不会更快,但我相信它更清楚。
首先创建标准化视图(您可以以这种方式存储数据 - 这是理想的)
Create View SubjectData AS
(
SELECT Roll_number, 1 AS SubjectNumber, Subject1 AS Subject FROM TABLE1
UNION ALL
SELECT Roll_number, 2 AS SubjectNumber, Subject2 AS Subject FROM TABLE1
UNION ALL
SELECT Roll_number, 3 AS SubjectNumber, Subject3 AS Subject FROM TABLE1
UNION ALL
SELECT Roll_number, 4 AS SubjectNumber, Subject4 AS Subject FROM TABLE1
UNION ALL
SELECT Roll_number, 5 AS SubjectNumber, Subject5 AS Subject FROM TABLE1
UNION ALL
SELECT Roll_number, 6 AS SubjectNumber, Subject6 AS Subject FROM TABLE1
UNION ALL
SELECT Roll_number, 7 AS SubjectNumber, Subject7 AS Subject FROM TABLE1
UNION ALL
SELECT Roll_number, 8 AS SubjectNumber, Subject8 AS Subject FROM TABLE1
)
现在选择很简单:
SELECT Roll_number, SubjectNumber from SubjectData where CONTAINS(Subject,'Bio')
n.b。注意包含的使用,应更快地做。
我没有测试上述查询,它们可能具有错别字。
在这种情况下,
SELECT *
FROM students_data
WHERE
nvl(subject1,'') + ' ' + nvl(subject2,'') + ' ' + ..... + nvl(subject8,'')
LIKE '%BIO%';
如果您的数据库不支持,则可以使用ifnull()
或decode()