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

已于 2022-02-12 10:05:20 修改
阅读量761 收藏 2
点赞数
文章标签： sql mysql
于 2022-02-12 10:03:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yinna_csdn_cc/article/details/122892110
版权

文章目录

  • 一、序号导表
    • 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 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_jy_xqparson 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) 
UNION ALL
select '报告血球小项' as '项目',COUNT(*) as '报告血球小项' from health_info2021.mh_jy_xqresult where xq_parid in(select xq_parid from health_info2021.mh_jy_xqparson 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))
union all 
select '档案——血球小项' as '项目',COUNT(*) as '档案血球小项' from health_record2021.mh_jy_xqresult where xq_parid in(select xq_parid from health_record2021.mh_jy_xqparson 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))
union ALL 
select '报告用药人数' as '项目',COUNT(DISTINCT jktj_id) as '报告用药数量' from health_info2021.mh_jktj_medicine where jktj_id 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) 
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  and zyyyqk is not null and zyyyqk <>'无'and zyyyqk<>''
union ALL
select '报告用药人数小项' as '项目',COUNT(1) as '报告用药小项' from health_info2021.mh_jktj_medicine where jktj_id 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) 
union all 
select '档案——用药小项' as '项目',sum(LENGTH(zyyyqk)-LENGTH(replace((zyyyqk),';',''))) as '档案用药小项' from health_record2021.mh_lnr_jkda  where   org_id like concat(@org_id,'%') and tjrq >=@start_time and tjrq <=@end_time  and zyyyqk is not null and zyyyqk <>'无'and zyyyqk<>'';


-- 对比生化及各项人数
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 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(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 concat('报告-',IFNULL(item_name,''),IFNULL(item_code,'')) 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) GROUP BY item_code
union ALL
select concat('档案————',IFNULL(item_name,''),IFNULL(item_code,''))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)GROUP BY item_code;


-- 对比血球人数
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 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(1) as '报告血球数量' from health_info2021.mh_jy_xqparson 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) 
union ALL
select '档案——血球人数' as '项目',COUNT(1) as '档案血球数量' from health_record2021.mh_jy_xqparson 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) 
 UNION ALL
select '报告血球小项' as '项目',COUNT(*) as '报告血球数量' from health_info2021.mh_jy_xqresult where xq_parid in(select xq_parid from health_info2021.mh_jy_xqparson 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))
union all 
select '档案——血球小项' as '项目',COUNT(*) as '档案血球小项' from health_record2021.mh_jy_xqresult where xq_parid in(select xq_parid from health_record2021.mh_jy_xqparson 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))
union all 
select concat('报告-',IFNULL(item_name,''),IFNULL(item_code,'')) as '项目',COUNT(*) as '报告血球小项' from health_info2021.mh_jy_xqresult where xq_parid in(select xq_parid from health_info2021.mh_jy_xqparson 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)  ) GROUP BY item_code  
union ALL
select concat('档案————',IFNULL(item_name,''),IFNULL(item_code,''))as '项目',COUNT(*) as '档案血球小项' from health_record2021.mh_jy_xqresult where xq_parid in(select xq_parid from health_record2021.mh_jy_xqparson 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)  )GROUP BY item_code  ;


-- 对比尿检及各项人数
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 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(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 concat('报告-',IFNULL(item_name,''),IFNULL(item_code,'')) 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) GROUP BY item_code
union ALL
select concat('档案————',IFNULL(item_name,''),IFNULL(item_code,''))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)GROUP BY item_code;

三、原始库数据汇总显示（+往年数据对比）

SET @org_id = 37232808;-- 机构号
SET @start_time = 20210101;-- 开始时间
SET @end_time = 20211231;-- 结束时间

SELECT
	b.name,sh.*, xq.*, nj.*, j.*,yaowu.`用药情况`,2021档案.*
FROM
	mh_lnr_jktj j
