如果存在特定行，如何将MYSQL列设置为特定值? [英] How to set MYSQL column to certain value if a certain row exists?

问题描述

我想在表中生成一个新列，如果在特定条件下存在一行，则该列为true.

I want to generate a new column in my table that is true if a row exists with certain conditions.

name | col1 | col2 | flag
--------------------------
a      1      2      0
a      2      3      0
b      1      2      0
b      4      3      0

让我们说，如果存在与name一起的行，并且其中col1=2和col2 = 3存在，我想将每个name标识符的标志设置为1.因此，这将导致:

Lets say I want to set the flag to 1 for every name identifier if a row exists with that name and where col1=2 and col2 = 3. So this would result in:

name | col1 | col2 | flag
--------------------------
a      1      2      1
a      2      3      1
b      1      2      0
b      4      3      0

因为对于a，存在具有col1=2和col2 = 3的行，但对于b，则不存在这样的行.

because for a a row with col1=2 and col2 = 3 exists, but for b, such a row doesn't exist.

在伪代码中，我想要这样的东西:

In pseudocode I want something like this:

ALTER TABLE table_name
ADD flag TINYINT(1)
IF ##row with condition col1=value1 and col2=value2 exists#
GROUP BY name

如何生成此列?

推荐答案

所以您只想从db获取这些值?还是要添加列?那是两个不同的目标.

So you want just to get those values from db? or you want to add column? those are 2 different goals.

因此，如果您只需要获取这些值，则可以:

So if you need just to get those values you can:

http://sqlfiddle.com/#!9/65b4c2/1

SELECT t.*, t2.flag
FROM table_name t
LEFT JOIN (
  SELECT name, MAX(IF(col1=2 AND col2=3,1,0)) flag
  FROM table_name
  GROUP BY name
  ) t2
ON t.name = t2.name

，如果您确实需要添加新列，请按照以下方式进行操作:

and if you really need to add new column then you go this way:

http://sqlfiddle.com/#!9/226fb3/1

ALTER TABLE table_name ADD COLUMN flag TINYINT;

UPDATE table_name t
LEFT JOIN (
      SELECT name, MAX(IF(col1=2 AND col2=3,1,0)) flag
      FROM table_name
      GROUP BY name
      ) t2
ON t.name = t2.name
SET t.flag=t2.flag

这篇关于如果存在特定行，如何将MYSQL列设置为特定值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！