mysql - IF 语句中的 SELECT

Question

SELECT jargons.jargon as jargon, 
         jargons.description as description, 
         jargons.example as example,
         IF(jargons.rootJargon != 0, (SELECT jargon FROM jargons WHERE id = jargons.rootJargon), NULL) as rootJargonName
FROM jargons 
LEFT JOIN users ON users.id = jargons.addedBy 
ORDER BY jargons.id DESC

我在行话表中有两行:

"id"  "jargon"  "description"   "example"   "rootJargonName"    "rootJargon"
"2"   "Child"         "jd"           "1"             NULL                1
"1"    Root"          "sad"          "1"             NULL                0

为什么 rootJargonName 在 Child 行上返回 null 而不是 Root？

我试图做的是，如果 rootJargon 列不等于零，则选择列中指定的 ID 不等于零的行。

Answer 1

这可能是您看到的表别名问题，但您可以使用 LEFT JOIN 重写它:

SELECT      J.jargon as jargon, 
            J.description as description, 
            J.example as example,
            R.jargon As rootJargonName
FROM        jargons as J
LEFT JOIN   users   as U ON U.id = J.addedBy 
LEFT JOIN   jargons as R ON J.rootJargon = R.ID
ORDER BY    J.id DESC

如果您想保留 IF() 函数，可以使用以下方法，但我建议使用 LEFT JOIN。

SELECT      J.jargon as jargon, 
            J.description as description, 
            J.example as example,
            IF(J.rootJargon != 0, (SELECT jargon FROM jargons as R WHERE R.id = J.rootJargon), NULL) as rootJargonName
FROM        jargons as J
LEFT JOIN   users   as U ON U.id = J.addedBy 
ORDER BY    J.id DESC