如何在子查询中使用in子句

问题描述：

我有两个表如下，

表 1:

ID      NAME    
------  --------
IM-1    Pencil  
IM-2    Pen     
IM-3    NoteBook
IM-4    Eraser

表 2:

ID      ITEM_ID       
------  --------------
MT-1    IM-1          
MT-2    IM-1,IM-2,IM-3     
MT-3    IM-1,IM-2,IM-4

要求的结果是:

ID      ITEMNAME
------  --------
MT-1    Pencil  
MT-2    Pencil,Pen,NoteBook  
MT-3    Pencil,Pen,Eraser

但是通过使用查询

SELECT T2.ID, 
          (SELECT T1.NAME 
           FROM TABLE1 AS T1 
           WHERE T1.ID IN (T2.ITEM_ID)) AS ITEMNAME 
    FROM TABLE2 AS T2

我得到了结果:

ID      ITEMNAME
------  --------
MT-1    Pencil  
MT-2    (NULL)  
MT-3    (NULL)

有人可以帮我吗?

答

试试这个:

SELECT   b.ID, 
         GROUP_CONCAT(a.Name) as ItemName
FROM     Table2 b INNER JOIN Table1 a
              ON FIND_IN_SET(a.name, b.Item_ID)
GROUP BY b.ID

如何在子查询中使用in子句

相关推荐