mysql - UNION 语句中的 WHERE

Question

问题提出:

Code a SQL statement that uses a UNION between the firstname and lastname of the editor and author tables where the 5th through 6th digits of the Social Security number are '72'.

IMPORTANT: Do NOT use the pattern matching characters, '%' and '_' in your answer!!

Also be sure to use a string expression to indicate whether or not someone is an author or editor in the result set.

到目前为止我有这个代码:

SELECT 'editor', firstname, lastname
FROM editor
WHERE ssn=
UNION
SELECT 'author', firstname, lastname
FROM author
WHERE ssn= ;

但是如果我不能使用模式匹配字符“%”和“_”，我应该在 WHERE 语句中添加什么

SSN 在数据库中写入为 123-45-6789

Answer 1

使用 SUBSTRING 可以满足您的解决方案不应包含任何引号或通配符的要求。该函数的第一个参数指定要搜索的表达式，第二个参数指定起始位置 (5)，第三个参数指定子字符串的长度 (2)。

SELECT 'editor', firstname, lastname
FROM editor
WHERE SUBSTRING(ssn, 5, 2) = '72'
UNION
SELECT 'author', firstname, lastname
FROM author
WHERE SUBSTRING(ssn, 5, 2) = '72'