我有以下MySQL查询:
SELECT id, name, parent_id AS pID,
(SELECT parent_id
FROM category
WHERE id = pID)
AS grandparent_id,
(SELECT parent_id
FROM category
WHERE id = grandparent_id)
AS greatgrandparent_id,
(SELECT name
FROM category
WHERE id = pID)
AS parent,
(SELECT name
FROM category
WHERE id = grandparent_id)
AS grandparent,
(SELECT name
FROM category
WHERE id = greatgrandparent_id)
AS greatgrandparent
FROM category
WHERE active = '1'
HAVING grandparent IS NOT NULL
ORDER BY grandparent_id, parent_id, sort, id ASC
当greatgrandparent
为NULL时,我希望它显示grandparent
值。这可能吗?
我已经尝试过CASE WHEN
,但必须得到语法错误的地方,因为它不断抛出一个错误。
简单解:IFNULL(greatgrandparent, grandparent)
。
如果没有组by子句,就不能有having子句。
也许这段代码对你有帮助。
SELECT id, name, parent_id AS pID当祖父母不为空时(SELECT parent_id FROM category WHERE id = grandparent_id)其他的(SELECT parent_id FROM category WHERE id = pID)
以_parent_id结束,当祖父母不为空时(SELECT name FROM category WHERE id = grandparent_id)其他的(SELECT name FROM category WHERE id = pID)end AS _parent_nameFROM category WHERE active = '1'
我将使用带有别名和合并的左连接来重写它。鉴于这种
/*
CREATE TABLE CATEGORY(ID INT, NAME VARCHAR(20), PARENT_ID INT);
TRUNCATE TABLE CATEGORY;
INSERT INTO CATEGORY VALUES
(1,'AAAA',2),(2,'AAA',3),(3,'AA',4),(4,'A',5),
(10,'BBBB',11),(11,'BBB',12)
;
*/
这段代码SELECT C.ID,C.NAME,C.PARENT_ID ,
CP.NAME AS PARENTNAME,CP.PARENT_ID AS PARENT_PARENT_ID,
CG.NAME AS GRANDPARENTNAME,
CG.PARENT_ID AS GRANDPARENT_PARENT_ID,
CGG.NAME AS GREATGRANDPARENTNAME ,
CGG.PARENT_ID AS GREATGRANDPARENT_PARENT_ID
FROM CATEGORY C
LEFT JOIN CATEGORY CP ON CP.ID = C.PARENT_ID
LEFT JOIN CATEGORY CG ON CG.ID = CP.PARENT_ID
LEFT JOIN CATEGORY CGG ON CGG.ID = CG.PARENT_ID
ORDER BY C.ID;
在 搜索结果
+------+------+-----------+------------+------------------+-----------------+-----------------------+----------------------+----------------------------+
| ID | NAME | PARENT_ID | PARENTNAME | PARENT_PARENT_ID | GRANDPARENTNAME | GRANDPARENT_PARENT_ID | GREATGRANDPARENTNAME | GREATGRANDPARENT_PARENT_ID |
+------+------+-----------+------------+------------------+-----------------+-----------------------+----------------------+----------------------------+
| 1 | AAAA | 2 | AAA | 3 | AA | 4 | A | 5 |
| 2 | AAA | 3 | AA | 4 | A | 5 | NULL | NULL |
| 3 | AA | 4 | A | 5 | NULL | NULL | NULL | NULL |
| 4 | A | 5 | NULL | NULL | NULL | NULL | NULL | NULL |
| 10 | BBBB | 11 | BBB | 12 | NULL | NULL | NULL | NULL |
| 11 | BBB | 12 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+-----------+------------+------------------+-----------------+-----------------------+----------------------+----------------------------+
和这个代码
SELECT C.ID,C.NAME,C.PARENT_ID ,
CP.NAME AS PARENTNAME,CP.PARENT_ID AS PARENT_PARENT_ID,
COALESCE(CG.NAME,CP.NAME) AS GRANDPARENTNAME,
COALESCE(CG.PARENT_ID,CP.PARENT_ID) AS GRANDPARENT_PARENT_ID,
COALESCE(CGG.NAME,CG.NAME) AS GREATGRANDPARENTNAME ,
COALESCE(CGG.PARENT_ID,CG.PARENT_ID) AS GREATGRANDPARENT_PARENT_ID
FROM CATEGORY C
LEFT JOIN CATEGORY CP ON CP.ID = C.PARENT_ID
LEFT JOIN CATEGORY CG ON CG.ID = CP.PARENT_ID
LEFT JOIN CATEGORY CGG ON CGG.ID = CG.PARENT_ID
ORDER BY C.ID
在 搜索结果
+------+------+-----------+------------+------------------+-----------------+-----------------------+----------------------+----------------------------+
| ID | NAME | PARENT_ID | PARENTNAME | PARENT_PARENT_ID | GRANDPARENTNAME | GRANDPARENT_PARENT_ID | GREATGRANDPARENTNAME | GREATGRANDPARENT_PARENT_ID |
+------+------+-----------+------------+------------------+-----------------+-----------------------+----------------------+----------------------------+
| 1 | AAAA | 2 | AAA | 3 | AA | 4 | A | 5 |
| 2 | AAA | 3 | AA | 4 | A | 5 | A | 5 |
| 3 | AA | 4 | A | 5 | A | 5 | NULL | NULL |
| 4 | A | 5 | NULL | NULL | NULL | NULL | NULL | NULL |
| 10 | BBBB | 11 | BBB | 12 | BBB | 12 | NULL | NULL |
| 11 | BBB | 12 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+-----------+------------+------------------+-----------------+-----------------------+----------------------+----------------------------+