PHP 和 MySQL 子查询 with where id in()

Question

我正在尝试运行此查询:

  $sql = "SELECT p.*, c.id as cid, c.name as catname, p.title as ptitle,"
  . "\n CONCAT(u.first,' ',u.last) as user," 
  . "\n (SELECT GROUP_CONCAT(DISTINCT tags) as tagnames FROM tagtable WHERE id IN (p.tagids))" 
  . "\n FROM posts as p" 
  . "\n LEFT JOIN categories as c ON c.id = a.cid" 
  . "\n LEFT JOIN users as u ON u.id = a.uid" 
  . "\n WHERE a.active = 1 LIMIT 1";

我遇到的唯一问题是子查询

“选择 GROUP_CONCAT(不同标签)...”

由于某种原因，它没有产生任何结果。我期望将标签名称行设置为

"lorem, ipsum, dolores, ..."

字段tag-ids采用1,2,3格式。

Answer 1

如果 tagid 的格式为“1,2,3”，请使用

FIND_IN_SET

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.

所以，类似

WHERE FIND_IN_SET(id,p.tagids) > 0

SQL Fiddle DEMO