mysql - ELT() 使用子查询而不是参数

Question

整个查询:

SELECT NULL, 
ELT( MOD( A.contact_id, (SELECT count(*) FROM  `ak_contact` WHERE  `contact_type_id` = 12) )+1, (SELECT  CONCAT( "'",GROUP_CONCAT(CAST(contact_id  AS CHAR(7)) SEPARATOR "','"),"'"  )  FROM  `ak_contact` WHERE  `contact_type_id` = 12) ) as operator_id,
B.account_id, A.contact_id, A.email
FROM ak_contact A
JOIN ak_accounts B USING (contact_id)
GROUP BY A.email, A.contact_id, B.account_id
ORDER BY A.contact_id DESC

子查询应该从ids 10493, 10495,11262,11800,12362,13405中选择一个，每行一个一个:

ELT( MOD( A.contact_id, (SELECT count(*) FROM  `ak_contact` WHERE  `contact_type_id` = 12) )+1, (SELECT  CONCAT( "'",GROUP_CONCAT(CAST(contact_id  AS CHAR(7)) SEPARATOR "','"),"'"  )
#result: ELT(MOD(3333, 5)+1, '10493','10495','11262','11800','12362','13405')   

问题是我不能强制使用 ELT(A.contact_id, subquery) 和子查询。或者我如何制作这样的东西:

ELT(MOD(3333, 5)+1, '10493','10495','11262','11800','12362','13405')

在主查询中

Answer 1

您可以使用 FIND_IN_SET 和一个子查询，该子查询使用 GROUP_CONCAT 将所有值作为逗号分隔的字符串“10493,10495,11262,...”提供。

(但请注意，GROUP_CONCAT 可以返回的字符串大小受服务器配置限制。)