join health_info2020.mh_lnr_jktj 2020体检 on j.mh_card=2020体检.mh_card
#join health_info2019.mh_lnr_jktj 2019体检 on j.mh_card=2019体检.mh_card
join health_record2021.mh_lnr_jkda 2021档案 on j.mh_card=2021档案.mh_card
join health_record2021.organization b on LEFT(j.org_id,8)=b.org_id
LEFT JOIN mh_jy_parson sp ON j.id = sp.tjid
AND sp.jytype = 1
LEFT JOIN (
	SELECT
		jy_parid,
		sum(

			IF (
				item_code = 'ALT',
				item_value,
				NULL
			)
		) '血清谷丙转氨酶',
		grouP_concat(

			IF (
				item_code = 'ALT',
				item_flag,
				NULL
			)
		) '血清谷丙结论',
		grouP_concat(

			IF (
				item_code = 'ALT',
				item_limit,
				NULL
			)
		) '血清谷丙范围',
		sum(

			IF (
				item_code = 'AST',
				item_value,
				NULL
			)
		) '血清谷草转氨酶',
		grouP_concat(

			IF (
				item_code = 'AST',
				item_flag,
				NULL
			)
		) '血清谷草结论',
		grouP_concat(

			IF (
				item_code = 'AST',
				item_limit,
				NULL
			)
		) '血清谷草范围',
		sum(

			IF (
				item_code = 'TBIL',
				item_value,
				NULL
			)
		) '总胆红素',
		grouP_concat(

			IF (
				item_code = 'TBIL',
				item_flag,
				NULL
			)
		) '总胆红素结论',
		grouP_concat(

			IF (
				item_code = 'TBIL',
				item_limit,
				NULL
			)
		) '总胆红素范围',
		sum(

			IF (
				item_code = 'CRE',
				item_value,
				NULL
			)
		) '血清肌酐',
		grouP_concat(

			IF (
				item_code = 'CRE',
				item_flag,
				NULL
			)
		) '肌酐结论',
		grouP_concat(

			IF (
				item_code = 'CRE',
				item_limit,
				NULL
			)
		) '肌酐范围',
		sum(

			IF (
				item_code = 'UREA',
				item_value,
				NULL
			)
		) '血尿素氮',
		grouP_concat(

			IF (
				item_code = 'UREA',
				item_flag,
				NULL
			)
		) '尿素氮结论',
		grouP_concat(

			IF (
				item_code = 'UREA',
				item_limit,
				NULL
			)
		) '尿素氮范围',
		sum(

			IF (
				item_code = 'TC',
				item_value,
				NULL
			)
		) '总胆固醇',
		grouP_concat(

			IF (
				item_code = 'TC',
				item_flag,
				NULL
			)
		) '总胆固醇结论',
		grouP_concat(

			IF (
				item_code = 'TC',
				item_limit,
				NULL
			)
		) '总胆固醇范围',
		sum(

			IF (
				item_code = 'TG',
				item_value,
				NULL
			)
		) '甘油三酯',
		grouP_concat(

			IF (
				item_code = 'TG',
				item_flag,
				NULL
			)
		) '甘油三脂结论',
		grouP_concat(

			IF (
				item_code = 'TG',
				item_limit,
				NULL
			)
		) '甘油三酯范围',
		sum(

			IF (
				item_code = 'LDL-C',
				item_value,
				NULL
			)
		) '低密度脂蛋白胆固醇',
		grouP_concat(

			IF (
				item_code = 'LDL-C',
				item_flag,
				NULL
			)
		) '低密结论',
		grouP_concat(

			IF (
				item_code = 'LDL-C',
				item_limit,
				NULL
			)
		) '低密范围',
		sum(

			IF (
				item_code = 'HDL-C',
				item_value,
				NULL
			)
		) '高密度脂蛋白胆固醇',
		grouP_concat(

			IF (
				item_code = 'HDL-C',
				item_flag,
				NULL
			)
		) '高密结论',
		grouP_concat(

			IF (
				item_code = 'HDL-C',
				item_limit,
				NULL
			)
		) '高密范围',
		sum(

			IF (
				item_code = 'GLU',
				item_value,
				NULL
			)
		) '空腹血糖',
		grouP_concat(

			IF (
				item_code = 'GLU',
				item_flag,
				NULL
			)
		) '血糖结论',
		grouP_concat(

			IF (
				item_code = 'GLU',
				item_limit,
				NULL
			)
		) '血糖范围'
	FROM
		mh_jy_result
	WHERE
		jy_parid IN (
			SELECT
				jy_parid
			FROM
				mh_jy_parson
			WHERE
				org_id LIKE concat(@org_id, '%')
		)
	GROUP BY
		jy_parid
) AS sh ON sh.jy_parid = sp.jy_parid
LEFT JOIN mh_jy_xqparson xp ON xp.tjid = j.id
LEFT JOIN (
	SELECT
		xq_parid,
		sum(

			IF (
				item_code = 'WBC',
				item_value,
				NULL
			)
		) '白细胞',
		grouP_concat(

			IF (
				item_code = 'WBC',
				item_flag,
				NULL
			)
		) '白细胞结论',
		grouP_concat(

			IF (
				item_code = 'WBC',
				item_limit,
				NULL
			)
		) '白细胞范围',
		sum(

			IF (
				item_code = 'RBC',
				item_value,
				NULL
			)
		) '红细胞',
		grouP_concat(

			IF (
				item_code = 'RBC',
				item_flag,
				NULL
			)
		) '红细胞结论',
		grouP_concat(

			IF (
				item_code = 'RBC',
				item_limit,
				NULL
			)
		) '红细胞范围',
		sum(

			IF (
				item_code = 'HGB',
				item_value,
				NULL
			)
		) '血红蛋白',
		grouP_concat(

			IF (
				item_code = 'HGB',
				item_flag,
				NULL
			)
		) '血红蛋白结论',
		grouP_concat(

			IF (
				item_code = 'HGB',
				item_limit,
				NULL
			)
		) '血红蛋白范围',
		sum(

			IF (
				item_code = 'PLT',
				item_value,
				NULL
			)
		) '血小板',
		grouP_concat(

			IF (
				item_code = 'PLT',
				item_flag,
				NULL
			)
		) '血小板结论',
		grouP_concat(

			IF (
				item_code = 'PLT',
				item_limit,
				NULL
			)
		) '血小板范围',
		sum(

			IF (
				item_code = 'LYMPH#',
				item_value,
				NULL
			)
		) '淋巴细胞数',
		grouP_concat(

			IF (
				item_code = 'LYMPH#',
				item_flag,
				NULL
			)
		) '淋巴数结论',
		grouP_concat(

			IF (
				item_code = 'LYMPH#',
				item_limit,
				NULL
			)
		) '淋巴数范围',
		sum(

			IF (
				item_code = 'LYMPH%',
				item_value,
				NULL
			)
		) '淋巴百分比',
		grouP_concat(

			IF (
				item_code = 'LYMPH%',
				item_flag,
				NULL
			)
		) '淋巴百分比结论',
		grouP_concat(

			IF (
				item_code = 'LYMPH%',
				item_limit,
				NULL
			)
		) '淋巴百分比范围',
		sum(

			IF (
				item_code = 'MONO#',
				item_value,
				NULL
			)
		) '单核数',
		grouP_concat(

			IF (
				item_code = 'MONO#',
				item_flag,
				NULL
			)
		) '单核数结论',
		grouP_concat(

			IF (
				item_code = 'MONO#',
				item_limit,
				NULL
			)
		) '单核数范围',
		sum(

			IF (
				item_code = 'MONO%',
				item_value,
				NULL
			)
		) '单核百分比',
		grouP_concat(

			IF (
				item_code = 'MONO%',
				item_flag,
				NULL
			)
		) '单核百分比结论',
		grouP_concat(

			IF (
				item_code = 'MONO%',
				item_limit,
				NULL
			)
		) '单核百分比范围',
		sum(

			IF (
				item_code = 'NEUT#',
				item_value,
				NULL
			)
		) '中性数',
		grouP_concat(

			IF (
				item_code = 'NEUT#',
				item_flag,
				NULL
			)
		) '中性数结论',
		grouP_concat(

			IF (
				item_code = 'NEUT#',
				item_limit,
				NULL
			)
		) '中性数范围',
		sum(

			IF (
				item_code = 'NEUT%',
				item_value,
				NULL
			)
		) '中性百分比',
		grouP_concat(

			IF (
				item_code = 'NEUT%',
				item_flag,
				NULL
			)
		) '中性百分比结论',
		grouP_concat(

			IF (
				item_code = 'NEUT%',
				item_limit,
				NULL
			)
		) '中性百分比范围',
		sum(

			IF (
				item_code = 'EOS#',
				item_value,
				NULL
			)
		) '嗜酸数',
		grouP_concat(

			IF (
				item_code = 'EOS#',
				item_flag,
				NULL
			)
		) '嗜酸数结论',
		grouP_concat(

			IF (
				item_code = 'EOS#',
				item_limit,
				NULL
			)
		) '嗜酸数范围',
		sum(

			IF (
				item_code = 'EO%',
				item_value,
				NULL
			)
		) '嗜酸百分比',
		grouP_concat(

			IF (
				item_code = 'EO%',
				item_flag,
				NULL
			)
		) '嗜酸比率结论',
		grouP_concat(

			IF (
				item_code = 'EO%',
				item_limit,
				NULL
			)
		) '嗜酸比范围',
		sum(

			IF (
				item_code = 'BASO#',
				item_value,
				NULL
			)
		) '嗜碱数',
		grouP_concat(

			IF (
				item_code = 'BASO#',
				item_flag,
				NULL
			)
		) '嗜碱数结论',
		grouP_concat(

			IF (
				item_code = 'BASO#',
				item_limit,
				NULL
			)
		) '嗜碱数范围',
		sum(

			IF (
				item_code = 'BASO%',
				item_value,
				NULL
			)
		) '嗜碱比',
		grouP_concat(

			IF (
				item_code = 'BASO%',
				item_flag,
				NULL
			)
		) '嗜碱比结论',
		grouP_concat(

			IF (
				item_code = 'BASO%',
				item_limit,
				NULL
			)
		) '嗜碱比范围',
		sum(

			IF (
				item_code = 'HCT',
				item_value,
				NULL
			)
		) '红细胞压积',
		grouP_concat(

			IF (
				item_code = 'HCT',
				item_flag,
				NULL
			)
		) '红压积结论',
		grouP_concat(

			IF (
				item_code = 'HCT',
				item_limit,
				NULL
			)
		) '红压积范围',
		sum(

			IF (
				item_code = 'MCHC',
				item_value,
				NULL
			)
		) '平均红细胞血红蛋白浓度',
		grouP_concat(

			IF (
				item_code = 'MCHC',
				item_flag,
				NULL
			)
		) '平均红白浓度结论',
		grouP_concat(

			IF (
				item_code = 'MCHC',
				item_limit,
				NULL
			)
		) '平均红白浓度范围',
		sum(

			IF (
				item_code = 'MCV',
				item_value,
				NULL
			)
		) '平均红细胞体积',
		grouP_concat(

			IF (
				item_code = 'MCV',
				item_flag,
				NULL
			)
		) '平均红体积结论',
		grouP_concat(

			IF (
				item_code = 'MCV',
				item_limit,
				NULL
			)
		) '平均红体积范围',
		sum(

			IF (
				item_code = 'MCH',
				item_value,
				NULL
			)
		) '平均红细胞血红蛋白含量',
		grouP_concat(

			IF (
				item_code = 'MCH',
				item_flag,
				NULL
			)
		) '平均红白含量结论',
		grouP_concat(

			IF (
				item_code = 'MCH',
				item_limit,
				NULL
			)
		) '平均红白含量范围',
		sum(

			IF (
				item_code = 'RDW_SD',
				item_value,
				NULL
			)
		) '红细胞分布宽度标准差',
		grouP_concat(

			IF (
				item_code = 'RDW_SD',
				item_flag,
				NULL
			)
		) 'r_sd结论',
		grouP_concat(

			IF (
				item_code = 'RDW_SD',
				item_limit,
				NULL
			)
		) 'r_sd范围',
		sum(

			IF (
				item_code = 'MPV',
				item_value,
				NULL
			)
		) '平均血小板体积',
		grouP_concat(

			IF (
				item_code = 'MPV',
				item_flag,
				NULL
			)
		) 'MPV结论',
		grouP_concat(

			IF (
				item_code = 'MPV',
				item_limit,
				NULL
			)
		) 'MPV范围',
		sum(

			IF (
				item_code = 'PCT',
				item_value,
				NULL
			)
		) '血小板压积',
		grouP_concat(

			IF (
				item_code = 'PCT',
				item_flag,
				NULL
			)
		) 'PCT结论',
		grouP_concat(

			IF (
				item_code = 'PCT',
				item_limit,
				NULL
			)
		) 'PCT范围',
		sum(

			IF (
				item_code = 'RDW_CV',
				item_value,
				NULL
			)
		) '红细胞分布宽度CV',
		grouP_concat(

			IF (
				item_code = 'RDW_CV',
				item_flag,
				NULL
			)
		) 'RDW_CV结论',
		grouP_concat(

			IF (
				item_code = 'RDW_CV',
				item_limit,
				NULL
			)
		) 'RDW_CV范围',
		sum(

			IF (
				item_code = 'PDW',
				item_value,
				NULL
			)
		) '血小板分布宽度',
		grouP_concat(

			IF (
				item_code = 'PDW',
				item_flag,
				NULL
			)
		) 'PDW结论',
		grouP_concat(

			IF (
				item_code = 'PDW',
				item_limit,
				NULL
			)
		) 'PDW范围',
		sum(

			IF (
				item_code = 'P-LCR',
				item_value,
				NULL
			)
		) '大型血小板比率',
		grouP_concat(

			IF (
				item_code = 'P-LCR',
				item_flag,
				NULL
			)
		) 'P-LCR结论',
		grouP_concat(

			IF (
				item_code = 'P-LCR',
				item_limit,
				NULL
			)
		) 'P-LCR范围',
		sum(

			IF (
				item_code = 'P-LCC',
				item_value,
				NULL
			)
		) '大血小板数目',
		grouP_concat(

			IF (
				item_code = 'P-LCC',
				item_flag,
				NULL
			)
		) 'P-LCC结论',
		grouP_concat(

			IF (
				item_code = 'P-LCC',
				item_limit,
				NULL
			)
		) 'P-LCC范围',
		sum(

			IF (
				item_code = 'GRNC',
				item_value,
				NULL
			)
		) '粒细胞数目',
		grouP_concat(

			IF (
				item_code = 'GRNC',
				item_flag,
				NULL
			)
		) '粒细胞数结论',
		grouP_concat(

			IF (
				item_code = 'GRNC',
				item_limit,
				NULL
			)
		) '粒细胞数范围',
		sum(

			IF (
				item_code = 'GRNR',
				item_value,
				NULL
			)
		) '粒细胞百分比',
		grouP_concat(

			IF (
				item_code = 'GRNR',
				item_flag,
				NULL
			)
		) '粒细胞百分比结论',
		grouP_concat(

			IF (
				item_code = 'GRNR',
				item_limit,
				NULL
			)
		) '粒细胞百分比范围',
		sum(

			IF (
				item_code = 'MIDC',
				item_value,
				NULL
			)
		) '中间细胞数目',
		grouP_concat(

			IF (
				item_code = 'MIDC',
				item_flag,
				NULL
			)
		) '中间细胞数结论',
		grouP_concat(

			IF (
				item_code = 'MIDC',
				item_limit,
				NULL
			)
		) '中间细胞数范围',
		sum(

			IF (
				item_code = 'MIDR',
				item_value,
				NULL
			)
		) '中间细胞百分比',
		grouP_concat(

			IF (
				item_code = 'MIDR',
				item_flag,
				NULL
			)
		) '中间细胞百分比结论',
		grouP_concat(

			IF (
				item_code = 'MIDR',
				item_limit,
				NULL
			)
		) '中间细胞百分比范围'
	FROM
		mh_jy_xqresult
	WHERE
		xq_parid IN (
			SELECT
				xq_parid
			FROM
				mh_jy_xqparson
			WHERE
				org_id LIKE concat(@org_id, '%')
		)
	GROUP BY
		xq_parid
) AS xq ON xq.xq_parid = xp.xq_parid

