mysql 再次比较生成列的值

Question

我的表格有两列，第一列和第二列。我想比较针对两列之和的值。我该怎么做？

CREATE TABLE test2 (
    id INT AUTO_INCREMENT,
    first INT,
    second INT,
    PRIMARY KEY (id)
);

insert into test2(first,second) values (3,4);
insert into test2(first,second) values (13,14);
insert into test2(first,second) values (23,24);


SET @mintotal := 20;

select first + second as total from test2;//works well

select first + second as total from test2 where total > @mintotal;  //sql error, unknown column total. expecting 2nd and 3d data row to be returned.

Answer 1

您可以使用像 So 这样生成的列

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first` int(11) DEFAULT NULL,
  `second` int(11) DEFAULT NULL,
  `Sum` int(11) GENERATED ALWAYS AS ((`first` + `second`)) VIRTUAL,
  PRIMARY KEY (`id`)
);

insert into test2(first,second) values (3,4);
insert into test2(first,second) values (13,14);
insert into test2(first,second) values (23,24);   

现在，每次您为 first 和 second 插入值时，Sum 都会 = first + 第二个...

Select * From test2 where Sum >  @mintotal;

注意:如果您不想生成上述列，您可以修复以下查询..

select first + second as total from test2 WHERE total > @mintotal;

进入

select first + second as total from test2 HAVING total > @mintotal;

你必须使用HAVING而不是WHERE