时间戳

首页 > 解决方案 > 如果该状态已与该表的另一个属性一起存在，则停止将该表的属性更新为特定状态

mysql - 如果该状态已与该表的另一个属性一起存在，则停止将该表的属性更新为特定状态

问题描述

我有两张桌子。月_with_years 和months_with_years_status。我不希望超过一个month_with_year_id 的状态为“正在进行”。如果我尝试将 month_with_year_id 的状态更新为“正在进行”，而另一个 month_with_year_id 的状态为“正在进行”，我希望我的计数器显示 1 并停止更新。

CREATE TABLE `months_with_years` (
month_with_year_id INT(2) NOT NULL AUTO_INCREMENT,
month_with_year_mwy VARCHAR(50) NOT NULL,
PRIMARY KEY (month_with_year_id),
UNIQUE (month_with_year_mwy)
);
CREATE TABLE `months_with_years_status` (
month_with_year_id INT(2),
status_of_month_with_year VARCHAR(50),
FOREIGN KEY (month_with_year_id)
    REFERENCES months_with_years (month_with_year_id)
);

delimiter $$
create trigger trigger_2
after insert on months_with_years
for each row 
begin 
insert into months_with_years_status(month_with_year_id, 
status_of_month_with_year)
values (new.month_with_year_id,'Status has not been updated yet');

end$$
delimiter ;

insert into months_with_years 
values(1,'1-2019'),(2,'2-2019'),(3,'3-2019');

update months_with_years_status 
set  status_of_month_with_year='Ongoing'
where month_with_year_id=1;

update months_with_years_status 
set  status_of_month_with_year='Ongoing'
where month_with_year_id=2;

当计数发生时，我试图获得 count_of_ongoing_status_YearMonth 值 1。

select count(*) as count_of_ongoing_status_YearMonth
from months_with_years_status 
where status_of_month_with_year='Ongoing' having count(*) like '%Ongoing%' = 1;

标签： mysqlcount

解决方案

检查您只需要的计数

select count(*) as count_of_ongoing_status_YearMonth
from months_with_years_status 
where status_of_month_with_year='Ongoing' having count(*) = 1;

可能是您每个月都需要这些_with_year_id .. 为此您可以使用 group by

select month_with_year_id , count(*) as count_of_ongoing_status_YearMonth
from months_with_years_status 
where status_of_month_with_year='Ongoing' 
group by month_with_year_id 
having count(*) = 1;

推荐阅读