PostgreSQL优化-exists改关联查询、with里面的where条件放到最终查询join的on里面

已于 2023-08-31 07:50:01 修改
阅读量246 收藏
点赞数
分类专栏： PostgreSQL 文章标签： postgresql
于 2023-08-30 18:38:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tttzzzqqq2018/article/details/132588464
版权

PostgreSQL的exists改关联查询

PostgreSQL的exists改关联查询

一、exists改关联查询

二、with里面的where条件放到最终查询join的on里面

三、示例SQL

-- drop table tzq_t4 cascade;
-- create table tzq_t3 as 
-- explain(analyze true,verbose true,costs true,buffers true,timing true,format text)
-- 
-- create table tzq_t4 as 
WITH t AS (
	select t
		.FUND_TRANSFER_APPLY_ID,
		t.proc_inst_id AS procInstId,
		(
		select
			fmt.message 
		from
			fnd.tpl_fd_message_t fmt 
		where
			fmt.app_name = 'etreasury' 
			and fmt.message_key = t.workflow_status 
			and fmt.language = 'en_US' 
			limit 1 
		) as status,
		coalesce (
			(
			select
				( select u.lname from fnd.tpl_user_t u where u.user_id = ta.assignee_ :: numeric ) 
			from
				ewf.act_ru_task AS ta 
			where
				ta.proc_inst_id_ = t.receive_procinst_id 
			),
			(
			SELECT
				string_agg ( ( SELECT u.lname FROM fnd.tpl_user_t u WHERE u.user_id = twhi.user_id_ ), ',' ) 
			FROM
				ewf.tpl_wf_hi_identitylink_t AS twhi,
				ewf.act_hi_taskinst AS art 
			WHERE
				twhi.task_id_ = art.id_ 
				AND art.proc_inst_id_ :: VARCHAR = t.receive_procinst_id 
				AND ( art.delete_reason_ != 'completed' OR art.delete_reason_ IS NULL ) 
				AND t.receive_wf_status = art.description_ 
				AND art.description_ != 'etreasury.deal.receivefund.flow.completed' 
			) 
		) AS currentHandler,
		t.deal_no as dealNumber,
		t.ticket_no as ticketNumber,
		( select im.instrument_name from ets.ets_bas_instrument_t im where im.bas_instrument_id = t.bas_instrument_id limit 1 ) as instrument,
		( select currency_code from ets.ets_mdm_rm_currency_info_t ct where ct.currencies_id = t.payment_ccy_id limit 1 ) as paymentCurrency,
		round( t.payment_amount, 2 ) as paymentAmount,
		to_char( t.payment_date, 'yyyy-MM-dd' ) as paymentDate,
		round( t.equivalent_usd_amount, 2 ) as equivalentUSDAmount,
	CASE
			
			WHEN t.urgent_flag = 1 THEN
			'Yes' 
			WHEN t.urgent_flag = 0 THEN
			'No' 
		END AS urgent,
		round( t.wht_amount, 2 ) as wHTAmount,
		( select et.management_entity_en_name from ets.ets_bas_management_entity_t et where et.bas_management_entity_id = t.back_to_back_subsidiary_id limit 1 ) as b2B3rdParty,
		t.back_to_back_deal_no as b2BDealNo,
		( select et.management_entity_en_name from ets.ets_bas_management_entity_t et where et.bas_management_entity_id = t.source_mgt_entity_id limit 1 ) as sourceEntity,
		( select ct.company_code from ets.ets_company_t ct where ct.company_id = t.source_company_id limit 1 ) as sourceCompanyCode,
		( select bt.bank_account_no from ets_bbam.finbas_bank_account_t bt where bt.bank_account_id = t.source_bank_account_id limit 1 ) as sourceBankAccountNo,
		( select currency_code from ets.ets_mdm_rm_currency_info_t ct where ct.currencies_id = t.source_acc_ccy_id limit 1 ) as sourceAccountCurrency,
		round( t.source_amount, 2 ) as sourceAmount,
		(
		select
			bnt.account_nature 
		from
			(
			SELECT
				n.bas_account_nature_id,
				n.account_nature_name,
			CASE
					
					WHEN coalesce ( n.parent_id :: VARCHAR, '' ) = '' THEN
					n.account_nature_name ELSE n.parent_name 
				END AS account_nature 
			FROM
				(
				SELECT t
					.bas_account_nature_id,
					t.parent_id,
					( SELECT p.account_nature_name FROM ets_bbam.ets_bas_account_nature_t p WHERE p.bas_account_nature_id = t.parent_id ) parent_name,
					t.account_nature_name,
					t.account_nature_name_cn,
					t.status,
					t.account_nature_type 
				FROM
					ets_bbam.ets_bas_account_nature_t t 
				) AS n 
			) bnt 
		where
			bnt.bas_account_nature_id = t.source_account_nature 
		) as sourceAccountNature,
		t.source_bank_name as sourceBankName,
		(
		SELECT
			fbt.cparty_short_name 
		FROM
			ets_bbam.finbas_bank_account_t bat,
			ets_bbam.finbas_bankacc_currency_t bct,
			ets_bbam.finbas_cparty_t fbt 
		WHERE
			bct.owner_id = bat.bank_account_id 
			AND bat.bank_id = fbt.cparty_id 
			AND bct.currency_id = t.source_acc_ccy_id 
			AND bat.bank_account_id = t.source_bank_account_id 
			AND bct.owner_type = 1 
			LIMIT 1 
		) as sourceBankShortName,
		t.source_bank_branch_name as sourceBankBranchName,
		t.source_convert_rate as sourceFXconvertRate,
		( select et.management_entity_en_name from ets.ets_bas_management_entity_t et where et.bas_management_entity_id = t.destination_mgt_entity_id  limit 1 ) as destinationEntity,
		( select ct.company_code from ets.ets_company_t ct where ct.company_id = t.destination_company_id  limit 1 ) as destinationCompanyCode,
		( select bt.bank_account_no from ets_bbam.finbas_bank_account_t bt where bt.bank_account_id = t.destination_bank_account_id  limit 1 ) as destinationBankAccountNo,
		( select currency_code from ets.ets_mdm_rm_currency_info_t ct where ct.currencies_id = t.destination_acc_ccy_id limit 1 ) destinationAccountCurrency,
		round( t.destination_amount, 2 ) as destinationAmount,
		(
		select
			bnt.account_nature 
		from
			(
			SELECT
				n.bas_account_nature_id,
				n.account_nature_name,
			CASE
					
					WHEN coalesce ( n.parent_id :: VARCHAR, '' ) = '' THEN
					n.account_nature_name ELSE n.parent_name 
				END AS account_nature 
			FROM
				(
				SELECT t
					.bas_account_nature_id,
					t.parent_id,
					( SELECT p.account_nature_name FROM ets_bbam.ets_bas_account_nature_t p WHERE p.bas_account_nature_id = t.parent_id ) parent_name,
					t.account_nature_name,
					t.account_nature_name_cn,
					t.status,
					t.account_nature_type 
				FROM
					ets_bbam.ets_bas_account_nature_t t 
				) AS n 
			) bnt 
		where
			bnt.bas_account_nature_id = t.destination_account_nature 
		) as destinationAccountNature,
		t.destination_bank_name as destinationBankName,
		(
		SELECT
			fbt.cparty_short_name 
		FROM
			ets_bbam.finbas_bank_account_t bat,
			ets_bbam.finbas_bankacc_currency_t bct,
			ets_bbam.finbas_cparty_t fbt 
		WHERE
			bct.owner_id = bat.bank_account_id 
			AND bat.bank_id = fbt.cparty_id 
			AND bct.currency_id = t.destination_acc_ccy_id 
			AND bat.bank_account_id = t.destination_bank_account_id 
			AND bct.owner_type = 1 
			LIMIT 1 
		) as destinationBankShortName,
		t.destination_bank_branch_name as destinationBankBranchName,
		t.destination_convert_rate as destinationFXconvertRate,
		( select u.lname from fnd.tpl_user_t u where u.user_id = t.created_by limit 1 ) as dealer,
		to_char( t.deal_date, 'yyyy-MM-dd' ) as dealDate,
		to_char( t.creation_date, 'yyyy-MM-dd hh24:mi:ss' ) as createTime,
		t.payment_method as paymentMethod,
		t.document_no as documentNo,
		t.deal_date,
	CASE
			
			WHEN t.fund_transfer_reason = 0 THEN
			'Drawdown' 
			WHEN t.fund_transfer_reason = 1 THEN
			'Repayment' 
			WHEN t.fund_transfer_reason = 2 THEN
			'' 
		END AS fundTransferReason,
	CASE
			
			WHEN t.print_Status = '0' THEN
			'Unprinted' 
			WHEN t.print_Status = '1' THEN
			'Printed' ELSE'' 
		END AS printStatus,
		t.reprint_reason reprintReason
		,t.source_mgt_entity_id AS source_mgt_entity_id
	from
		ets.ets_deal_fund_transfer_apply_t t,
		ets.ets_bas_management_entity_t me 
		-- 新增关联表
		,(SELECT distinct lit.item_name
			 FROM fnd.tpl_lookup_item_t lit 
			WHERE 1=1 
				AND lit.classify_code = 'RECEIVE_I18NSTATUS' 
				AND lit.app_name = 'etreasury' 
				) lit
	where
	
		me.bas_management_entity_id = t.source_mgt_entity_id
		-- 关联查询条件
		and t.receive_wf_status = lit.item_name 
-- 逻辑修改，改成关联查询
-- 		and exists ( SELECT 1
-- 		               FROM fnd.tpl_lookup_item_t lit 
-- 		              WHERE t.receive_wf_status = lit.item_name 
-- 									  AND lit.classify_code = 'RECEIVE_I18NSTATUS' 
-- 										AND lit.app_name = 'etreasury' 
-- 										) 
										
		and t.source_id != 2 
		and t.channel_delete_flag = 0 

-- 之前的快的		
-- 		me.bas_management_entity_id = t.source_mgt_entity_id 
-- 			and exists ( SELECT 1 FROM fnd.tpl_lookup_item_t lit 
-- 			              WHERE t.workflow_status = lit.item_name 
-- 										AND lit.classify_code = 'FUNDTRANSFER_I18NSTATUS' 
-- 										AND app_name = 'etreasury' ) 
-- 			and t.source_id != 2 
-- 			and t.channel_delete_flag = 0 

-- 逻辑修改，往下面放
-- 		and t.source_mgt_entity_id not in (
-- 						'229',
-- 						'247',
-- 						'351',
-- 						'230',
-- 						'506',
-- 						'112',
-- 						'224',
-- 						'231',
-- 						'233',
-- 						'221',
-- 						'1845',
-- 						'294',
-- 						'2145',
-- 						'204' 
-- 					) 

	)

