mysql - 复杂的 SQL 查询(使用 2 列排序)
问题描述
我执行 SQL 并得到以下结果。
select vital_history_id,vital_id,patient_id,measurement_datetime,create_datetime
from t_patient_vital_history
where patient_id = 54
group by vital_id,create_datetime
order by measurement_datetime desc, create_datetime desc
limit 100 offset 0
results-1 但我想得到这样的结果。
results-2 这是 DDL 和 DML。
create table t_patient_vital_history (
vital_history_id bigint unsigned not null
, vital_id bigint unsigned not null
, patient_id bigint unsigned not null
, measurement_datetime datetime
, sbp double
, dbp double
, temperature
, pulse double
, spo2 double
, weight double
, bmi double
, abdominal double
, height_measurement double
, head_measurement double
, chest_measurement double
, action varchar(20)
, is_deleted tinyint default 0 not null
, create_id varchar(20) not null
, create_name varchar(100) not null
, create_datetime datetime(3) not null
, update_id varchar(20) not null
, update_name varchar(100) not null
, update_datetime datetime(3) not null
, constraint t_patient_vital_history_PKC primary key (vital_history_id)
);
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (107,66,54,'2018-06-25 17:15:32',900,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'register',0,'1','Mike','2018-05-25 17:15:40.638','1','Mike','2018-05-25 17:15:40.638');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (119,69,54,'2018-07-25 18:24:15',22,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'register',0,'1','Mike','2018-05-25 18:24:57.772','1','Mike','2018-05-25 18:24:57.772');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (129,69,54,'2018-07-25 18:24:15',22,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:37:19.971','1','Mike','2018-05-25 18:37:19.971');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (130,69,54,'2018-07-25 18:24:15',22,1,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:37:24.128','1','Mike','2018-05-25 18:37:24.128');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (131,69,54,'2018-07-25 18:24:15',22,1,2,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:37:31.503','1','Mike','2018-05-25 18:37:31.503');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (134,69,54,'2018-07-25 18:24:15',22,1,2,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:44:42.444','1','Mike','2018-05-25 18:44:42.444');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (135,69,54,'2018-07-25 18:24:15',22,1,2,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-25 18:44:49.518','1','Mike','2018-05-25 18:44:49.518');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (141,69,54,'2018-07-25 18:24:15',22,1,2,NULL,12,5,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-28 17:47:22.835','1','Mike','2018-05-28 17:47:22.835');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (147,66,54,'2018-06-25 17:15:32',90,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-05-28 19:48:00.159','1','Mike','2018-05-28 19:48:00.159');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (164,69,54,'2018-07-25 18:24:15',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'削除',1,'1','Mike','2018-05-30 12:07:12.563','1','Mike','2018-05-30 12:07:12.563');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (168,90,54,'2018-10-01 01:00:59',1,1,1,1,1,1,10000,1,1,1,1,'register',0,'1','Mike','2018-05-30 13:11:29.756','1','Mike','2018-05-30 13:11:29.756');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (1231,90,54,'2018-10-01 01:00:59',1,1,1,1,1,1,NULL,1,NULL,1,1,'update',0,'1','Mike','2018-06-11 16:32:33.263','1','Mike','2018-06-11 16:32:33.263');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (1302,90,54,'2018-10-01 01:00:59',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'削除',1,'1','Mike','2018-06-15 13:16:18.100','1','Mike','2018-06-15 13:16:18.100');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (1323,66,54,'2018-06-25 17:00:32',90,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-06-15 16:40:40.627','1','Mike','2018-06-15 16:40:40.627');
INSERT INTO `t_patient_vital_history` (`vital_history_id`,`vital_id`,`patient_id`,`measurement_datetime`,`sbp`,`dbp`,`temperature`,`pulse`,`spo2`,`weight`,`bmi`,`abdominal`,`height_measurement`,`head_measurement`,`chest_measurement`,`action`,`is_deleted`,`create_id`,`create_name`,`create_datetime`,`update_id`,`update_name`,`update_datetime`) VALUES (1340,66,54,'2018-06-25 17:20:32',90,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'update',0,'1','Mike','2018-06-15 18:33:20.112','1','Mike','2018-06-15 18:33:20.112');
首先,按measurement_datetime desc 排序。接下来,按create_datetime desc 排序,保持按measurement_datetime desc 排序。如何使用 measure_datetime 和 create_datetime 对查询进行排序?请给我建议。
解决方案
vital_id
您可以通过first 然后 by实现您正在寻找的排序vital_history_id
。
mysql > select vital_history_id,vital_id,patient_id,measurement_datetime,create_datetime
-> from t_patient_vital_history
-> where patient_id = 54
-> group by vital_id,create_datetime
-> order by vital_id desc, vital_history_id desc, measurement_datetime desc, create_datetime desc
-> limit 100 offset 0;
+------------------+----------+------------+----------------------+-------------------------+
| vital_history_id | vital_id | patient_id | measurement_datetime | create_datetime |
+------------------+----------+------------+----------------------+-------------------------+
| 1302 | 90 | 54 | 2018-10-01 01:00:59 | 2018-06-15 13:16:18.100 |
| 1231 | 90 | 54 | 2018-10-01 01:00:59 | 2018-06-11 16:32:33.263 |
| 168 | 90 | 54 | 2018-10-01 01:00:59 | 2018-05-30 13:11:29.756 |
| 164 | 69 | 54 | 2018-07-25 18:24:15 | 2018-05-30 12:07:12.563 |
| 141 | 69 | 54 | 2018-07-25 18:24:15 | 2018-05-28 17:47:22.835 |
| 135 | 69 | 54 | 2018-07-25 18:24:15 | 2018-05-25 18:44:49.518 |
| 134 | 69 | 54 | 2018-07-25 18:24:15 | 2018-05-25 18:44:42.444 |
| 131 | 69 | 54 | 2018-07-25 18:24:15 | 2018-05-25 18:37:31.503 |
| 130 | 69 | 54 | 2018-07-25 18:24:15 | 2018-05-25 18:37:24.128 |
| 129 | 69 | 54 | 2018-07-25 18:24:15 | 2018-05-25 18:37:19.971 |
| 119 | 69 | 54 | 2018-07-25 18:24:15 | 2018-05-25 18:24:57.772 |
| 1340 | 66 | 54 | 2018-06-25 17:20:32 | 2018-06-15 18:33:20.112 |
| 1323 | 66 | 54 | 2018-06-25 17:00:32 | 2018-06-15 16:40:40.627 |
| 147 | 66 | 54 | 2018-06-25 17:15:32 | 2018-05-28 19:48:00.159 |
| 107 | 66 | 54 | 2018-06-25 17:15:32 | 2018-05-25 17:15:40.638 |
+------------------+----------+------------+----------------------+-------------------------+
15 rows in set (0.00 sec)
这是查询:
select vital_history_id,vital_id,patient_id,measurement_datetime,create_datetime
from t_patient_vital_history
where patient_id = 54
group by vital_id,create_datetime
order by vital_id desc, vital_history_id desc, measurement_datetime desc, create_datetime desc
limit 100 offset 0;
推荐阅读
- solr - 按查询对 SOLR 结果进行排名
- dart - 循环时插入复选框
- hadoop - SAS Hive (Hadoop) 中是否有包含函数?
- javascript - Vanilla JavaScript:禁用网页中给定的预先存在的组合键
- python - 子列表的正则表达式
- c++ - 如何应用 libcurl 的持久连接选项
- reactjs - 当其长度 = 1 时,无法在 flatlist 中显示数组的内容
- php - PHP 数组到 PHP 变量
- laravel - nicolaslopezj/searchable:如何为单个表的多个连接设置配置
- html - 播放 Youtube 视频时暂停 Bootstrap 轮播