MySQL的SELECT凡在LIST而不是在同一个SQL LIST [英] MySQL SELECT WHERE IN LIST and NOT IN LIST in the same SQL

问题描述

我有这样的：

  $ IDS =1,2,3,4,5;
    $的SQLQuery =SELECT ID，moderation_date
                    从table_live
                    WHERE ID IN（$ IDS。）;    $ Q = $这个 - ＆GT; CI-＆GT; DB-GT＆;查询（$的SQLQuery）;    如果（$ Q-＆GT; NUM_ROWS（）大于0）{
        的foreach（$ Q-＆GT;的结果（）为$行）{
            $常用3 [] = $行;
        }
    }    返回$ ARR;
 

这仅仅是工作的罚款，如果在table_live存在的所有ID
并返回

 阵列（[ID] =＆GT; 1 [moderation_date] =＆GT; 2012-04-11 12点55分57秒）......
 

问题：如果我把IDS 1-2-3-4-5其中只有1-2-5列表中所列条款的比赛名单
我需要返回所有在列表，并为那些不匹配列表中的一个空值。

 阵列（[ID] =＆GT; 3 [moderation_date] =＆GT;空）
 

解决方案

生成一个外部联接语句，这样您可以：

  SELECT ids.id，table_live.moderation_date
FROM（选择1号联盟的所有选择2 UNION ALL ....）IDS
LEFT JOIN table_live
ON ids.id = table_live.id
 

其中IDS是一个子查询枚举所有的价值观，是这样的：

  $ IDS ='1,2,3,4,5'
$子查询='选择'.str_replace（'，'，'ID联盟的所有选择'，$ IDS）。''
$的SQL =SELECT ids.id，table_live.moderation_date
FROM（$子查询）IDS
LEFT JOIN table_live
ON ids.id = table_live.id
 

请务必选择 ids.id ，而不是 table_live.id 。这样，IDS总会出现，而moderation_date只有对应的行table_live存在。

另一种方法是保持查询，你曾经有过，结果存储在一个数组，然后让你保留所有的键合并数组在PHP，并填写值仅其中两个数组的关键比赛。

我真的不知道你用什么样的DB库，所以我不知道如何获取结果集的数组，但假设你会使用一个字符串再$存储在一个PHP数组中的行，该id作为键，日期值的对$ psentation，那么这个code应该做的伎俩：

  $项目=阵列（
    '1'=＆GT;空值
，'2'= GT;空值
...
）;
//注意：为了合并使用字符串键！
$结果= array_merge（$项目，$结果集）;
 

请参阅： http://php.net/manual/en/function。阵列merge.php

I have this:

    $ids = "1,2,3,4,5";
    $sqlQuery = "SELECT id, moderation_date
                    FROM table_live
                    WHERE id IN (".$ids.")";

    $q = $this->CI->db->query($sqlQuery);

    if($q->num_rows() > 0) {
        foreach ($q->result() as $row) {
            $arr[] = $row;
        }
    }

    return $arr;

This is just working fine if all ids exist in table_live and return

           array([id] => 1 [moderation_date] => 2012-04-11 12:55:57)....

The problem: If I send a list of ids 1-2-3-4-5 where only 1-2-5 match the IN LIST clause I need to return all in list and for those don't match the list a null value.

           array([id] => 3 [moderation_date] => null) 

解决方案

generate an outer join statement so that you get:

SELECT ids.id, table_live.moderation_date
FROM (select 1 id union all select 2 union all ....) ids
LEFT JOIN table_live
ON ids.id = table_live.id

where ids is a subquery enumerating all the values, something like this:

$ids = '1,2,3,4,5'
$subquery = 'select '.str_replace(',', ' id union all select ', $ids).''
$sql = "SELECT ids.id, table_live.moderation_date
FROM ($subquery) ids
LEFT JOIN table_live
ON ids.id = table_live.id"

be sure to select ids.id, not table_live.id. That way, the ids will always show up, and the moderation_date only if the corresponding row exists in table_live.

Another approach would be to keep the query as you had it, store the result in an array, and then merge the arrays in php so that you retain all keys, and fill in the values only where the key matches in both arrays.

I am not really sure what kind of db library you're using so I don't know how to obtain an array of the resultset, but suppose you would have stored the rows in a php array, using a string representation of the id as key, and the date as value, then this code should do the trick:

$items = array(
    '1' => NULL
,   '2' => NULL
,   ...
); 
//note: use string keys in order to merge!!
$result = array_merge($items, $resultset);

see: http://php.net/manual/en/function.array-merge.php

这篇关于MySQL的SELECT凡在LIST而不是在同一个SQL LIST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！