嗨,我想将我的registry_ug表与基于registry_ug.type的第二个表联接。...我想避免不必要的联接。

这是我的代码

   SELECT  ug.id , tbl.id FROM registry_ug ug
      JOIN    CASE ug.type
             WHEN 1   THEN  users
             WHEN 2   THEN  group_software
       END
       AS  tbl
       ON
       ug.id = tbl.id

     WHERE ug.id = $uid


对我来说似乎还可以,但我收到此错误

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 ug.type WHEN 1 THEN users WHEN 2 THEN g' at line 2

SELECT ug.id , tbl.id FROM registry_ug ug JOIN CASE ug.type WHEN 1 THEN users WHEN 2 THEN group_software END AS tbl ON ug.id = tbl.id WHERE ug.id = 55


*而且我不想将其加入所有可能的第二张桌子*

最佳答案

您不能以这种方式这样做。您不能以这种方式使用CASE表达式。尝试以下方法:

SELECT
  ug.id,
  COALESCE(u.id, g.id)
FROM registry_ug AS ug
LEFT JOIN users          AS u ON ug.id = u.id AND ug.type = 1
LEFT JOIN group_software AS g ON ug.id = g.id AND ug.type = 2
WHERE ug.id = $uid

关于mysql - 根据第一表列与第二表联接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/14877022/

10-11 08:04