117 lines
3.3 KiB
SQL
117 lines
3.3 KiB
SQL
-- 查询挂号记录表主键的SQL语句
|
||
-- 表名：adm_encounter
|
||
-- 主键字段：id (bigint，雪花算法生成)
|
||
|
||
-- ========================================
|
||
-- 1. 查询所有挂号记录的主键（简单查询）
|
||
-- ========================================
|
||
SELECT id
|
||
FROM adm_encounter
|
||
WHERE delete_flag = '0'
|
||
ORDER BY id DESC;
|
||
|
||
-- ========================================
|
||
-- 2. 查询最近N条挂号记录的主键
|
||
-- ========================================
|
||
SELECT id
|
||
FROM adm_encounter
|
||
WHERE delete_flag = '0'
|
||
ORDER BY start_time DESC
|
||
LIMIT 10;
|
||
|
||
-- ========================================
|
||
-- 3. 查询指定日期范围内的挂号记录主键
|
||
-- ========================================
|
||
SELECT id
|
||
FROM adm_encounter
|
||
WHERE delete_flag = '0'
|
||
AND DATE(start_time) >= '2026-01-01'
|
||
AND DATE(start_time) <= '2026-01-31'
|
||
ORDER BY start_time DESC;
|
||
|
||
-- ========================================
|
||
-- 4. 查询指定科室的挂号记录主键
|
||
-- ========================================
|
||
SELECT id
|
||
FROM adm_encounter
|
||
WHERE delete_flag = '0'
|
||
AND organization_id = 123 -- 替换为实际的科室ID
|
||
ORDER BY start_time DESC;
|
||
|
||
-- ========================================
|
||
-- 5. 查询指定患者的挂号记录主键
|
||
-- ========================================
|
||
SELECT id
|
||
FROM adm_encounter
|
||
WHERE delete_flag = '0'
|
||
AND patient_id = 456 -- 替换为实际的患者ID
|
||
ORDER BY start_time DESC;
|
||
|
||
-- ========================================
|
||
-- 6. 查询主键及基本信息（常用）
|
||
-- ========================================
|
||
SELECT
|
||
id AS 主键ID,
|
||
bus_no AS 就诊编码,
|
||
patient_id AS 患者ID,
|
||
organization_id AS 科室ID,
|
||
registrar_id AS 医生ID,
|
||
display_order AS 流水号序号,
|
||
start_time AS 挂号时间,
|
||
status_enum AS 状态
|
||
FROM adm_encounter
|
||
WHERE delete_flag = '0'
|
||
ORDER BY start_time DESC
|
||
LIMIT 20;
|
||
|
||
-- ========================================
|
||
-- 7. 统计主键数量（按日期分组）
|
||
-- ========================================
|
||
SELECT
|
||
DATE(start_time) AS 日期,
|
||
COUNT(id) AS 挂号记录数,
|
||
MIN(id) AS 最小主键ID,
|
||
MAX(id) AS 最大主键ID
|
||
FROM adm_encounter
|
||
WHERE delete_flag = '0'
|
||
GROUP BY DATE(start_time)
|
||
ORDER BY 日期 DESC
|
||
LIMIT 30;
|
||
|
||
-- ========================================
|
||
-- 8. 查询主键的最大值、最小值、总数
|
||
-- ========================================
|
||
SELECT
|
||
COUNT(*) AS 总记录数,
|
||
MIN(id) AS 最小主键ID,
|
||
MAX(id) AS 最大主键ID,
|
||
MAX(id) - MIN(id) AS 主键ID范围
|
||
FROM adm_encounter
|
||
WHERE delete_flag = '0';
|
||
|
||
-- ========================================
|
||
-- 9. 根据主键查询单条记录（精确查询）
|
||
-- ========================================
|
||
SELECT *
|
||
FROM adm_encounter
|
||
WHERE id = 1996923066055286785 -- 替换为实际的主键ID
|
||
AND delete_flag = '0';
|
||
|
||
-- ========================================
|
||
-- 10. 查询主键及关联的患者信息
|
||
-- ========================================
|
||
SELECT
|
||
e.id AS 挂号记录主键ID,
|
||
e.bus_no AS 就诊编码,
|
||
e.display_order AS 流水号序号,
|
||
e.start_time AS 挂号时间,
|
||
p.id AS 患者ID,
|
||
p.name AS 患者姓名,
|
||
p.bus_no AS 患者业务号
|
||
FROM adm_encounter e
|
||
INNER JOIN adm_patient p ON e.patient_id = p.id AND p.delete_flag = '0'
|
||
WHERE e.delete_flag = '0'
|
||
ORDER BY e.start_time DESC
|
||
LIMIT 20;
|
||
|