LEFT JOIN mh_jy_parson np ON j.id = np.tjid
AND np.jytype = 5
LEFT JOIN (
	SELECT
		jy_parid,
		grouP_concat(

			IF (
				item_code = 'KET',
				item_value,
				NULL
			)
		) '尿酮体',
		grouP_concat(

			IF (
				item_code = 'BLD',
				item_value,
				NULL
			)
		) '尿潜血',
		grouP_concat(

			IF (
				item_code = 'PRO',
				item_value,
				NULL
			)
		) '尿蛋白',
		grouP_concat(

			IF (
				item_code = 'GLU_N',
				item_value,
				NULL
			)
		) '尿糖',
		grouP_concat(

			IF (
				item_code = 'BIL',
				item_value,
				NULL
			)
		) '尿胆红素',
		grouP_concat(

			IF (
				item_code = 'LEU',
				item_value,
				NULL
			)
		) '尿白细胞',
		grouP_concat(

			IF (
				item_code = 'NIT',
				item_value,
				NULL
			)
		) '亚硝酸盐',
		grouP_concat(

			IF (
				item_code = 'URO',
				item_value,
				NULL
			)
		) '尿胆原',
		grouP_concat(

			IF (
				item_code = 'VC',
				item_value,
				NULL
			)
		) '抗坏血酸',
		grouP_concat(

			IF (
				item_code = 'PH',
				item_value,
				NULL
			)
		) 'PH',
		grouP_concat(

			IF (
				item_code = 'SG',
				item_value,
				NULL
			)
		) '尿比重'
	FROM
		mh_jy_result
	WHERE
		jy_parid IN (
			SELECT
				jy_parid
			FROM
				mh_jy_parson
			WHERE
				org_id LIKE concat(@org_id, '%')
		)
	GROUP BY
		jy_parid
) AS nj ON nj.jy_parid = np.jy_parid
LEFT JOIN (
	SELECT
		jktj_id,
		group_concat(
			m.drug_name,
			',',
			m.drug_usage_code,
			',',
			m.consumption,
			',',
			m.medicationtime,
			',',
			m.compliance SEPARATOR ';'
		) '用药情况'
	FROM
		mh_jktj_medicine m WHERE m.jktj_id in(SELECT id from mh_jktj WHERE org_id like concat(@org_id, '%') and tjrq >=@start_time and tjrq <=@end_time)
	GROUP BY
		jktj_id
) AS yaowu ON j.id = yaowu.jktj_id
WHERE
	j.org_id LIKE concat(@org_id, '%')
