MYSQL查询中的循环变量

Question

我有一个 ID 列表，我想循环遍历下面的查询。因此，我希望针对每个 ID 运行查询，并让 ID_FROM_LIST 从 LIST_OF_IDS 中提取下一个 ID。

列表:

LIST_OF_IDS = (379, 517, 519, 797, 800, 896, 897, 900, 902, 903, 904, 905, 906, 907, 919)

查询:

    select w.Record_Count, w.Distinct_Provider_Count from  
(select  c as Record_Count, dc as Distinct_Provider_Count
from
(select * from centene_cic_decodes where standard_use in ('','P','PL')) r left join
(
select 
deceased_status as t, count(*) as c , count(distinct group_key) as dc,  'provider_status' as cat
from t_conditioncodes_0086252_cen_idvf2 
where NET_ID_ef = ID_FROM_LIST
group by 1 
union all 
select 
name_status  as t, count(*) as c , count(distinct group_key) as dc,  'name_status' as cat
from t_conditioncodes_0086252_cen_idvf2  
where NET_ID_ef = ID_FROM_LIST
group by 1
) z
on (if((z.t is null or z.t = ''), 'BLANK',z.t)) = r.Status_Codes and z.cat = r.Category 
order by r.category_rank, r.rank)w ;

目标是为每个 ID 创建单独的输出。因此，一份 379 份报告，然后一份单独的 517 份报告，等等...

(为简单起见，这只是完整查询和 ID 列表的片段。)

Answer 1

如果表中引用了 LIST_OF_IDS 的所有 id，您可以使用 find_in_set 循环遍历逗号分隔的列表(例如:“1,2,3”)，如下所示:

select t.id id_from_list
from mytable t
where find_in_set(t.id, LIST_OF_IDS);