使用逗号分隔的sql id

If you run this query

如果运行此查询

select     GROUP_CONCAT(p1.id) _fid,    GROUP_CONCAT(p2.id) _mid,    count(1)from    new_person as pb        inner join    new_person as p1 ON pb.father_id = p1.id        inner join    new_person as p2 ON pb.mother_id = p2.idwhere    (p1.last_name <> 'N.N.'        and p1.last_name <> '')        or (p2.last_name <> 'N.N.'        and p2.last_name <> '')group by p1.first_name , p1.last_name , p2.first_name , p2.last_namehaving count(1) > 1;

you get a list of id back like this

你会得到一个id列表

'4676,6088,4804,4968,6554,5212,5504,5810,7298,7782,6970'

Is there a way so you can immediately use it in a other query or do you have to load it in php first?

是否有一种方法可以立即在其他查询中使用它，还是必须先用php加载它?

1 个解决方案

#1

You can use it in another query like so:

您可以在另一个查询中使用它，如:

SELECT M.*, P.* FROM (    SELECT GROUP_CONCAT(p1.id) _fid, GROUP_CONCAT(p2.id) _mid, count(1)  FROM new_person AS pb     INNER JOIN new_person AS p1 ON pb.father_id = p1.id     INNER JOIN new_person AS p2 ON pb.mother_id = p2.id  WHERE (     p1.last_name <> 'N.N.'     AND p1.last_name <> '')     OR (p2.last_name <> 'N.N.'     AND p2.last_name <> '')  GROUP BY p1.first_name, p1.last_name, p2.first_name, p2.last_name  HAVING COUNT(1) > 1) AS M INNER JOIN new_person as p ON M._fid = p.id

Notice I added the entire query to the from statement with the alias as M. You can then JOIN M to another table or do whatever you want from there.

注意，我将整个查询添加到from语句中，别名为M。您可以将M连接到另一个表，或者从那里做任何您想做的事情。

#1