按照IN子句的顺序取记录 - 在IN子句中有重复的值 [英] fetch record in order with IN clause - having duplicate values in IN clause
问题描述
我有表格问题：
id |问题| question_level
______________________________________
1 | abc | 1
______________________________________
2 | prs | 3
______________________________________
3 | oesl | 2
______________________________________
4 | ocsl | 3
______________________________________
5 | qoindos | 1
______________________________________
6 | xyz | 3
______________________________________
7 | mnlop | 2
______________________________________
8 | cllse | 2
______________________________________
9 | teuosn | 4
______________________________________
10 | ulcd | 2
______________________________________
我想选择10个记录，与问题级别$ b $匹配b我有要求的所有记录的级别顺序如下
1,2,1,2,3,2， 4,2,3,3
输出应该是
id |问题| question_level
______________________________________
1 | abc | 1
______________________________________
3 | oesl | 2
______________________________________
5 | qoindos | 1
______________________________________
7 | mnlop | 2
______________________________________
2 | prs | 3
______________________________________
8 | cllse | 2
______________________________________
9 | teuosn | 4
______________________________________
10 | ulcd | 2
______________________________________
4 | ocsl | 3
______________________________________
6 | xyz | 3
______________________________________
我尝试了不同的解决方案，但无法正确输出
尝试与字段，find_in_set但没有成功。
参考[强制MySQL从WHERE IN子句中返回重复项，而不使用JOIN / UNION？
但是只有按顺序计数和记录
我在下面的解决方案尝试
SELECT question_level从`tbl_questions` where'question_level` IN（1,2,1,2,3 ，2,4,2,3,3）ORDER BY FIELD（`question_level`，1，2，3，2，4，2，3，3）限制10
SELECT question_level FROM tbl_questions WHERE FIND_IN_SET（`question_level`，'1,2,1,2,3,2,4,2,3,3'）;
SELECT question_level，question_object_name，question_object_path，question_answer
FROM tbl_questions e JOIN（SELECT 1 AS question_level UNION ALL
SELECT 2 UNION ALL
SELECT 1 UNION ALL
选择2所有
选择3所有
选择2所有
选择4所有
选择2所有
选择3所有
选择3
）匹配
USING（question_level）LIMIT 10;
我也使用了foreach循环，但是每次获得相同的记录时问题级别与值匹配
$ array = explode（'，'，'1,2,1,2,3,2,4,2,3,3 '）
foreach（$ array as $ value）{
SELECT question_level FROM tbl_questions
WHERE question_level = $ value;
$ b如果在mysql中不可能，那么可以用php来实现。 / p>
提前致谢
解决方案 p>
$ sequence =1,2,1,2,3,2,4,2,3,3;
$ seq_arr = explode（'，'，$ sequence）;
第1步：
创建数组以存储所有详细信息
$ questions = array（）; //存储所有问题
$ final_questions = array（）; //按照需要的顺序存储最终的问题
$ level_1 = array（）; // level 1 question
$ level_2 = array（）; // 2级问题
$ level_3 = array（）; // 3级问题
$ level_4 = array（）; // 4级问题
$ level_5 = array（）; //第5级问题
第2步：
从Db中选择所有数组，并根据问题级别存储在每个数组中
$这 - > DB->选择（*）;
$ this-> db-> from（questions）;
$ query = $ this-> db-> get（）;
$ result = $ query-> result_array（）; $ $ b $ for（$ i = 0; $ i
{
$ level_1 [] = $ result [$ i];
if（$ result [$ i] ['question_level'] == 2）
{
$ level_2 [] = $ result [$ i];
if（$ result [$ i] ['question_level'] == 3）
{
$ level_3 [] = $ result [$ i];
if（$ result [$ i] ['question_level'] == 4）
{
$ level_4 [] = $ result [$ i];
if（$ result [$ i] ['question_level'] == 5）
{
$ level_5 [] = $ result [$ i];
lockquote
步骤3：
匹配所有数组的序列并存储到最终数组中。
（$
） foreach（$ seq_arr as $ key => $ value）{
if（$ value == 1）
{
for （$ set）（$ level_1 [$ i]）&&！empty（$ level_1 [$ i]）i = 0; $ i
if（！in_array（$ level_1 [$ i]，$ final_questions））
{
$ final_questions [] = $ level_1 [$ i];
break; $
$ if（$ value == 2）
{
for（$ i = 0; $ i < count（$ difficulty_array）; $ i ++）
{
if（isset（$ level_2 [$ i]）&！empty（$ level_2 [$ i]））{
if（！in_array（$ level_2 [$ i]，$ final_questions））
{
$ final_questions [] = $ level_2 [$ i];
break; $
$ if（$ value == 3）
{
for（$ i = 0; $ i < count（$ difficulty_array）; $ i ++）
{
if（isset（$ level_3 [$ i]）&&！empty（$ level_3 [$ i]））{
if（！in_array（$ level_3 [$ i]，$ final_questions））
{
$ final_questions [] = $ level_3 [$ i];
break; $
$ if（$ value == 4）
{
for（$ i = 0; $ i < count（$ difficulty_array）; $ i ++）
{
if（isset（$ level_4 [$ i]）&&！empty（$ level_4 [$ i]））{
if（！in_array（$ level_4 [$ i]，$ final_questions））
{
$ final_questions [] = $ level_4 [$ i];
break; $
$ if（$ value == 5）
{
for（$ i = 0; $ i < count（$ difficulty_array）; $ i ++）
{
if（isset（$ level_5 [$ i]）&&！empty（$ level_5 [$ i]））{
if（！in_array（$ level_5 [$ i]，$ final_questions））
{
$ final_questions [] = $ level_5 [$ i];
break;
$ b print_r（$ final_questions）;
I have table questions as
id | question | question_level
______________________________________
1 | abc | 1
______________________________________
2 | prs | 3
______________________________________
3 | oesl | 2
______________________________________
4 | ocsl | 3
______________________________________
5 | qoindos | 1
______________________________________
6 | xyz | 3
______________________________________
7 | mnlop | 2
______________________________________
8 | cllse | 2
______________________________________
9 | teuosn | 4
______________________________________
10 | ulcd | 2
______________________________________
I want to select 10 records which will match with question level
I have order of level in which I want all records as below
1,2,1,2,3,2,4,2,3,3
Output should be
id | question | question_level
______________________________________
1 | abc | 1
______________________________________
3 | oesl | 2
______________________________________
5 | qoindos | 1
______________________________________
7 | mnlop | 2
______________________________________
2 | prs | 3
______________________________________
8 | cllse | 2
______________________________________
9 | teuosn | 4
______________________________________
10 | ulcd | 2
______________________________________
4 | ocsl | 3
______________________________________
6 | xyz | 3
______________________________________
I tried different solutions but couldn't get correct output
tried with field, find_in_set but no success.
Refered [Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?
but getting only count and record in asceding order
I tried below solutions
SELECT question_level FROM `tbl_questions` WHERE `question_level` IN (1,2,1,2,3,2,4,2,3,3) ORDER BY FIELD(`question_level`, 1, 2, 1, 2, 3, 2, 4, 2, 3, 3) LIMIT 10
SELECT question_level FROM tbl_questions WHERE FIND_IN_SET(`question_level`,'1,2,1,2,3,2,4,2,3,3');
SELECT question_level,question_object_name,question_object_path,question_answer
FROM tbl_questions e JOIN (SELECT 1 AS question_level UNION ALL
SELECT 2 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 2 UNION ALL
SELECT 4 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3
) matches
USING (question_level) LIMIT 10;
I tried with foreach loop also but every time getting same record when question level matches with value
$array = explode(',', '1,2,1,2,3,2,4,2,3,3')
foreach ($array as $value) {
SELECT question_level FROM tbl_questions
WHERE question_level = $value;
}
If it is not possible in mysql then can it be achieve using php.
Thanks in advance
解决方案 I sorted this out using php
$sequence = "1,2,1,2,3,2,4,2,3,3";
$seq_arr = explode(',', $sequence);
Step 1:
Created array to store all details
$questions = array(); // to store all questions
$final_questions = array(); // tos store final questions in the sequence needed
$level_1 = array(); // level 1 questions
$level_2 = array(); // level 2 questions
$level_3 = array(); // level 3 questions
$level_4 = array(); // level 4 questions
$level_5 = array(); // level 5 questions
Step 2 :
Select all array from Db and store in each array according to question level
$this->db->select(*);
$this->db->from(questions);
$query = $this->db->get();
$result = $query->result_array();
for ($i=0; $i<count($result) ; $i++) {
if(isset($result[$i]) && !empty($result[$i])){
if($result[$i]['question_level'] == 1)
{
$level_1[] = $result[$i];
}
if($result[$i]['question_level'] == 2)
{
$level_2[] = $result[$i];
}
if($result[$i]['question_level'] == 3)
{
$level_3[] = $result[$i];
}
if($result[$i]['question_level'] == 4)
{
$level_4[] = $result[$i];
}
if($result[$i]['question_level'] == 5)
{
$level_5[] = $result[$i];
}
}
}
Step 3 :
Match sequence with all array and store into final array.
foreach ($seq_arr as $key => $value) {
if($value == 1)
{
for ($i=0; $i < count($difficulty_array) ; $i++)
{
if(isset($level_1[$i]) && !empty($level_1[$i])){
if(!in_array($level_1[$i], $final_questions))
{
$final_questions[] = $level_1[$i];
break;
}
}
}
}
if($value == 2)
{
for ($i=0; $i < count($difficulty_array) ; $i++)
{
if(isset($level_2[$i]) && !empty($level_2[$i])){
if(!in_array($level_2[$i], $final_questions))
{
$final_questions[] = $level_2[$i];
break;
}
}
}
}
if($value == 3)
{
for ($i=0; $i < count($difficulty_array) ; $i++)
{
if(isset($level_3[$i]) && !empty($level_3[$i])){
if(!in_array($level_3[$i], $final_questions))
{
$final_questions[] = $level_3[$i];
break;
}
}
}
}
if($value == 4)
{
for ($i=0; $i < count($difficulty_array) ; $i++)
{
if(isset($level_4[$i]) && !empty($level_4[$i])){
if(!in_array($level_4[$i], $final_questions))
{
$final_questions[] = $level_4[$i];
break;
}
}
}
}
if($value == 5)
{
for ($i=0; $i < count($difficulty_array) ; $i++)
{
if(isset($level_5[$i]) && !empty($level_5[$i])){
if(!in_array($level_5[$i], $final_questions))
{
$final_questions[] = $level_5[$i];
break;
}
}
}
}
}
print_r($final_questions);
这篇关于按照IN子句的顺序取记录 - 在IN子句中有重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
