mysql - 试图选择第一个包含我名字的完整行

Question

我正在尝试进行查询，以选择完全填满的第一行，并且输入值位于该行的某个位置，否则它不会提取任何内容。这是我到目前为止所做的:

CASE WHEN `name_1` != '' and `name_2` != '' and `name_3` != ''  and `name_4` = 'Jimmy' THEN (SELECT`name_1`,`name_2`,`name_3`,`name_4` FROM `names`) end
CASE WHEN `name_1` != '' and `name_2` != '' and `name_4` != ''  and `name_3` = 'Jimmy' THEN (SELECT`name_1`,`name_2`,`name_3`,`name_4` FROM `names`) end
CASE WHEN `name_1` != '' and `name_4` != '' and `name_3` != ''  and `name_2` = 'Jimmy' THEN (SELECT`name_1`,`name_2`,`name_3`,`name_4` FROM `names`) end
CASE WHEN `name_4` != '' and `name_2` != '' and `name_3` != ''  and `name_1` = 'Jimmy' THEN (SELECT`name_1`,`name_2`,`name_3`,`name_4` FROM `names`) end

返回错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN name_1 != '' and name_2 != '' and name_3 != '' and name_4 = '' at line 1

那么明明写错了，那到底是哪里出了问题呢？我这样做的方式正确吗？

Answer 1

这不是使用 CASE 的正确方法。我想你想选择 JIMMY 可能出现在一个或多个列上的行，并且任何列都不能为空，

SELECT  `name_1`,`name_2`,`name_3`,`name_4` 
FROM    `names`
WHERE   `name_1` IS NOT NULL AND 
        `name_2` IS NOT NULL AND 
        `name_3` IS NOT NULL AND 
        `name_4` IS NOT NULL AND
        (
            `name_1` = 'JIMMY' OR 
            `name_2` = 'JIMMY' OR 
            `name_3` = 'JIMMY' OR 
            `name_4` = 'JIMMY' 
        )

我认为这个解决方案比使用CASE要短得多。