我正在尝试选择每层楼有多少张免费床位,以及这些床位所在的房间数。我想我有麻烦了,因为我首先必须计算每个房间有多少张空床,我是通过减去房间里的床位数减去分配到该房间的人数来计算的。
我能以一种达到预期结果的方式GROUP BY
当前的查询吗?
使用的表格如下:
CREATE TABLE IF NOT EXISTS planta(
codigo int PRIMARY KEY NOT NULL AUTO_INCREMENT,
especialidad varchar(25) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS habitacion(
id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
numero_camas int NOT NULL,
planta_id int NOT NULL,
FOREIGN KEY (planta_id) REFERENCES planta(codigo)
)ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS paciente(
dni varchar(9) PRIMARY KEY NOT NULL,
num_ss varchar(10) NOT NULL,
nombre varchar(20) NOT NULL,
direccion varchar(50) NOT NULL,
tratamiento mediumtext NOT NULL,
diagnostico mediumtext NOT NULL,
habitacion_id int NOT NULL,
medico_id int NOT NULL,
FOREIGN KEY (habitacion_id) REFERENCES habitacion(id),
FOREIGN KEY (medico_id) REFERENCES medico(num_colegiado)
)ENGINE=InnoDB;
查询是这样的:
SELECT planta.codigo AS Floor_id,
habitacion.id AS Room_id,
numero_camas - count(dni) AS Free_beds
FROM habitacion, paciente, planta
WHERE planta_id = planta.codigo AND habitacion_id = habitacion.id
GROUP BY planta.codigo, habitacion.id;
它返回以下结果:
Floor id | Room id | Free beds
1 1 1
1 2 1
2 3 3
但我想要的是:
Floor id | Rooms | Free beds
1 2 2
2 1 3
发布于 2019-02-28 05:40:13
根据戈登·林诺夫和yor的回答,我想通过这个小的改变,你应该得到正确的结果(我想需要计算不同的房间,需要计算床位数减去分配的人数):
SELECT
pl.codigo AS Floor_id,
count(distinct habitacion_id) as num_rooms,
SUM(numero_camas - count(dni)) AS Free_beds
FROM habitacion h
join paciente p on p.habitacion_id = h.id
join condition planta pl on h.planta_id = pl.codigo
GROUP BY pl.codigo
发布于 2019-02-28 06:10:12
您需要两个汇总:每个居所的床位数,然后是每个楼层的可用床位总数。实现这一点的一种方法是:
select planta_id, count(*) as rooms, sum(available - occupied) as free_beds
from
(
select
planta_id,
h.id as habitacion_id,
numero_camas as available,
(select count(*) from paciente p where p.habitacion_id = h.id) as occupied
from habitacion h
) habitation_aggregated
group by planta_id
order by planta_id;
您可以看到,我在FROM
子句中有一个表示聚合的子查询。这是处理多个聚合的一种非常好且省钱的方法。
https://stackoverflow.com/questions/54913904
复制相似问题