sql复杂查询语句

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