MySQL 选择。如果 WHERE 中的值不存在，则根据另一个(值)WHERE 进行 SELECT

Question

我有这样的mysql表

我有 2 个变量/值，例如 FLASH 和 LCD

我想:如果 FLASH 不存在于列 name 中，则 SELECT ... WHERE 列 name = '液晶屏'。如果FLASH存在，则不选择基于LCD

写了这样的查询

SELECT 

t1.name AS lev1, t1.url AS url1

FROM CategoryForTest AS t1
LEFT JOIN CategoryForTest AS t2 ON t2.parent = t1.category_id
LEFT JOIN CategoryForTest AS t3 ON t3.parent = t2.category_id
LEFT JOIN CategoryForTest AS t4 ON t4.parent = t3.category_id

WHERE exists (
SELECT name
from CategoryForTest
WHERE t4.name = 'FLASH'
)

UNION ALL

SELECT 

t1.name AS lev1, t1.url AS url1

FROM CategoryForTest AS t1
LEFT JOIN CategoryForTest AS t2 ON t2.parent = t1.category_id
LEFT JOIN CategoryForTest AS t3 ON t3.parent = t2.category_id
LEFT JOIN CategoryForTest AS t4 ON t4.parent = t3.category_id

WHERE not exists (
SELECT name
from CategoryForTest
WHERE t4.name = 'FLASH'
)
AND t2.name = 'LCD'

但是我明白了

Array
(
[0] => Array
    (
        [lev1] => ELECTRONICS
        [url1] => url-electronics
    )

[1] => Array
    (
        [lev1] => TELEVISIONS
        [url1] => url-televisions
    )

)

期望只看到 [0] => Array

Answer 1

这可能对你有帮助。

(
   SELECT 

     t1.name AS lev1, t1.url AS url1

   FROM test AS t1
     LEFT JOIN test AS t2 ON t2.parent = t1.categ_id
     LEFT JOIN test AS t3 ON t3.parent = t2.categ_id
     LEFT JOIN test AS t4 ON t4.parent = t3.categ_id

   WHERE not exists (
       SELECT 

         t1.name AS lev1, t1.url AS url1

       FROM test AS t1
         LEFT JOIN test AS t2 ON t2.parent = t1.categ_id
         LEFT JOIN test AS t3 ON t3.parent = t2.categ_id
         LEFT JOIN test AS t4 ON t4.parent = t3.categ_id

      WHERE exists (
         SELECT name
         from test
         WHERE t4.name = 'FLASH'
      )
   )
   AND t2.name = 'LCD'
 GROUP BY t1.categ_id
)
UNION ALL
(
   SELECT 

     t1.name AS lev1, t1.url AS url1

   FROM test AS t1
     LEFT JOIN test AS t2 ON t2.parent = t1.categ_id
     LEFT JOIN test AS t3 ON t3.parent = t2.categ_id
     LEFT JOIN test AS t4 ON t4.parent = t3.categ_id

   WHERE exists (
     SELECT name
     from test
     WHERE t4.name = 'FLASH'
   )
)

文件是 here