使用以逗号分隔的SQL的ID [英] Using id that are comma separated sql

问题描述

如果您运行此查询

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;

您会收到这样的ID列表

you get a list of id back like this

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

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

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

推荐答案

您可以在其他查​​询中使用它，例如:

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

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

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.

这篇关于使用以逗号分隔的SQL的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！