MYSQL动态表名选择连接和条件

Question

如果我有这个架构

• 列表(lead_id、list_id、campaign_id...)
• 广告事件(campaign_id，...)
• custom_list_id(lead_id，...)

有许多名为 custom_list_id 的表，用于各种 list_id我想要:

SELECT * from lists ls inner join campaign c ON c.id=ls.campaign_id
inner join custom_"@LISTID" cll ON ls.lead_id = cll.lead_id

如何编写这个查询？谢谢

Answer 1

不确定你真正想要什么sql，但试试这个:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'INNER JOIN custom_',  list_id, 'cll', list_id, ' ON ls.lead_id = cll', list_id '.lead_id')
    )
    SEPARATOR ' '
  ) INTO @sql
FROM lists ;
SET @sql = CONCAT('SELECT * from lists ls inner join campaign c ON c.id=ls.campaign_id ', @sql);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;