mysql - MySQL SELECT 语句中的 CASE 条件

Question

我在 SELECT 中有 CASE 条件。下面是查询条件

查询来自 View

SELECT expt_id,stain_type,control_stain, test_stain
   CASE WHEN stain_type = 'Blue' THEN control_stain = 'NA'
   CASE WHEN stain_type = 'Hemat' THEN test_stain = 'NA'
FROM experiment_results__view

此处必须为 stain_type 的条件填充值“NA”。感谢您提供有关如何编写此查询的建议

谢谢!!

Answer 1

我想你想在行中使用一些东西:

SELECT 
   expt_id,
   stain_type,
   CASE WHEN stain_type = 'Blue' THEN 'NA' ELSE NULL END control_stain, 
   CASE WHEN stain_type = 'Hemat' THEN 'NA' ELSE NULL END test_stain
FROM experiment_results__view

将“NA”的值放入名为 control_stain 和 test_stain 的列中。但是，如果您只想在这些情况下使用值“NA”覆盖这些列中的现有值，请使用:

SELECT 
   expt_id,
   stain_type,
   CASE WHEN stain_type = 'Blue' THEN 'NA' ELSE control_stain END control_stain, 
   CASE WHEN stain_type = 'Hemat' THEN 'NA' ELSE test_stain END test_stain
FROM experiment_results__view

如果您打算更新表格，那么您应该将其重写为:

UPDATE 
   experiment_results__view 
SET 
   control_stain = CASE WHEN stain_type = 'Blue' THEN 'NA' ELSE control_stain END, 
   test_stain = CASE WHEN stain_type = 'Hemat' THEN 'NA' ELSE test_stain END
-- if you don't want to update the complete table add an WHERE clause
WHERE
   <some condition>