AND j.is_fufei = 0
AND j.tjrq >=@start_time
AND j.tjrq <=@end_time
and j.xdt_flag is not null and j.bc_flag is not null 
and nj.尿酮体 is not null and sh.血清肌酐 is not null and xq.白细胞 is not null 
and j.lxdh is not null
#and 2021档案.up_status in('21','31')
and j.age>65
GROUP BY j.org_name
limit 5
#ORDER BY length(2021档案.jkpjyc) desc limit 30

 四、2021机构进度查询

SET @start_time = 20210101;
SET @end_time = 20211231;
 
SELECT
aa.机构号,
	aa.机构,
	aa.开始查体日期,
	aa.最近查体日期,
	aa.查体总数,
	aa.报告打印数量,
	cc.档案打印数量,
	bb.上传成功总数
FROM
	(
		SELECT
		left(d.org_id,8) as '机构号',
			d.NAME AS '机构',
			COUNT(1) AS '查体总数',
			DATE(min(b.tjrq)) AS '开始查体日期',
			DATE(max(b.tjrq)) AS '最近查体日期',
			count(
				CASE
				WHEN (b.dy_detail IS NOT NULL or b.zjdysj is not null) THEN
					'1'
				END
			) AS '报告打印数量'
		FROM
			 health_info2021.mh_lnr_jktj b 
		JOIN health_record2021.organization d ON LEFT (b.org_id, 8) = d.org_id
		WHERE  b.org_id regexp '50011301|50011110|37021406|37110212|37052105|37232810|37110104|37110103|37112106|37112103|37110208|37050201|37022105|61052401|51018601' 
and b.tjrq BETWEEN @start_time
		AND @end_time
		AND b.is_fufei = 0
		GROUP BY
			LEFT (b.org_id, 8) 
	) AS aa

