所以我有下面的表运行,但我有一个特定的情况下的问题。
我有一个肥皂分配器网络,我想跟踪他们当前的肥皂水平。我正在计算泵的数量(每个3毫升),并做greatest(full_capacity - number_pumps * 3, 0)
,如视图表所示。
但我的问题是:有表维护,和一个"描述";可能是"续杯"。我想要的是,当表记录中的number_pumps
的maintenance_description = "refill"
被设置为0时,这个分配器。这可能吗?我读了关于触发器的书,但不太明白如何做到这一点。
作为一个实际的例子,假设我有一个id为1的肥皂分配器,最大容量为1000ml,然后我数了300个泵,所以我知道我还剩下100ml。然后我做一个补充,并希望泵的数量被设置为0。否则,在下次使用时,它会说我有97ml可用,而实际上我有997ml,因为我已经补充了。
提前谢谢你。
create table dispenser(
id_dispenser int not null auto_increment,
localization_disp varchar(20) not null,
full_capacity int not null,
primary key (id_dispenser));
create table records(
time_stamp DATETIME DEFAULT CURRENT_TIMESTAMP not null,
dispenser_id int not null,
number_pumps int not null,
battery_level float not null,
primary key (dispenser_id,time_stamp));
create table maintenance(
maintenance_id int not null auto_increment,
maintenance_date DATETIME DEFAULT CURRENT_TIMESTAMP not null,
employee_id int not null,
maintenance_description varchar(20) not null,
dispenser_id int not null,
primary key (maintenance_id));
CREATE VIEW left_capacity
AS
SELECT max(time_stamp) AS calendar,
id_dispenser AS dispenser,
full_capacity AS capacity,
greatest(full_capacity - number_pumps * 3, 0) AS available
FROM records r
INNER JOIN dispenser d
ON d.id_disp = r.id_dispenser
GROUP by id_dispenser;
如果我理解正确的话,您想要一个剩余金额的视图。这将是自上次补充后的泵数,根据您的公式。
MySQL在视图中的子查询有棘手的问题。我认为以下是MySQL的视图安全:
select d.*,
(d.full_capacity -
(select count(*) * 3
from records r
where r.id_dispenser = d.id_dispenser and
r.time_stamp > (select max(m.maintenance_date)
from maintenance m
where m.id_dispenser = r.id_dispenser and
m.maintenance_description = 'refill'
)
)
) as available
from dispenser d;