选择inside IF语句

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

在jargons表中有两行：

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

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

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

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

Answer 2

你为什么不试个案子

像这样：

SELECT jargons.jargon as jargon, 
     jargons.description as description, 
     jargons.example as example,
     CASE 
         WHEN (jargons.rootJargon == 0) THEN NULL
         WHEN (jargons.rootJargon != 0) THEN
         (SELECT jargon FROM jargons WHERE id = jargons.rootJargon)
     END) AS 'rootJargonName'
FROM jargons 
LEFT JOIN users ON users.id = jargons.addedBy 
ORDER BY jargons.id DESC

我可能在语法上错了，但我想这就是我的想法.