update
update patients set Status='active' where id=7;
create table
create table inactive_patients as select * from patients where status='inactive';
delete from
delete from patients where status='inactive';
alter table
alter table patients rename to active_patients;
with...select
with total_active as (select count(*) as active from active_patients),
total_inactive as (select count(*) as inactive from inactive_patients),
total_patients as (select total_active.active+total_inactive.inactive as total
from total_active,total_inactive) select total_active.active*100/total_patients.total
as percent_active,
total_inactive.inactive*100/total_patients.total as percent_inactive from total_active,
total_inactive,total_patients;
↓这两者都可以
select * ,max(id) from active_patients;
select * from active_patients where id=(select max(id) from active_patients);
union , random()
select * from (select name,address,phone from active_patients
union
select name,address,phone from inactive_patients) order by random() limit 10;
先写 group by , 然后是having ,然后是order by
alter table add column; update...set
alter table panels add column LDL decimal(5,2);
update panels set LDL=panels.Cholesterol-(panels.HDL+panels.Triglycerides/5);
最下面这个折腾了我很久。其一是rv,它只是(SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL")的临时名称。其二是group by有两个参数,LDL 和Name。问题是明明没有重复的Name,然而去掉LDL之后,还会少很多数据。原来是因为LDL是被join的主表Panels中的列,而Name是active_patients 中的列,它join到Panels之后,就出现了重复值,一个病人可能检查了两次,一次有LDL超标,一次没有超标,如果只用Name来分,就会漏掉(是随机选择超标还是没超标的数么?还是只要有没超标的记录就不符合要求?),所以还要LDL一起来分。
SELECT
active_patients.Name AS Name,
round(LDL) AS LDL,
active_patients.Address AS Address,
active_patients.Phone AS Phone
FROM
panels,
(SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL") rv
JOIN active_patients ON panels.patient_ID = active_patients.ID
GROUP BY
LDL,
active_patients.Name
HAVING LDL >= rv.Borderline_High
ORDER BY LDL DESC;
这里和上面的with 很相似,但是处理方式不一样
SELECT
COUNT(CASE WHEN (LDL >= rv.Borderline_High AND LDL < rv.High) THEN LDL END) AS Borderline_High,
COUNT(CASE WHEN LDL >= rv.High THEN LDL END) AS High
FROM
panels,
(SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL") rv;