mysql - 更新工资查询MySQL

Question

我想查询更新所有已入职的医生的工资执业10年以上，当年诊疗量100人次以上的，新工资在原工资基础上加薪10%。

我的数据库有这些表:

create table employee (
  employee_VAT varchar(255),
  employee_name varchar(255),
  employee_birth_date date,
  employee_street varchar(255),
  employee_city varchar(255),
  employee_ZIP varchar(255),
  employee_IBAN varchar(255),
  employee_salary numeric(20,2),
  primary key(employee_VAT),
  unique(employee_IBAN)
);
create table doctor (
  employee_VAT varchar(255),
  specialization varchar(255),
  biography text,
  email varchar(255) not null,
  primary key(employee_VAT),
  foreign key(employee_VAT) references employee(employee_VAT),
  unique(email)
);
create table appointment (
  doctor_VAT varchar(255),
  date_timestamp datetime, 
  appointment_description text,
  client_VAT varchar(255),
  primary key(doctor_VAT, date_timestamp),
  foreign key(doctor_VAT) references doctor(employee_VAT),
  foreign key(client_VAT) references client(client_VAT)
);
create table consultation (
  doctor_VAT varchar(255),
  date_timestamp datetime, 
  SOAP_S text,
  SOAP_O text,
  SOAP_A text,
  SOAP_P text,
  primary key(doctor_VAT, date_timestamp),
  foreign key(doctor_VAT, date_timestamp) references appointment(doctor_VAT, date_timestamp)
);

我已经接近我写的这个查询:

update employee 
set employee_salary = employee_salary + 0.1 * employee_salary
where
  employee_VAT in (
    select pd.employee_VAT
    from permanent_doctor as pd
    where years >= 10
  )
  and employee_VAT in (
    select doctor_VAT
    from consultation 
    where doctor_VAT in (
      select doctor_VAT
      from consultation
      group by doctor_VAT having count(*) > 100
    )
  );

然而，这并没有像我想要的那样考虑到今年的磋商。我知道我可能必须在某个地方合并 year(date_timestamp) =year(now()) ，但我不知道如何。我在这里缺少什么？

Answer 1

在此处添加时间戳限制

select doctor_VAT
from consultation
where year(date_timestamp) = year(now())
group by doctor_VAT having count(*) > 100

只要查询的其余部分按您想要的方式工作。否则，请提供一些示例数据以及您的预期结果。