MySQL：在单个查询中从数据库和PHP数组中获取值

J.K 发表于 Dev

混合菜单

混合菜单

SELECT mixed.unique_id, 
MENU_LABEL 
      case 
          when (--something...)
FROM mixed_menu mixed 
ORDER BY mixed.position ASC

问题：我想编写一个mysql查询，该查询可以在以下情况下检索unique_id，MENU_LABEL。

情况1：

如果类型=='db_category'，则从数据库（1.db_category）中检索标签，其中id = unique_id
情况2：

如果type =='category'，则从php数组（2.PHP Array）中检索数组值，其中array key = unique_id

1. db_category

db_category

2. PHP数组

$category_array = array('car'  => 'All Cars', 
                        'jeep' => 'All Jeeps'
                       );

肖恩

如果用php数组创建一个新表会更容易。

但是使用您当前的结构，您可以创建一个临时/伪表，如下所示

SELECT * FROM (
  SELECT 'car' as `id`, 'All Cars' as `label`
  UNION
  SELECT 'jeep' as `id`, 'All Jeeps' as `label`
) php

因此，您可以使用类似-

SELECT mm.unique_id,

CASE
  WHEN mm.type = 'db_category' THEN dbc.label
  WHEN mm.type = 'category' THEN php.label
END as menu_label

FROM mixed_menu mm

LEFT JOIN db_category dbc ON mm.unique_id = dbc.id

LEFT JOIN (
  /*Create fake table*/
  SELECT 'car' as `id`, 'All Cars' as `label`
  UNION
  SELECT 'jeep' as `id`, 'All Jeeps' as `label`
) php ON mm.unique_id = php.id

ORDER BY mm.position ASC

这里是一个SQLFiddle例子- http://sqlfiddle.com/#!2/5630cc/5

然后，您需要使用您的php数组构建该假表。

编辑

由于mixed_menu和db_category表是COLLATION latin1_swedish_ci，并且SELECT UNION默认使用using ，因此COLLATION utf8_general_ci您需要在声明SELECT UNION

SELECT * FROM (
  SELECT 'car' COLLATE latin1_swedish_ci as `id`, 'All Cars' COLLATE latin1_swedish_ci as `label`
  UNION
  SELECT 'jeep' COLLATE latin1_swedish_ci as `id`, 'All Jeeps' COLLATE latin1_swedish_ci as `label`
) php

所以现在查询将是

SELECT mm.unique_id,

CASE
  WHEN mm.type = 'db_category' THEN dbc.label
  WHEN mm.type = 'category' THEN php.label
END as menu_label

FROM mixed_menu mm

LEFT JOIN db_category dbc ON mm.unique_id = dbc.id

LEFT JOIN (
  /*Create fake table*/
  SELECT 'car' COLLATE latin1_swedish_ci as `id`, 'All Cars' COLLATE latin1_swedish_ci as `label`
  UNION
  SELECT 'jeep' COLLATE latin1_swedish_ci as `id`, 'All Jeeps' COLLATE latin1_swedish_ci as `label`
) php ON mm.unique_id = php.id

ORDER BY mm.position ASC