如果条件为mysql，则获得两列 [英] Get two columns with if condition mysql

问题描述

我具有以下表格结构:

cv1    cv2   cv_content1   cv_content2
abc    xyz     php,mysql     sql dotnet
aaa    xxx     java          php is my skill

我有两个关键字，可以说php和dotnet.现在，我想从表t1上获取带有OR条件的列cv_content1和cv_content2中两个先前提到的关键字的记录.我想要以下记录:

I have two keywords lets say php and dotnet. Now I want to fetch record from above table t1 with two previous mentioned keywords in column cv_content1 and cv_content2 with OR condition. I want below records:

cv1   cv2
abc    xyz  
---    xxx

我尝试了case语句，如果还要，还必须检查记录中有多少个关键字匹配，例如，结果的第一行是100％(php和dotnet都在行中)，而第二行是50％(仅PHP匹配).

I tried case statement and if also and I also have to check that how many keywords match for a record for example for first row of result it is 100%(php and dotnet both are in row) but for second row it is 50%(only php matched).

到目前为止，我在下面的查询中进行了尝试:

I tried below query so far:

    SELECT ROUND(( ( if(LOCATE("php",cv_content1 )>0,25,0) OR if(LOCATE("php",cv_content2 )>0,25,0) ) + if(LOCATE("dotnet",cv_content1 )>0,25,0) OR if(LOCATE("dotnet",cv_content2 )>0,25,0) ) * 100 ) / 50) as SkillTot,if (own_content regexp '[[:<:]]smith[[:>:]]',`own_filename`),if (kings_content regexp '[[:<:]]smith[[:>:]]' ,`kings_filename`) FROM `t1`WHERE ( cv_content1 REGEXP '[[:<:]]php[[:>:]]' OR cv_content2 REGEXP '[[:<:]]php[[:>:]]' OR cv_content1 REGEXP '[[:<:]]dotnet[[:>:]]' OR cv_content2 REGEXP '[[:<:]]dotnet[[:>:]]') 

它也有语法错误.不明白如何使用if语句

It has a syntax error also. Don't understand how to use if statement

推荐答案

SELECT IF(FIND_IN_SET('php', cv_content1) OR FIND_IN_SET('dotnet', cv_content1),
          cv1, NULL) AS cv1,
       IF(FIND_IN_SET('php', cv_content2) OR FIND_IN_SET('dotnet', cv_content2),
          cv2, NULL) AS cv2
FROM YourTable
HAVING cv1 IS NOT NULL OR cv2 IS NOT NULL

这篇关于如果条件为mysql，则获得两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！