mysql查询：如何使用concat\ws和coalesce正确地识别逗号分隔的结果值并将其重新转换为原始概念

iq0todco 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(461)

关于在mysql数据库表中以逗号分隔的方式保存基于字符串的结果有很多讨论。我不想在这里用我自己的哲学评论来扩展这一点，我只想说，这个问题是我所熟知的，但不是现在的主题。
题目是我有这样的情况要评估。数据保存为基于字符串的密码。这些密码中的每一个都表示特定类型的手术后的医疗并发症。
例子：
mysql数据库表“complements”包含一个名为“indication for surgical revision”（varchar[50]）的字段，其中保存了诸如“3、7、9、16”之类的数据项，因为这4个不同的适应症是由用户从多个选择菜单中选择的。
现在我想做以下工作：
我知道“3”、“7”、“9”和“16”代表什么。我想使用mysql select查询将这些密码重新显示为它们的原始概念（如“体重恢复”、“体重减轻失败”等），例如：

SELECT blah blah blah (a lot of other stuff),
CONCAT_WS(", "
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%1%" THEN "Innere Hernie (Meso)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%2%" THEN "Innere Hernie (PETERSEN)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%3%" THEN "Weight Regain  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%4%" THEN "Weight Loss Failure  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%5%" THEN "Anastomosenstenose  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%6%" THEN "Dysphagie  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%7%" THEN "Reflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%8%" THEN "Gallenreflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%9%" THEN "Malnutrition  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%10%" THEN "Diarrhö  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%11%" THEN "Gastrogastrale Fistel" ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%12%" THEN "Ulcusperforation  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%13%" THEN "Chronische Ulcus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%14%" THEN "Chronische Schmerzen  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%15%" THEN "Ileus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%16%" THEN "Choledocholithiasis nach Magen-Bypass  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN op.OP2RevisionIndikation LIKE "%17%" THEN "Leakage  " ELSE CONCAT("", "") END, "NULL")
                 ) AS "Indikation zur Revision",

现在你可能会问“为什么这个家伙如此间接？”或者“他为什么不使用php呢？”。我是用php来做的，但是在一个不同的上下文中。在这里，我需要使用sql查询进行直接计算，因为在这段代码中，并非我所说的所有数据都被提取出来，以便在excel csv文件中自动创建要进行二次处理的结果集—我不想重新发明轮子。
现在，上面提到的查询并不是我想要它做的。在我的例子中，我想从这个“3，7，9，16”字符串中显示“体重恢复，反流，营养不良，胆总管结石nach-magen旁路”。
我知道一个事实。。。例如“%3%”。。。在这里不起作用。
有一些使用“find_in_set（'3'，op.op2revisionindikation）”的建议，但这里我们不是直接在主select语句中，而是在then else使用coalesce和concat_ws结束过程的情况下。
你们中有人知道如何通过将字符串“3，7，9，16”Map到原始值并让mysql以这种方式显示它来正确地计算它吗？
我希望我已经足够透彻和充分理解。
致以最诚挚的问候，无限感谢您的帮助
马库斯

mysql coalesce csv concat-ws

来源：https://stackoverflow.com/questions/50185833/mysql-query-how-to-properly-identify-and-retranslate-comma-separated-result-val

3条答案

按热度按时间

igsr9ssn1#

在更一般的情况下，当目标是将逗号分隔的正整数值列表“转换”为相应字符串列表时，顺序与原始值列表相同，并且不引入无关的“逗号”和空格。。。
我们可以用一种可怕的表情来达到这个目的。
作为示范。

SELECT op.OP2RevisionIndikation
      , CONCAT_WS(', '
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  1 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  2 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  3 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  4 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  5 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  6 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  7 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  8 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',',  9 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 10 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 11 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 12 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 13 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 14 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 15 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 16 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 17 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 18 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 19 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
        , ELT(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(op.OP2RevisionIndikation,REPEAT(',',21)),',', 20 ),',',-1)+0,'Innere Hernie (Meso)','Innere Hernie (PETERSEN)','Weight Regain','Weight Loss Failure','Anastomosenstenose','Dysphagie','Reflux','Gallenreflux','Malnutrition','Diarrhö','Gastrogastrale Fistel','Ulcusperforation','Chronische Ulcus','Chronische Schmerzen','Ileus','Choledocholithiasis nach Magen-Bypass','Leakage') 
   ) AS translated
  FROM ( SELECT '' AS OP2RevisionIndikation 
         UNION ALL SELECT '17'         
         UNION ALL SELECT '3'
         UNION ALL SELECT '3, 17'
         UNION ALL SELECT '17, 3, 7'
         UNION ALL SELECT '17 , ,, ,3, flurb, 747, 17'
         UNION ALL SELECT ', x,,,,000017,, 3x  ,,x, 01,,17'
       ) AS op

退货