LEFT JOIN (
	SELECT
left(d.org_id,8) as '机构号',
		d.NAME AS '机构',
		count(*) '档案打印数量'
	FROM
		health_record2021.mh_lnr_jkda a
	#RIGHT JOIN health_info2021.mh_jktj b ON a.mh_card = b.mh_card
	left JOIN health_record2021.mh_jkda_state c ON a.id = c.id
	JOIN health_record2021.organization d ON LEFT (a.org_id, 8) = d.org_id
	WHERE
 a.tjrq BETWEEN @start_time
	AND @end_time
	AND a.is_fufei = 0
	AND c.dy_detail IS NOT NULL
	GROUP BY
		LEFT (a.org_id, 8)
) AS cc ON aa.机构 = cc.机构


LEFT JOIN (
	SELECT
left(d.org_id,8) as '机构号',
		d.NAME AS '机构',
		count(1) '上传成功总数'
	FROM
		health_record2021.mh_lnr_jkda a
   left JOIN health_info2021.mh_jktj b ON a.mh_card = b.mh_card
	JOIN health_record2021.organization d ON LEFT (b.org_id, 8) = d.org_id
	WHERE
 b.tjrq BETWEEN @start_time
	AND @end_time
	AND (a.up_status IN ('21', '31') or  a.zl_up_status in ('21', '31'))
	AND a.is_fufei = 0
	GROUP BY
		LEFT (b.org_id, 8)
) AS bb ON aa.机构 = bb.机构
;

