1.描述:
在数据库中使用sql语句查询数据,常常需要做报表和统计分析。难免需要做很多的复杂查询。
解决方案:
调用数据库的自带函数(我用的是mysql的函数)来查询(函数中嵌套函数来实现复杂查询)
2.实例:
求差函数: sum
函数嵌套函数查询:(使用timestampdiff函数查询2002-01-01到现在(2019-04-08 12:00:00 )相差了多少天)
select timestampdiff(day ,'2002-01-01',x) as timeDiff from (select NOW() as x) a;
注释:
select timestampdiff(day ,nowTime,abledTime) as ltimeDiff from (select NOW() as nowTime) as f,(
select SP_REGISTRATION_DATE as abledTime from sys_supplies where SP_LICENCE_NUMBER='粤食药监械生产许20142525号' and SP_REGISTRATION_DATE-(select NOW())>0
) as bb;
select ltimeDiff as 证书有效天数,count(*)有效证书的个数 from
(select timestampdiff(day ,nowTime,abledTime) as ltimeDiff from (select NOW() as nowTime) as f,(
select SP_REGISTRATION_DATE as abledTime from sys_supplies where SP_LICENCE_NUMBER='粤食药监械生产许20142525号' and SP_REGISTRATION_DATE-(select NOW())>0
) as bb) as ableDayTable
group by ltimeDiff
select sys_supplies.sp_wz_name as 耗材名称,sys_order.EXEC_DEPT_NAME as 科室名称 from
sys_supplies,sys_order,sys_rfid,sys_order_item,sys_apply_setting
where sys_order.id=sys_order_item.OD_ID and sys_rfid.id=sys_order_item.R_ID and sys_supplies.ID=sys_rfid.SP_ID
and sys_apply_setting.SP_ID=sys_supplies.ID
select sys_supplies.SP_GENERICNAME as suppliesName,sys_hospital.H_NAME as hDeptName from sys_supplies,sys_rfid,sys_apply_setting,sys_hospital where sys_supplies.ID=sys_rfid.SP_ID and sys_apply_setting.SP_ID=sys_supplies.ID AND sys_apply_setting.O_ID=sys_hospital.ID and sys_hospital.H_FINDLEVEL=3 and sys_rfid.RSP_NUM <sys_apply_setting.AS_SP_WARNING_NUM