2021数据库MYSQL语句梳理（Navicat）

promise~~

已于 2022-02-12 10:05:20 修改

阅读量926

点赞数

CC 4.0 BY-SA版权

文章标签： sql mysql

于 2022-02-12 10:03:56 首次发布

本文链接：https://blog.youkuaiyun.com/yinna_csdn_cc/article/details/122892110

本文介绍了如何使用MySQL进行数据排序，包括按首字母和序号，以及原始库与档案库的数据转移量对比、汇总显示及机构进度查询，旨在记录SQL操作实践。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

一、序号导表
- 1.首字母排序
- 2.按序号输出
  - （1）递增序号
  - （2）按照机构递增序号
二、原始库与档案库数据转移量对比
三、原始库数据汇总显示（+往年数据对比）
四、2021机构进度查询
总结

一、序号导表

1、设置姓名按照汉字首字母排序，使用强制转换（转换编码） convert(xm using gbk)。


select  (@i:=@i+1) as '序号',tjrq  '体检日期',org_name '机构',xm '姓名',sfz '身份证',
concat(SUBSTRING(sfz FROM 7 FOR 4) ,'-',SUBSTRING(sfz FROM 11 FOR 2),'-',SUBSTRING(sfz FROM 13 FOR 2)) '出生日期',
case when gender=1 then '男' else '女'end '性别',lxdh '联系电话'
 from health_record2021.mh_lnr_jkda,(SELECT @i:=0)  as i
where org_id like '37110212%' and tjrq BETWEEN '20210401' and '20210430' and is_fufei=0
ORDER by convert(xm using gbk) asc;

2、设置记录按照序号输出：

（1）添加递增的序号列。

select (@i:=@i+1) as i,a.* from mh_lnr_jkda a,(select @i:=0) as i

（2）按照机构设置递增序号列。

select RANK '序号',tjrq  '体检日期',org_name '机构',xm '姓名',sfz '身份证',
concat(SUBSTRING(sfz FROM 7 FOR 4) ,'-',SUBSTRING(sfz FROM 11 FOR 2),'-',SUBSTRING(sfz FROM 13 FOR 2)) '出生日期',age '年龄',
case when gender=1 then '男' else '女'end '性别',lxdh '联系电话',address '地址'
from
(select mh_lnr_jkda_A.*, @rank:=if(@ORG_NAME = ORG_NAME,@rank + 1, 1) as  rank, @ORG_NAME:=ORG_name  from
(SELECT * FROM MH_LNR_JKDA WHERE ORG_ID LIKE'37110212%') MH_LNR_JKDA_A,
(select @rank:=0,@ORG_NAME:=null) a   order by ORG_NAME,ry_type) a
where org_id like '37110212%' and tjrq BETWEEN '20210401' and '20210430' 
ORDER BY tjrq,org_name

二、原始库与档案库数据转移量对比。

set @org_id=50011301; -- 机构号
set @start_time=20211026; -- 开始时间
set @end_time=20211026; -- 结束时间


-- 整体对比查询各项目人数

select '报告总人数' as '项目',COUNT(1) as '数量'  from health_info2021.mh_jktj j  where j.org_id like concat(@org_id,'%') and j.tjrq >=@start_time and j.tjrq <=@end_time  and j.is_fufei=0  
 UNION ALL
select '档案——总人数' as '项目',COUNT(1) as '档案数量'  from health_record2021.mh_lnr_jkda   where  org_id like concat(@org_id,'%') and  tjrq >=@start_time and tjrq <=@end_time   
union all 
select '报告中医体质' as '项目',COUNT(*) as '报告中医数量'  from health_info2021.mh_zytz where org_id like concat(@org_id,'%') and tbrq >=@start_time and tbrq <=@end_time
union all 
select '档案——中医体质' as '项目',COUNT(*) as '档案中医数量'  from health_record2021.mh_zytz where org_id like concat(@org_id,'%') and tbrq >=@start_time and tbrq <=@end_time
union ALL
select '报告基本信息' as '项目',COUNT(1) as '报告基本信息数量' from basicinfo.rhr where mh_card in (select mh_card from health_info2021.mh_jktj where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time and is_fufei=0)  
union ALL
select '档案——基本信息' as '项目',COUNT(1) as '档案基本信息数量' from health_record2021.mh_grjbxx where mh_card in (select mh_card from health_record2021.mh_lnr_jkda where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time)  
 UNION ALL
select '报告生化人数' as '项目',COUNT(1) as '报告生化数量' from health_info2021.mh_jy_parson where tjid in (select id from health_info2021.mh_jktj where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time and is_fufei=0) and jytype = 1
UNION ALL
select '档案——生化人数' as '项目',COUNT(1) as '档案生化数量' from health_record2021.mh_jy_parson where tjid in (select id from health_record2021.mh_lnr_jkda where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time) and jytype = 1
 UNION ALL
select '报告生化小项' as '项目',COUNT(*) as '报告生化小项' from health_info2021.mh_jy_result where jy_parid in(select jy_parid from health_info2021.mh_jy_parson where tjid in (select id from health_info2021.mh_jktj where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time and is_fufei=0) and jytype = 1)
union all 
select '档案——生化小项' as '项目',COUNT(*) as '档案生化小项' from health_record2021.mh_jy_result where jy_parid in(select jy_parid from health_record2021.mh_jy_parson where tjid in (select id from health_record2021.mh_lnr_jkda where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time) and jytype = 1)
 UNION ALL
select '报告尿检人数' as '项目',COUNT(1) as '报告尿检数量' from health_info2021.mh_jy_parson where tjid in (select id from health_info2021.mh_jktj where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time and is_fufei=0) and jytype = 5
union all 
select '档案——尿检人数' as '项目',COUNT(1) as '档案尿检数量' from health_record2021.mh_jy_parson where tjid in (select id from health_record2021.mh_lnr_jkda where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time) and jytype = 5
UNION ALL
select '报告尿检小项' as '项目',COUNT(*) as '报告尿小项数量' from health_info2021.mh_jy_result where jy_parid in(select jy_parid from health_info2021.mh_jy_parson where tjid in (select id from health_info2021.mh_jktj where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time and is_fufei=0) and jytype = 5)
union all 
select '档案——尿检小项' as '项目',COUNT(*) as '档案尿小项数量' from health_record2021.mh_jy_result where jy_parid in(select jy_parid from health_record2021.mh_jy_parson where tjid in (select id from health_record2021.mh_lnr_jkda where org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time) and jytype = 5)
UNION ALL
select '报告血球人数' as '项目',COUNT(1) as '报告血球数量' from health_info2021.mh_jy_xqparson where tjid in (select id from health_info2021.mh_jktj wh