mysql查询平均温度_如何通过mysql检测所有患者的温度信息？

我希望你真的能在这样的事情之后 - 尽管将温度存储在一个单独的表格中并没有意义......

DROP TABLE IF EXISTS patient;

CREATE TABLE patient

(patient_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

,name VARCHAR(12) NOT NULL

,gender CHAR(1) NOT NULL

,dob DATE NOT NULL

);

INSERT INTO patient VALUES

(101 ,'Hind','F','2015-01-01'),

(102 ,'Saad','M','2016-01-01');

DROP TABLE IF EXISTS patient_temperature;

CREATE TABLE patient_temperature

(patient_id INT NOT NULL

,temperature_id INT NOT NULL

,PRIMARY KEY(patient_id,temperature_id)

);

INSERT INTO patient_temperature VALUES

(101,1),

(101,2),

(102,3),

(102,4);

DROP TABLE IF EXISTS temperature;

CREATE TABLE temperature

(temperature_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

,temperature DECIMAL(5,2) NOT NULL

);

INSERT INTO temperature VALUES

(1,37.9),

(2,37),

(3,38),

(4,38.4);

SELECT p.*

, t.*

FROM patient p

JOIN patient_temperature pt

ON pt.patient_id = p.patient_id

JOIN temperature t

ON t.temperature_id = pt.temperature_id;

+------------+------+--------+------------+----------------+-------------+

| patient_id | name | gender | dob | temperature_id | temperature |

+------------+------+--------+------------+----------------+-------------+

| 101 | Hind | F | 2015-01-01 | 1 | 37.90 |

| 101 | Hind | F | 2015-01-01 | 2 | 37.00 |

| 102 | Saad | M | 2016-01-01 | 3 | 38.00 |

| 102 | Saad | M | 2016-01-01 | 4 | 38.40 |

+------------+------+--------+------------+----------------+-------------+