总结：此文章仅做为记录使用。 

promise~~
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
Mac 数据库mysql查看软件 navicat
11-19
亲测可用，Mac 数据库mysql查看软件 navicat，
navicat MySQL 语句
weixin_42627035的博客
12-18 1424
1.使用navicat.exe可视化工具管理数据库 2.SELECT * FROM t_user WHERE uid =1; SELECT uname FROM t_user；//显示所有 SELECT DISTINCT uname FROM t_user;//如需从 uname " 列中仅选取唯一不同的值 SELECT * FROM t_user WHERE uid &gt;2 AND ...
navicat操作mysql_Navicat Premium操作MySQL数据库(执行sql语句)
weixin_30095015的博客
01-19 2573
一. Navicat 简介1. 什么是Navicat ?Navicat是一个强大的MySQL数据库管理和开发工具。Navicat为专业开发者提供了一套强大的足够尖端的工具，但它对于新用户仍然是易于学习。Navicat,使用了极好的图形用户界面(GUI)，可以让你用一种安全和更为容易的方式快速和容易地创建、组织、存取和共享信息。用户可完全控制 MySQL 数据库和显示不同的管理资料，包括一个多功能的...
Mysql返回一条空数据_打印mysql语句在Navicat有返回值，但是代码运行返回是空的...
weixin_35753291的博客
01-28 312
问题描述某处调用db 公共函数getRow方法，返回值是：2018-09-14 17:11:50 ERR: Array([a] =>[b] =>)但是打印出运行的mysql语句在Navicat运行是有值的！别处也调用这个方法，都是正常的，甚至这一段运行相同业务处理别的数据，返回值也是正常的问题出现的环境背景及自己尝试过哪些方法查看getRow内部方法 接受到的sql是正常(外部运行也是...
标准SQL语句大全【持续更新】（navicat12版亲测有效）
weixin_44077556的博客
05-02 2万+
提示：用ctrl+F快速查找相关指令哦 -- 创建数据库 create database test_sql; -- 修改数据库名称(只有 sysadmin 和 dbcreator 固定服务器角色的成员才答能执行 sp_renamedb) sp_renamedb 'test_sql','exam_sql'; -- 删除数据库 drop database test_sql; -- 引用数据库 ...
mysql语句总结篇-以navicat为可视化界面
qq_42871249的博客
11-18 480
一、检索 查找表所有的记录，*代表所有记录，2019jw__kscj是我的表名，一个记录学生成绩的表 navicat结果 SELECT * FROM `2019jw__kscj` 查找某列，例如学号xh SELECT xh FROM 2019jw__kscj 查找某列的前几行，例如学号xh的前五行 SELECT xh FROM 2019jw__kscj LIMIT 5 也可以从任意行开始选...
mysql数据库远程工具之Navicat安装与配置
12-15
mysql数据库远程工具之Navicat安装与配置 1、什么Navicat ？ Navicat是一套快速、可靠并价格相当便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业...
MySQL数据库：使用NAVICAT工具更新数据.pptx
05-29
使用NAVICAT工具更新数据 课程目标 1）掌握 ——使用navicat工具进行数据插入的方法； 2）掌握 ——使用navicat工具进行数据修改的方法； 3）掌握 ——使用navicat工具进行数据删除的方法。 使用navicat工具插入数据...
mysql数据库和Navicat for MySQL
08-27
mysql 数据库安装包mysql数据库和Navicat for MySQLmysql数据库和Navicat for MySQL
数据库客户端 DBeaver、Navicat（Mysql、Oracle）
04-09
数据库客户端 DBeaver、Navicat（Mysql、Oracle）
Navicat for MySql常用命令
LLkk0099的博客
12-05 4152
注：SQL语言是以;来结束一个语句的。在MySql中命令好像是不区分大小写的。 一、MySQL常用语句及数据类型 1、创建数据库 mysql&gt; create database &lt;数据库名&gt; ; 例：mysql&gt; create database student; //创建一个student数据库 2、显示所有数据库 mysql&gt; SHOW DATABASES; ...
怎么用sql语句_Navicat MySQL：写SQL的一万种偷懒方法（ing）
weixin_33501343的博客
01-12 238
写在前面：劝大家踏踏实实写SQL！不要偷懒！FIRST Tools->History log打开可查看此数据库中所有操作记录对应的sql语句（复制粘贴后修改字段值运行即可）SECOND单表查询点击这个漏斗，在左栏选择字段，中间栏选择条件，右栏输入值，点击对号，即可执行查询操作（条件可选项很多，建议闲着的时候可以试试各种条件，自己筛选着玩玩点击对号后，在最底部会自动生成和所选条件相符的查询语句...
MySql常用命令和语法以及可视化工具Navicat的使用
康康
12-03 4859
今天在课程的基础上，又看了网上的教程 ，自己动手创建了几个数据库，发现经常用到的命令就那些。现在记录下以备忘记。 注：SQL语言是以;来结束一个语句的。在MySql中命令好像是不区分大小写的。 一、MySQL常用语句及数据类型 1、创建数据库 mysql> create database ;例：mysql> create database student; //创建一个student数据
Mysql基础操作
weixin_30362083的博客
01-09 91
1、数据库操作 　　进入命令行：mysql -uroot -p123456 　　查看数据库：show databases; 　　查看数据库创建语句：show create database test; 　　创建数据库：create database test charset utf8mb4; 　　选择数据库：use test; 　　删除数据库：drop database test; ...
navicat for mysql
weixin_44898301的博客
09-01 286
https://blog.csdn.net/a599174211/article/details/82795658
MySQL常使用到的语句
热门推荐
qq_45069279的博客
04-24 9万+
学习的时候，有不少经常使用的mysql语句，下面记录一下学习时经常使用到的mysql语句。 MySQL语句一、 MySQl程序操作1.1 在终端上操作1.2 通用语法二、 数据库命令行操作2.1 操作数据库2.2 操作数据表2.3 创建表实例三、 crud命令行操作3.1 增加3.2 读取3.3 更新3.4 删除四、 DQL（数据库查询语句）4.1 查询语句4.2 条件查询4.3 排序查询4...
[SWPUCTF-2022-新生赛]ez_sql
liaochonxiang的博客
04-24 400
根据提示，POST传参得到假的flag。
SQL底层执行过程
最新发布
qq_14886109的博客
04-28 781
SQL底层执行过程，INNODB，二阶段提交
SQL的基础语句
qq_51321722的博客
04-22 1337
因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。包含左边表的全部行（不管右边的表中是否存在与他们匹配的行），以及右边表中全部匹配的行。包含右边表的全部行（不管右边的表中是否存在与他们匹配的行），以及左边表中全部匹配的行。
navicat工具MYsql数据库查询语句参数化
06-10
1. 打开Navicat，连接到MySQL数据库。 2. 在查询窗口中编写SQL查询语句。例如： SELECT * FROM users WHERE username = :username AND password = :password 这里的“:username”和“:password”是参数名，可以...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

promise~~

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值