OP2RevisionIndikation            translated
-------------------------------  -------------------------------------------------------
17                               Leakage
3                                Weight Regain
3, 17                            Weight Regain, Leakage
17, 3, 7                         Leakage, Weight Regain, Reflux
17 , ,, ,3, flurb, 747, 17       Leakage, Weight Regain, Leakage
^^       ^              ^^
, x,,,,000017,, 3x  ,,x, 01,,17  Leakage, Weight Regain, Innere Hernie (Meso), Leakage
           ^^   ^         ^  ^^

注意事项：
每个 ELT 行是一个完全相同的副本，除了一个整数基本上指定了要提取的逗号分隔的整数列表的第n个元素，即第1个、第4个、第5个等等。
表达式只处理列表中的有限个元素；每个元素都需要一个单独的elt表达式。该示例最多可以处理逗号分隔列表中的20个元素；这可以扩展到处理更多的元素。
这只适用于以逗号分隔的正整数值列表。逗号之间的每个值都将作为整数计算。这意味着一个元素 'x17' 将评估为 0 . 元素 '-6.2' 将评估为 -6 . 要素 '007jamesbond' 将评估为 7 . 等等。
求值中的整数值用作索引，以便从中的字符串列表中“查找”字符串 ELT 功能。
计算为与列表中的字符串不对应的整数值的元素（例如。 0 , -6 )被忽略，就好像元素不在原始列表中一样。
示例中返回的最后两行演示了逗号分隔列表的行为，我们可能认为这些列表格式不正确。翻译表达式是“尽力而为”，它返回可以翻译的内容。当逗号分隔的列表格式不正确时，表达式不会引发错误，也不会返回null或空字符串。

展开查看全部

赞(0）回复(0）举报 2021-06-21

6jjcrrmo2#

啊哈，尼克的最后一句话很有意思：

CONCAT_WS(", "
                 , COALESCE(CASE WHEN FIND_IN_SET("1", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Innere Hernie (Meso)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("2", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Innere Hernie (PETERSEN)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("3", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Weight Regain  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("4", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Weight Loss Failure  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("5", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Anastomosenstenose  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("6", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Dysphagie  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("7", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Reflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("8", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Gallenreflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("9", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Malnutrition  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("10", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Diarrhö  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("11", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Gastrogastrale Fistel" ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("12", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Ulcusperforation  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("13", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Chronische Ulcus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("14", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Chronische Schmerzen  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("15", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Ileus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("16", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Choledocholithiasis nach Magen-Bypass  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("17", REPLACE(op.OP2RevisionIndikation, ' ', '')) THEN "Leakage  " ELSE CONCAT("", "") END, "NULL")
                 ) AS "Indikation zur Revision",

结果显示在此屏幕截图中：

现在唯一要做的就是一个好的格式化（去掉逗号和空格等）。
问题解决了。

展开查看全部

赞(0）回复(0）举报 2021-06-21

ltskdhd13#

谢谢你的回答。
在集合中查找解决方案。。。

CONCAT_WS(", "
                 , COALESCE(CASE WHEN FIND_IN_SET("1", op.OP2RevisionIndikation) THEN "Innere Hernie (Meso)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("2", op.OP2RevisionIndikation) THEN "Innere Hernie (PETERSEN)  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("3", op.OP2RevisionIndikation) THEN "Weight Regain  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("4", op.OP2RevisionIndikation) THEN "Weight Loss Failure  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("5", op.OP2RevisionIndikation) THEN "Anastomosenstenose  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("6", op.OP2RevisionIndikation) THEN "Dysphagie  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("7", op.OP2RevisionIndikation) THEN "Reflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("8", op.OP2RevisionIndikation) THEN "Gallenreflux  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("9", op.OP2RevisionIndikation) THEN "Malnutrition  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("10", op.OP2RevisionIndikation) THEN "Diarrhö  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("11", op.OP2RevisionIndikation) THEN "Gastrogastrale Fistel" ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("12", op.OP2RevisionIndikation) THEN "Ulcusperforation  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("13", op.OP2RevisionIndikation) THEN "Chronische Ulcus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("14", op.OP2RevisionIndikation) THEN "Chronische Schmerzen  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("15", op.OP2RevisionIndikation) THEN "Ileus  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("16", op.OP2RevisionIndikation) THEN "Choledocholithiasis nach Magen-Bypass  " ELSE CONCAT("", "") END, "NULL")
                 , COALESCE(CASE WHEN FIND_IN_SET("17", op.OP2RevisionIndikation) THEN "Leakage  " ELSE CONCAT("", "") END, "NULL")
                 ) AS "Indikation zur Revision",

... 能够识别单个项目。但它不处理逗号：
从“3，7，9，16”中可以正确识别“3”，但其余的一点也不正确，因此我只显示了这些项目中的第一个：

展开查看全部

赞(0）回复(0）举报 2021-06-21

我来回答

mysql查询：如何使用concat\ws和coalesce正确地识别逗号分隔的结果值并将其重新转换为原始概念

3条答案

相关问题

热门标签

最新问答