Postgresql数据库高级sql总结3

  • 开发
  • 26
-- 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;

阅读全部

相关推荐

  1. Postgresql数据库高级sql总结3

    2024-04-23 15:48:01       27 阅读

  2. PostgreSQL数据库高级sql总结2

    2024-04-23 15:48:01       39 阅读

  3. PostgreSQL高级sql积累

    2024-04-23 15:48:01       30 阅读

  4. PostgreSQL数据类型总结

    2024-04-23 15:48:01       23 阅读

  5. Mysql数据库高级SQL

    2024-04-23 15:48:01       31 阅读

  6. 【PostgreSQL】- 1.3 PostgreSQL 创建数据库（初始化）

    2024-04-23 15:48:01       37 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-04-23 15:48:01       94 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-04-23 15:48:01       100 阅读

  9. 在Django里面运行非项目文件

    2024-04-23 15:48:01       82 阅读

  19. Python语言-面向对象

    2024-04-23 15:48:01       91 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-04-23 15:48:01       85 阅读

热门阅读

  1. ChatGPT在论文写作中的应用：提升表达与逻辑的双重助力

    2024-04-23 15:48:01       28 阅读

  6. oracle sql 示例

    2024-04-23 15:48:01       30 阅读

  19. python画图笔记

    2024-04-23 15:48:01       39 阅读

  24. python 文件打包(使用pyinstaller)

    2024-04-23 15:48:01       42 阅读

  27. 简单了解Element Plus

    2024-04-23 15:48:01       47 阅读

  28. 第20篇 Vue命令简介

    2024-04-23 15:48:01       27 阅读

  30. win32使用现代样式的公共控件

    2024-04-23 15:48:01       35 阅读