sql - 使用 WHERE 和 OR 术语时如何知道哪个匹配

Question

我有如下的sql代码:

SELECT user_name, user_e_mail, user_phone 
    FROM users 
WHERE user_name=:user_name 
OR user_e_mail=:email 
OR user_phone=:phone

通常，我会尝试获取 0 或 1(无论是否匹配)。但我不想创建更多的 sql 查询。很快，我的问题是如何知道哪一个匹配(姓名、邮件或电话)？

我希望我能说出我的问题是什么..

Answer 1

这个的标准 SQL 语法将为每个条件返回一个单独的标志:

SELECT user_name, user_e_mail, user_phone,
       (case when user_name = :user_name then 1 else 0 end) as MatchesName,
       (case when user_e_mail = :user_e_mail then 1 else 0 end) as MatchesEmail,
       (case when user_phone = :user_phone then 1 else 0 end) as MatchesPhone
FROM users 
WHERE user_name=:user_name OR user_e_mail=:email OR user_phone=:phone;

如果您只想知道第一个匹配的:

SELECT user_name, user_e_mail, user_phone,
       (case when user_name = :user_name then 'Name'
             when user_e_mail = :user_e_mail then 'Email'
             when user_phone = :user_phone then 'Phone'
        end) as which
FROM users 
WHERE user_name=:user_name OR user_e_mail=:email OR user_phone=:phone;