,ttmp AS (

	SELECT
		TA.* 
	FROM
		(
		
		SELECT
			TWL.ASSIGNEE,
			
			(
			SELECT coalesce
				( MAX ( TFM.MESSAGE ), TWL.TASK_DEF_NAME ) 
			FROM
				fnd.TPL_FD_MESSAGE_T TFM 
			WHERE
				TFM.MESSAGE_KEY = TWL.TASK_DEF_NAME 
				AND TFM.LANGUAGE = 'en_US' 
				AND TFM.APP_NAME = 'etreasury' 
			) TASK_NAME,
			
			PROCESS_INSTANCE_ID AS PROC_INST_ID_,
			TWL.CREATION_DATE,
			ROW_NUMBER ( ) OVER ( PARTITION BY TWL.PROCESS_INSTANCE_ID, TWL.TASK_DEF_NAME ORDER BY TWL.CREATION_DATE DESC ) RN 
		FROM
			EWF.TPL_WF_LOG_T TWL 
		WHERE
			TWL.TYPE = 0 
			AND TWL.PROCESS_INSTANCE_ID IN ( SELECT t.procInstId FROM t ) 
		) TA 
	WHERE
		RN = 1 
	)
	
	, ttmp2 AS (
	
	select
		ttmp.PROC_INST_ID_,
	CASE
			
			WHEN ttmp.TASK_NAME = 'Supervisor' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as supervisor,
	CASE
			
			WHEN ttmp.TASK_NAME = 'Approver' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as Approver,
	CASE
			
			WHEN ttmp.TASK_NAME = 'Accountant' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END AS ACCOUNTANT,
	CASE
			
			WHEN ttmp.TASK_NAME = 'Accountant Reviewer' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as accountantReviewer,
	CASE
			
			WHEN ttmp.TASK_NAME = 'Actual Pay Cashier' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as actualPayCashier,
	CASE
			
			WHEN ttmp.TASK_NAME = 'Local Cashier' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as localCashier,
	CASE
			
			WHEN ttmp.TASK_NAME = 'eBanking Group C' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as eBankingGroupC,
	CASE
			
			WHEN ttmp.TASK_NAME = 'eBanking Group B' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as eBankingGroupB,
	CASE
			
			WHEN ttmp.TASK_NAME = 'eBanking Group A' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as eBankingGroupA,
	CASE
			
			WHEN ttmp.TASK_NAME = 'Bill Group B' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END AS billGroupB,
	CASE
			
			WHEN ttmp.TASK_NAME = 'Bill Group A' THEN
			( ttmp.ASSIGNEE || ',' || TO_CHAR( ttmp.CREATION_DATE, 'yyyy-mm-dd hh24:mi:ss' ) ) ELSE'' 
		END as billGroupA 
	from
		ttmp 
	)

	,t2 AS (
	
	SELECT
		proc_inst_id_,
		( regexp_match ( approver :: text, '[^,]+' ) ) [ 1 ] AS approver,
		( SELECT ( regexp_matches( approver :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS approverTime,
		( regexp_match ( accountant :: text, '[^,]+' ) ) [ 1 ] AS accountant,
		( SELECT ( regexp_matches( accountant :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS accountantSignTime,
		( regexp_match ( accountantReviewer :: text, '[^,]+' ) ) [ 1 ] AS reviewer,
		( SELECT ( regexp_matches( accountantReviewer :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS reviewerSignTime,
		( regexp_match ( actualPayCashier :: text, '[^,]+' ) ) [ 1 ] AS cashier,
		( SELECT ( regexp_matches( actualPayCashier :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS cashierSignTime,
		( regexp_match ( localCashier :: text, '[^,]+' ) ) [ 1 ] AS localCashier,
		( SELECT ( regexp_matches( localCashier :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS localCashierSignTime,
		( regexp_match ( eBankingGroupC :: text, '[^,]+' ) ) [ 1 ] AS eBankingGroupC,
		( SELECT ( regexp_matches( eBankingGroupC :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS eBankingGroupCSignTime,
		( regexp_match ( eBankingGroupB :: text, '[^,]+' ) ) [ 1 ] AS eBankingGroupB,
		( SELECT ( regexp_matches( eBankingGroupB :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS eBankingGroupBSignTime,
		( regexp_match ( eBankingGroupA :: text, '[^,]+' ) ) [ 1 ] AS eBankingGroupA,
		( SELECT ( regexp_matches( eBankingGroupA :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS ebankingGroupASignTime,
		( regexp_match ( billGroupB :: text, '[^,]+' ) ) [ 1 ] AS billGroupB,
		( SELECT ( regexp_matches( billGroupB :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS billGroupBSignTime,
		( regexp_match ( billGroupA :: text, '[^,]+' ) ) [ 1 ] AS billGroupA,
		( SELECT ( regexp_matches( billGroupA :: text, '[^,]+', 'g' ) ) [ 1 ] LIMIT 1 OFFSET 2-1 ) AS billGroupASignTime 
	FROM
		(
		SELECT
			TA.* 
		FROM
			(
			SELECT
				PROC_INST_ID_,
				MAX ( supervisor ) AS supervisor,
				MAX ( Approver ) AS Approver,
				MAX ( ACCOUNTANT ) AS ACCOUNTANT,
				MAX ( accountantReviewer ) AS accountantReviewer,
				MAX ( actualPayCashier ) AS actualPayCashier,
				MAX ( localCashier ) AS localCashier,
				MAX ( eBankingGroupC ) AS eBankingGroupC,
				MAX ( eBankingGroupB ) AS eBankingGroupB,
				MAX ( eBankingGroupA ) AS eBankingGroupA,
				MAX ( billGroupB ) AS billGroupB,
				MAX ( billGroupA ) AS billGroupA 
			FROM
				ttmp2 
			group by
				PROC_INST_ID_ 
			) TA 
		) tt 
	) 

	SELECT t
	.FUND_TRANSFER_APPLY_ID,
	t.procInstId,
	t.status,
	t.currentHandler,
	t.dealNumber,
	t.ticketNumber,
	t.instrument,
	t.paymentCurrency,
	t.paymentAmount,
	t.paymentDate,
	t.equivalentUSDAmount,
	t.urgent,
	t.wHTAmount,
	t.b2B3rdParty,
	t.b2BDealNo,
	t.sourceEntity,
	t.sourceCompanyCode,
	t.sourceBankAccountNo,
	t.sourceAccountCurrency,
	t.sourceAmount,
	t.sourceAccountNature,
	t.sourceBankName,
	t.sourceBankShortName,
	t.sourceBankBranchName,
	t.sourceFXconvertRate,
	t.destinationEntity,
	t.destinationCompanyCode,
	t.destinationBankAccountNo,
	t.destinationAccountCurrency,
	t.destinationAmount,
	t.destinationAccountNature,
	t.destinationBankName,
	t.destinationBankShortName,
	t.destinationBankBranchName,
	t.destinationFXconvertRate,
	t.dealer,
	t.dealDate,
	t.createTime,
	t2.approver,
	t2.approverTime,
	t2.accountant,
	t2.accountantSignTime,
	t2.reviewer,
	t2.reviewerSignTime,
	t2.cashier,
	t.paymentMethod,
	t.documentNo,
	t2.cashierSignTime,
	t2.localCashier,
	t2.localCashierSignTime,
	t2.eBankingGroupC,
	t2.eBankingGroupCSignTime,
	t2.eBankingGroupB,
	t2.eBankingGroupBSignTime,
	t2.eBankingGroupA,
	t2.ebankingGroupASignTime,
	t2.billGroupB,
	t2.billGroupBSignTime,
	t2.billGroupA,
	t2.billGroupASignTime,
	t.fundTransferReason,
	t.printStatus,
	t.reprintReason
	
FROM t inner join t2 
  on t.procInstId = t2.proc_inst_id_ 
 and t.source_mgt_entity_id not in (
						'229',
						'247',
						'351',
						'230',
						'506',
						'112',
						'224',
						'231',
						'233',
						'221',
						'1845',
						'294',
						'2145',
						'204' 
					) 

order by
	t.dealDate desc,
	t.FUND_TRANSFER_APPLY_ID desc

四、PostgreSQL分页，查出结果后，用with当成临时表，再进行limit 15 offset 0。直接搞得话会很卡。

Tzq@2018
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
PostgreSQL自适应查询优化-C/C++开发
05-26
自适应查询优化自适应查询优化是标准PostgreSQL基于成本的查询优化器的扩展。 它的基本原理是使用查询执行统计信息来改善基数估计。 实验自适应查询优化自适应查询优化是标准PostgreSQL基于成本的查询优化器的扩展。 它的基本原理是使用查询执行统计信息来改善基数估计。 实验评估表明，这种改进有时可以为相当复杂的查询提供极大的加速。 安装该模块适用于PostgreSQL 9.6及更高版本。 该模块包含一个补丁和一个扩展。 必须将补丁应用于源
PostgreSQL Like模糊查询优化方案
最新发布
04-02
使用场景：要求LIKE '%xxx%'模糊查询支持索引，提高数据检索速度
PostgreSQL窗口查询优化
01-06
我们在实际应用中，常常会碰到这样的场景：分组取top值，比如取某一部门收入前10的人。 一般我们都会用到窗口函数去解决这类问题，同样在pg中也支持窗口函数。 例子： 创建测试表，生成10000个分组，1000万条记录。 bill=# create table tbl(c1 int, c2 int, c3 int); CREATE TABLE bill=# create index idx1 on tbl(c1,c2); CREATE INDEX bill=# insert into tbl select mod(trunc(random()*10000)::int, 10000), trun
PostgreSQL中In, Exists在SQL查询中到底有无区别
iihero@CSDN
03-10 1105
SQL查询当中，In和Exists子查询到底有无区别？记得很多年以前，确实是有相关的使用戒条的，或者说存在一些使用的惯用法。试图完全抹开两者的区别，就有点过了。两者的主要区别：从目的上讲，IN和EXISTS都是SQL中用于子查询的操作符。IN操作符是用来检查一个值是否在一组值中。例如EXISTS操作符是用来检查一个子查询是否至少返回一个记录。1. 语义上的区别：IN关键字用来查询在某个列表中的数据，EXISTS关键字用来查询是否存在子查询返回的数据。
深入了解PostgreSQL：高级查询和性能优化技巧
PGCCC的博客
08-02 1992
本文将深入探讨POSTGRESQL的高级查询和性能优化技巧，帮助您最大程度地发挥这个强大数据库的潜力。
PostgreSQL对or exists产生的filter优化二
落落的专栏 专注SQL调优 性能调优
07-20 814
PostgreSQL会对or exists产生的filter进行优化，上一篇文章没有测试exists中有大表的情况，今天来测试一下exists中有大表的情况 注意：测试期间没有对表添加索引 create table a as select * from dba_objects; create table b as select * from a; create table c as select * from a; create table d as select * from a; insert i
PostgreSQL对or exists产生的filter优化一
落落的专栏 专注SQL调优 性能调优
07-19 1211
在Oracle数据库中，where条件中有or exists子查询会走Filter，这种情况一般都需要改写SQL
PostgreSQL谓词之EXISTS
HighGO
06-10 1万+
作者：瀚高PG实验室 （Highgo PG Lab）- 田其壮 何为SQL谓词？ 我们在SQL中接触的 BETWEEN 、 LIKE 、 IN 、 IS NULL、 = 、 < 、 >等关键词都可以称之为SQL中的谓词，它是一种返回值只为真值（ true 、 false 或者 unknown）的特殊函数。它的目的是为了给SQL命题提供判断真假的手段，谓词逻辑的出现对SQL而言具有划时代的意义。 Codd在1969年曾提到过：“数据库并非是一种数据的集合，它是事实的集合。” 而这里的事实我们
sql关联查询
热门推荐
sy_shu的专栏
09-21 2万+
inner join(等值连接)只返回两个表中联结字段相等的行 left join(左联接)返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接)返回包括右表中的所有记录和左表中联结字段相等的记录 INNER JOIN语法： INNER JOIN连接两个数据表的用法： SELECT * FROM 表1 INNER JOIN 表2
PostgreSQL数据库中exists函数的使用
Monkey
08-02 1057
【代码】PostgreSQL数据库中exists函数的使用。
PostgreSQL中in、exists、= any性能对比
Focus on PostgreSQL
03-20 1万+
在SQL中我们可以使用in、exists语句来判断对象是否存在某个子查询中，在pg中除了支持这两种语法，还支持= any的语法，例如下面三个SQL的意思就是一样的： select * from tbl where id in (select id from t); select * from tbl where exists (select 1 from t where t.id=tbl...
postgresql之in和exists
weixin_44847119的博客
08-28 503
一，exists 子查询 如果子查询包含任何行，则EXISTS运算符返回true。 否则它返回false。 子查询看是否查询到结果，查询到，则返回true，否则返回false not exists 子查询查询到有结果则返回false，没有查询到结果返回true 把返回是true的行的外查询的结果都返回 注意：exists子查询查找到有结果则返回true，即使子查询查到的内容为null，也返回true exists（返回结果集，为真） not exists (不返回结果集，为真） 二，举例 表A ID N
PostgreSQL查询优化.pdf
11-07
PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS）支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。现在用户越来越多...
postgresql-monitoring：监视postgresql的查询
02-05
postgresql-monitoring：监视postgresql的查询
postgresql中关联多表递归查询，并分组计数、求和
魑魅魍魉
08-23 3586
postgresql中关联多表递归查询，并分组计数、求和
PostgreSQL——查询优化——预处理
weixin_47373497的博客
11-20 2417
2021SC@SDUSC 目录概述查询优化pg_plan_queries函数pg_plan_queries函数调用的—— **pg_plan_query函数**pg_plan_queries函数返回的—— **PlannedStmt结构体**pg_plan_query函数调用的——**planner函数**standard_planner函数调用的——**subquery_planner函数**subquery_planner函数返回的——**Plan结构体** 概述 我负责的PostgreSQL代码部分：
pg SQL查询优化经历
编程技术分享，Java后端、Vue前端
12-07 1710
1.大表不做数据转换 假设d表是大表，a表是小表 and a.sender=cast(d.staff_id as varchar) 改成： and cast(a.sender as numeric)=d.staff_id 2. distinct导致没用上索引 修改：删除distinct 3.视图查询慢 视图里面查询了太多东西改成查视图里面的源表，取我们需要的字段 比如视图里面有很多子查询，但我们不需要那么多字段。 4. exists只在理论中背过？ 查询语句 关
postgresql对EXISTS类型查询优化
landcruiser5700的专栏
12-22 3336
postgresql查询优化器逻辑优化对于EXISTS处理
postgresql-jdbc查询返回大写字段
07-08
要在 PostgreSQL-JDBC 查询中返回大写字段，可以使用以下两种方法之一： 1. 使用 AS 关键字为查询结果中的每个字段指定别名，并将别名设置为大写。例如： ```java String query = "SELECT column1 AS COLUMN1, column2 AS COLUMN2 FROM table"; ResultSet resultSet = statement.executeQuery(query); ``` 在上述示例中，查询结果的字段别名被设置为大写，以便返回大写字段。 2. 在连接到数据库时，设置 `reWriteBatchedInserts` 参数为 `true`。这将强制 PostgreSQL-JDBC 驱动程序将所有 SQL 语句中的标识符转换为大写。例如： ```java Properties props = new Properties(); props.setProperty("user", "your_username"); props.setProperty("password", "your_password"); props.setProperty("reWriteBatchedInserts", "true"); String url = "jdbc:postgresql://localhost:5432/your_database"; Connection conn = DriverManager.getConnection(url, props); ``` 通过上述设置，所有的查询语句中的字段将自动转换为大写。 无论使用哪种方法，查询结果中的字段将以大写形式返回。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

Tzq@2018

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

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

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

打赏作者

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

抵扣说明：

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

余额充值