如何将 find_in_set(value, column) 与 %% 值一起使用 [英] How to use find_in_set(value, column) with like %% values

问题描述

Mysql 表 "DemoTable" (id -> 自增)

Mysql table "DemoTable" (id -> auto increment)

id   subject_id  subject_name  question  exams
1        3         sub3         ques1    e1,e4
2        3         sub3         ques2    e1
3        2         sub2         ques3    e1
4        2         sub2         ques4    e3,e5

我想要的是用这种 where case 来跟踪输出 - where 像 '%e1%' 之类的考试

subject_id  subject_name  number_of_questions
    3         sub3         2
    2         sub2         1

在 Stackoverflow 用户 juergen d 的帮助下，我使用了这个查询 -

with the help of Stackoverflow user juergen d, I used this query -

SELECT subject_id, subject_name, COUNT(distinct question) as questions_count 
FROM DemoTable 
where find_in_set('e1', exams) > 0
group by subject_id, subject_name

但是，这个查询的问题是它输出为 -

But, the problem with this query is it outputs as -

subject_id  subject_name  questions_count 
    3         sub3         1
    2         sub2         1

这意味着，它只计算行 where Exams = 'e1'，因此它不包括第一行 where Exams = 'e1,e4' [其中通过 where 考试，如 '%e1%']

Which means, it only counts the rows where exams = 'e1', therefore it didn't include first row where exams = 'e1,e4' [which gets included in normal cases by where exams like '%e1%']

应该使用什么查询来获得所需的输出?

What query should be used to get desired output?

谢谢.问候，

推荐答案

Juergen 的回答应该是正确的.而且，事实上，它应该比这个版本更好:

Juergen's answer should be correct. And, in fact, it should be better than this version:

SELECT subject_id, subject_name, COUNT(distinct question) as questions_count 
FROM DemoTable 
where exams like '%e1%'
group by subject_id, subject_name;

优点是他的版本在搜索e1时不会混淆e10和e1.我怀疑您问题中的数据和您网站上的数据不同.您应该尝试设置 SQL Fiddle.

The advantage is that his version will not confuse e10 and e1 when searching for e1. I suspect the data in your question and on your site is different. You should try setting up a SQL Fiddle.

这篇关于如何将 find_in_set(value, column) 与 %% 值一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！