Postgresql数据库高级sql总结3

-- 1、2020年12月15日
	select DISTINCT pp.base_id as baseId,pp.project_type as projectType,pp.title,pp.report_type,pp.language_type as languageType
        ,case  when (pp.report_type='2' and pp.start_date is null and pp.end_date is null) then '申请中'
        when (pp.report_type='1' and date_format(concat(pp.start_date,' 00:00:01'), '%Y-%m-%d %H:%i:%s')< DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i') and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s')>DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then '申请中'
        when (pp.report_type='1' and  date_format(concat(pp.start_date,' 00:00:01'), '%Y-%m-%d %H:%i:%s')>DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'))  then '未开始'
        when (pp.report_type='1' and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s') < DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then '已结束'
        when pp.report_type='3' then '无'
				end reportStatus
        ,case when pp.report_type='3' then '无需申报'
        when pp.report_type='2' then '长期可申报'
        when (pp.report_type='1' and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s') > DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then (case when (to_days(pp.end_date)-to_days(NOW())<1) then 1 else (to_days(pp.end_date)-TO_DAYS(NOW()))  end) else '申报结束'
        end reportDay
        ,pp.key_words as keyWords
        ,pp.deal_depart_id as dealDepartId
        ,pp.money_unit as moneyUnit,pp.money
        ,pp.max_reward as maxReward,dic.base_name as suportType
        ,pp.start_date as startDate,pp.end_date AS endDate
        ,pp.base_create_time
				,th.theme,gov.sendingGovId,gov.sendingGov
        ,usT.useType,usT.useTypeCode
        ,sp.specialPerson
        ,attr.compAttribute
        ,cmp.compScale
        ,pa.base_create_time as auditStatus
        ,pp.city,dict.base_name as cityName
        ,pp.browse_num
        from project_punish pp
        left join (select t.project_id,GROUP_CONCAT(t.theme) as theme from project_punish_theme t
					inner join sys_dict_data dict on t.theme=dict.base_id
					where t.is_delete='0' group by t.project_id) th on pp.base_id=th.project_id
        left join (SELECT gov.project_id, GROUP_CONCAT(gov.sending_gov_id) AS sendingGovId, GROUP_CONCAT(gov.sending_gov ORDER BY dept.sort) AS sendingGov
					FROM project_punish_sending_gov gov LEFT JOIN sys_dept dept ON gov.sending_gov_id = dept.base_id WHERE gov.is_delete = '0' and dept.is_delete='0'
					GROUP BY gov.project_id) gov on pp.base_id=gov.project_id
		left join (select t.project_id,GROUP_CONCAT(dict.base_name) as useType,GROUP_CONCAT(t.use_type) as useTypeCode
					from project_punish_use_type t inner join sys_dict_data dict on t.use_type=dict.base_id
					where t.is_delete='0' and dict.is_delete='0' group by t.project_id) usT  on pp.base_id=usT.project_id
        left join (select sp.project_id,GROUP_CONCAT(sp.special_person) as specialPerson from project_punish_special_person sp
					inner join sys_dict_data dict on sp.special_person=dict.base_id
					where sp.is_delete='0' and dict.is_delete='0'  group by sp.project_id) sp on pp.base_id=sp.project_id
		left join (select c.project_id,GROUP_CONCAT(c.comp_attribute) as compAttribute from project_punish_comp_attribute c
					inner join sys_dict_data dict on c.comp_attribute=dict.base_id
					where c.is_delete='0' and dict.is_delete='0' group by c.project_id) attr on pp.base_id=attr.project_id
		left join (select cs.project_id,GROUP_CONCAT(cs.comp_scale) as compScale from project_punish_comp_scale cs
					inner join sys_dict_data dict on cs.comp_scale=dict.base_id
					where cs.is_delete='0' and dict.is_delete='0' group by cs.project_id) cmp  on pp.base_id=cmp.project_id
        left join sys_dict_data dic on pp.support_type=dic.base_id
        left join policy_project_audit pa on pp.base_id=pa.info_id
		left join sys_dict_data  dict on pp.city=dict.base_id
        where pp.is_delete='0' and pp.audit_status='3'

--2、 按照申报截止时间排序
select DISTINCT pp.base_id as baseId,pp.project_type as projectType,pp.title,pp.report_type,pp.language_type as languageType
        ,case  when (pp.report_type='2' and pp.start_date is null and pp.end_date is null) then '申请中'
        when (pp.report_type='1' and date_format(concat(pp.start_date,' 00:00:01'), '%Y-%m-%d %H:%i:%s')< DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i') and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s')>DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then '申请中'
        when (pp.report_type='1' and  date_format(concat(pp.start_date,' 00:00:01'), '%Y-%m-%d %H:%i:%s')>DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'))  then '未开始'
        when (pp.report_type='1' and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s') < DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then '已结束'
        when pp.report_type='3' then '申请中'
				end reportStatus
      ,case 
        when pp.report_type='2' then '长期可申报'
				when pp.report_type='3' then '无需申报'
        when (pp.report_type='1' and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s') > DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then (case when (to_days(pp.end_date)-to_days(NOW())<1) then 1 else (to_days(pp.end_date)-TO_DAYS(NOW()))  end) else '申报结束'
        end reportDay
        ,pp.key_words as keyWords
        ,pp.deal_depart_id as dealDepartId
        ,pp.money_unit as moneyUnit,pp.money
        ,pp.max_reward as maxReward,dic.base_name as suportType
        ,pp.start_date as startDate,pp.end_date AS endDate
        ,pp.base_create_time
				,th.theme,gov.sendingGovId,gov.sendingGov
        ,usT.useType,usT.useTypeCode
        ,sp.specialPerson
        ,attr.compAttribute
        ,cmp.compScale
				,pa.base_create_time as auditStatus
				,pp.city,dict.base_name as cityName
				,pp.browse_num
        from project_punish pp
    left join (select t.project_id,GROUP_CONCAT(t.theme) as theme from project_punish_theme t
					inner join sys_dict_data dict on t.theme=dict.base_id
					where t.is_delete='0' group by t.project_id) th on pp.base_id=th.project_id
    left join (SELECT gov.project_id, GROUP_CONCAT(gov.sending_gov_id) AS sendingGovId, GROUP_CONCAT(gov.sending_gov ORDER BY dept.sort) AS sendingGov
					FROM project_punish_sending_gov gov LEFT JOIN sys_dept dept ON gov.sending_gov_id = dept.base_id WHERE gov.is_delete = '0' and dept.is_delete='0'
					GROUP BY gov.project_id) gov on pp.base_id=gov.project_id
		left join (select t.project_id,GROUP_CONCAT(dict.base_name) as useType,GROUP_CONCAT(t.use_type) as useTypeCode
					from project_punish_use_type t inner join sys_dict_data dict on t.use_type=dict.base_id
					where t.is_delete='0' and dict.is_delete='0' group by t.project_id) usT  on pp.base_id=usT.project_id
        left join (select sp.project_id,GROUP_CONCAT(sp.special_person) as specialPerson from project_punish_special_person sp
					inner join sys_dict_data dict on sp.special_person=dict.base_id
					where sp.is_delete='0' and dict.is_delete='0'  group by sp.project_id) sp on pp.base_id=sp.project_id
		left join (select c.project_id,GROUP_CONCAT(c.comp_attribute) as compAttribute from project_punish_comp_attribute c
					inner join sys_dict_data dict on c.comp_attribute=dict.base_id
					where c.is_delete='0' and dict.is_delete='0' group by c.project_id) attr on pp.base_id=attr.project_id
		left join (select cs.project_id,GROUP_CONCAT(cs.comp_scale) as compScale from project_punish_comp_scale cs
					inner join sys_dict_data dict on cs.comp_scale=dict.base_id
					where cs.is_delete='0' and dict.is_delete='0' group by cs.project_id) cmp  on pp.base_id=cmp.project_id
    left join sys_dict_data dic on pp.support_type=dic.base_id
		left join policy_project_audit pa on pp.base_id=pa.info_id
		left join sys_dict_data  dict on pp.city=dict.base_id
        where pp.is_delete='0' and pp.audit_status='3' and dict.base_name='国家级'
order by 
   -- dict.sort asc,pa.base_create_time desc,pp.base_create_time desc  最新政策
-- 3、最热政策
   --  dict.sort ASC, pp.browse_num DESC
  
-- dict.sort asc,
-- case when reportDay='申请中' then '1' 
-- case when reportDay='长期可申报' then '2'
-- case when reportDay='无需申报' then '3'
--  end asc
dict.sort ASC,reportStatus asc,
case reportDay
when '申请中' then '1'
when '长期可申报' then '2'
when '无需申报' then '3' 
when '申报结束' then '4'
end desc


-- 4、按照申报开始时间排序
select DISTINCT pp.base_id as baseId,pp.project_type as projectType,pp.title,pp.report_type,pp.language_type as languageType
        ,case  when (pp.report_type='2' and pp.start_date is null and pp.end_date is null) then '申请中'
        when (pp.report_type='1' and date_format(concat(pp.start_date,' 00:00:01'), '%Y-%m-%d %H:%i:%s')< DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i') and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s')>DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then '申请中'
        when (pp.report_type='1' and  date_format(concat(pp.start_date,' 00:00:01'), '%Y-%m-%d %H:%i:%s')>DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'))  then '未开始'
        when (pp.report_type='1' and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s') < DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then '已结束'
        when pp.report_type='3' then '申请中'
				end reportStatus
      ,case 
        when pp.report_type='2' then '长期可申报'
				when pp.report_type='3' then '无需申报'
        when (pp.report_type='1' and date_format(concat(pp.end_date,' 23:59:59'), '%Y-%m-%d %H:%i:%s') > DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i')) then (case when (to_days(pp.end_date)-to_days(NOW())<1) then 1 else (to_days(pp.end_date)-TO_DAYS(NOW()))  end) else '申报结束'
        end reportDay
        ,pp.key_words as keyWords
        ,pp.deal_depart_id as dealDepartId
        ,pp.money_unit as moneyUnit,pp.money
        ,pp.max_reward as maxReward,dic.base_name as suportType
        ,pp.start_date as startDate,pp.end_date AS endDate
        ,pp.base_create_time
				,th.theme,gov.sendingGovId,gov.sendingGov
        ,usT.useType,usT.useTypeCode
        ,sp.specialPerson
        ,attr.compAttribute
        ,cmp.compScale
				,pa.base_create_time as auditStatus
				,pp.city,dict.base_name as cityName
				,pp.browse_num
        from project_punish pp
    left join (select t.project_id,GROUP_CONCAT(t.theme) as theme from project_punish_theme t
					inner join sys_dict_data dict on t.theme=dict.base_id
					where t.is_delete='0' group by t.project_id) th on pp.base_id=th.project_id
    left join (SELECT gov.project_id, GROUP_CONCAT(gov.sending_gov_id) AS sendingGovId, GROUP_CONCAT(gov.sending_gov ORDER BY dept.sort) AS sendingGov
					FROM project_punish_sending_gov gov LEFT JOIN sys_dept dept ON gov.sending_gov_id = dept.base_id WHERE gov.is_delete = '0' and dept.is_delete='0'
					GROUP BY gov.project_id) gov on pp.base_id=gov.project_id
		left join (select t.project_id,GROUP_CONCAT(dict.base_name) as useType,GROUP_CONCAT(t.use_type) as useTypeCode
					from project_punish_use_type t inner join sys_dict_data dict on t.use_type=dict.base_id
					where t.is_delete='0' and dict.is_delete='0' group by t.project_id) usT  on pp.base_id=usT.project_id
        left join (select sp.project_id,GROUP_CONCAT(sp.special_person) as specialPerson from project_punish_special_person sp
					inner join sys_dict_data dict on sp.special_person=dict.base_id
					where sp.is_delete='0' and dict.is_delete='0'  group by sp.project_id) sp on pp.base_id=sp.project_id
		left join (select c.project_id,GROUP_CONCAT(c.comp_attribute) as compAttribute from project_punish_comp_attribute c
					inner join sys_dict_data dict on c.comp_attribute=dict.base_id
					where c.is_delete='0' and dict.is_delete='0' group by c.project_id) attr on pp.base_id=attr.project_id
		left join (select cs.project_id,GROUP_CONCAT(cs.comp_scale) as compScale from project_punish_comp_scale cs
					inner join sys_dict_data dict on cs.comp_scale=dict.base_id
					where cs.is_delete='0' and dict.is_delete='0' group by cs.project_id) cmp  on pp.base_id=cmp.project_id
    left join sys_dict_data dic on pp.support_type=dic.base_id
		left join policy_project_audit pa on pp.base_id=pa.info_id
		left join sys_dict_data  dict on pp.city=dict.base_id
        where pp.is_delete='0' and pp.audit_status='3' and dict.base_name='国家级'
order by 
   -- dict.sort asc,pa.base_create_time desc,pp.base_create_time desc  最新政策
-- 5、最热政策
   --  dict.sort ASC, pp.browse_num DESC
  
-- dict.sort asc,
-- case when reportDay='申请中' then '1' 
-- case when reportDay='长期可申报' then '2'
-- case when reportDay='无需申报' then '3'
--  end asc
dict.sort ASC,reportStatus desc,
case reportDay
when '申请中' then '1'
when '长期可申报' then '2'
when '无需申报' then '3' 
when '申报结束' then '4'
end asc
-- 可用
dict.sort ASC,
reportStatus desc,
reportDay asc


6、简单查询
SELECT * FROM yun_user WHERE type IN(2,1,99) ORDER BY CASE type WHEN 99 THEN 1 WHEN 1 THEN 2 ELSE 3 END, tokencreatetime DESC;