php - 试图从 mysql 查询创建多维数组，php？

Question

尝试将两个查询组合成数组。看看我的代码，首先，我想让所有的水果都有 cat A 和 val good。其次，我将得到水果匹配第一个查询的标记，cat c 和 val OK 或 bad。最后，我想让它们像$arg = ('苹果'=>'4','香蕉'=>'2','梨'=>'0');因为 pear 没有匹配 val OK 或 bad，所以 0。你有什么办法吗？欣赏。

<?php
/*
fruit  cat     val    mark
apple   A      good    V
apple   c       OK     4
banana  A      good     W
banana  c      bad     2
pear    A      good     W
pear    c      good     8
*/
$val1 = 'OK';
$val2 = 'bad';
$result1 = $wpdb->get_results( $wpdb->prepare("
	SELECT DISTINCT fruit FROM my_table WHERE cat ='A' AND val='%s' ",$val1));

$result2 = $wpdb->get_results( $wpdb->prepare("
	SELECT mark FROM my_table WHERE cat ='c' AND val='%s' AND fruit IN (SELECT DISTINCT fruit FROM my_table WHERE cat ='A' AND val='%s')",$val2,$val1));

/*how can I combine these two queries into an array like 
$arg = ('apple'=>'4','banana'=>'2','pear'=>'0');
because pear did not match, so 0
*/
?>

Answer 1

最简单的解决方案是重写查询，而不是在 php 中手动执行，我认为这会满足您的要求:

SELECT
  t.fruit,
  CASE
    WHEN t.a_mark IS NOT NULL THEN t.a_mark
    WHEN t.c_mark IS NOT NULL THEN t.c_mark
    ELSE '0'
  END mark
FROM
  (SELECT
    t.fruit,
    ( SELECT MAX(mark)
      FROM my_table
      WHERE fruit = t.fruit AND val = 'good' AND cat = 'A'
        AND mark NOT IN ('V', 'W')) a_mark,
    ( SELECT MAX(mark)
      FROM my_table
      WHERE fruit = t.fruit AND val in ('OK', 'bad') AND cat = 'c'
        AND mark NOT IN ('V', 'W')) c_mark  
  FROM
    (SELECT DISTINCT fruit FROM my_table) t
  ) t
ORDER BY
  t.fruit