pgsql拼接资源-CSDN下载

共5个文件

sql：5个

5星 · 超过95%的资源需积分: 44 22 浏览量 2022-07-15 19:29:49 上传评论收藏 12KB RAR 举报

在SQL的世界里，`pgsql`通常指的是PostgreSQL，一个强大的开源关系型数据库系统。当我们谈论“pgsql拼接”时，我们实际上是在讨论如何在PostgreSQL中进行字符串连接操作。这通常涉及到将多个字符串或者数据库查询结果合并成一个单一的字符串。在数据库查询中，拼接操作非常常见，特别是在构建复杂的SQL语句或者格式化输出数据时。在PostgreSQL中，有几种方法可以实现字符串拼接： 1. **使用`||`操作符**：这是PostgreSQL中最直观的字符串拼接方式。例如，如果你有两个字符串变量`a`和`b`，你可以通过`a || b`来将它们连接在一起。 ```sql SELECT 'Hello' || ', ' || 'World'; ``` 上述SQL语句会返回"Hello, World"。 2. **使用`CONCAT()`函数**：这是一个通用的字符串拼接函数，可以接受任意数量的参数。每个参数都可以是常量、变量或表达式。 ```sql SELECT CONCAT('Hello', ', ', 'World'); ``` 此例同样会返回"Hello, World"。 3. **使用`STRING_AGG()`函数**：这个函数在需要将一系列字符串连接成一个字符串时特别有用，尤其在聚合操作中。它接受两个参数：要连接的元素和分隔符。 ```sql SELECT STRING_AGG(name, ', ') FROM table; ``` 这将返回表`table`中所有`name`字段的值，用逗号和空格分隔。在提供的压缩包文件中，我们可以看到一些可能与医院信息系统相关的SQL脚本，比如“术前小结”、“主任医生查房记录”等。这些脚本可能包含了对患者数据的处理，其中就可能运用到上述的字符串拼接技术，将不同记录或者字段组合成具有完整信息的字符串，以便于报告或分析。例如，`cda-首次病程拼接.sql`可能涉及将患者的首次病程记录中的各项信息（如病情描述、检查结果等）通过拼接整合成一个完整的病程描述。`cda-日常病程拼接.sql`则可能是对患者每日病情变化的记录进行拼接，形成连续的病程日志。了解并熟练掌握PostgreSQL中的字符串拼接技巧，对于处理和展示数据库中的复杂信息至关重要，尤其是在医疗、金融等需要详尽记录和报告的领域。

资源详情

资源评论

资源推荐

收起资源包目录

sql拼接成xml.rar （5个子文件）

cda-日常病程拼接.sql 7KB

cda-首次病程.sql 3KB

cda-主任医生查房记录.sql 8KB

cda-首次病程拼接.sql 8KB

cda-术前小结.sql 16KB

select '' ||(select '<?xml version="1.0" encoding="UTF-8"?> <ClinicalDocument xmlns="urn:hl7-org:v3" xmlns:mif="urn:hl7-org:v3/mif" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:hl7-org:v3 ..\sdschemas\SDA.xsd"> <realmCode code="CN"/> <typeId root="2.16.840.1.113883.1.3" extension="POCD_MT000040"/> <templateId root="2.16.156.10011.2.1.1.66"/>  <id root="2.16.156.10011.1.1" extension="1559727806"/> <code code="C0046" codeSystem="2.16.156.10011.2.4" codeSystemName="卫生信息共享文档编码体系"/>  <title>术前小结</title>  <effectiveTime value="'||replace( replace(replace( created_date::varchar(60),'-',''),' ',''),':','')::varchar(14)||'"/> <confidentialityCode code="N" codeSystem="2.16.840.1.113883.5.25" codeSystemName="Confidentiality" displayName="正常访问保密级别"/> <languageCode code="zh-CN"/> <setId/> <versionNumber/>' from emr_apply_file where treat_id='716199974265839616' and ID like 'cc970d21d8a249c3ae2e560b4d0c0b25' and COALESCE(del_flag, '') != '1' ) || (select '<recordTarget typeCode="RCT" contextControlCode="OP"> <patientRole classCode="PAT">  <id root="2.16.156.10011.1.12" extension="'||vae94||'"/> <patient classCode="PSN" determinerCode="INSTANCE">  <id root="2.16.156.10011.1.3" extension="'||VAA15||'"/> <name>'||vaa05||'</name> <administrativeGenderCode code="'||vaa1.abw01::text||'" codeSystem="2.16.156.10011.2.3.3.4" codeSystemName="生理性别代码表(GB/T 2261.1)" displayName="'||abw02||'性" />  <age unit="'||aau1.aau02||'" value="'||vaa10||'"></age> </patient> </patientRole> </recordTarget>' from vae1 join vaa1 on vae1.vaa01=vaa1.vaa01 join abw1 on vaa1.abw01=abw1.abw01 join aau1 on vaa1.aau01=aau1.aau01 where vae01='716199974265839616')|| (select ' <author typeCode="AUT" contextControlCode="OP">  <time value="'||replace( replace(replace( write_time::varchar(60),'-',''),' ',''),':','')::varchar(14)||'"/> <assignedAuthor classCode="ASSIGNED"> <id root="2.16.156.10011.1.7" extension="'||write_author_id||'"/> <assignedPerson> <name>'||author||'</name> </assignedPerson> </assignedAuthor> </author>' from emr_apply_file where treat_id='716199974265839616' and ID like 'cc970d21d8a249c3ae2e560b4d0c0b25' and COALESCE(del_flag, '') != '1' )|| (select ' <custodian typeCode="CST"> <assignedCustodian classCode="ASSIGNED"> <representedCustodianOrganization classCode="ORG" determinerCode="INSTANCE"> <id root="2.16.156.10011.1.5" extension="41734607-4"/> <name>安阳市肿瘤医院</name> </representedCustodianOrganization> </assignedCustodian> </custodian> ')|| (select ' <authenticator>  <time/> <signatureCode/> <assignedEntity> <id root="2.16.156.10011.1.4" extension="'||ven1.bce01::text||'"/> <code displayName="手术者"/> <assignedPerson> <name>'||ven1.bce03||'</name> </assignedPerson> </assignedEntity> </authenticator> ' from ven1 join bce1 on bce1.bce01=ven1.bce01 where vaq01 in (select id from emr_apply_file where treat_id='716199974265839616' and ID like 'cc970d21d8a249c3ae2e560b4d0c0b25' and COALESCE(del_flag, '') != '1') order by aah01 limit 1)|| (select ' <authenticator>  <time/> <signatureCode/> <assignedEntity> <id root="2.16.156.10011.1.4" extension="'||ven1.bce01::text||'"/> <code displayName="医师"/> <assignedPerson> <name>'||ven1.bce03||'</name> </assignedPerson> </assignedEntity> </authenticator> ' from ven1 join bce1 on bce1.bce01=ven1.bce01 join aah1 on bce1.aah01=aah1.aah01 where vaq01 in (select id from emr_apply_file where treat_id='716199974265839616' and ID like 'cc970d21d8a249c3ae2e560b4d0c0b25' and COALESCE(del_flag, '') != '1') order by bce1.aah01 desc limit 1 )|| (select '<participant typeCode="NOT">  <associatedEntity classCode="ECON">  <telecom value="'||VAE71||'"/>  <associatedPerson>  <name>'||VAE68||'</name> </associatedPerson> </associatedEntity> </participant> <relatedDocument typeCode="RPLC"> <parentDocument> <id/> <setId/> <versionNumber/> </parentDocument> </relatedDocument>' from vae1 join vaa1 on vae1.vaa01=vaa1.vaa01 join abw1 on vaa1.abw01=abw1.abw01 join aau1 on vaa1.aau01=aau1.aau01 where vae01='716199974265839616')|| (select ' <componentOf> <encompassingEncounter>  <effectiveTime xsi:type="IVL_TS" value="'|| replace( replace(replace( created_date::varchar(60),'-',''),' ',''),':','')::varchar(14)||'"/> <location> <healthCareFacility> <serviceProviderOrganization> <asOrganizationPartOf classCode="PART">  <wholeOrganization classCode="ORG" determinerCode="INSTANCE"> <id root="2.16.156.10011.1.22" extension="'||bcq1.bcq01::text||'"/> <name>'||to_char( BCQ04B::int,'000')||'</name>  <asOrganizationPartOf classCode="PART"> <wholeOrganization classCode="ORG" determinerCode="INSTANCE"> <id root="2.16.156.10011.1.21" extension="'||bck01c||'"/> <name>'||bck1.bck03||'</name>  <asOrganizationPartOf classCode="PART"> <wholeOrganization classCode="ORG" determinerCode="INSTANCE"> <id root="2.16.156.10011.1.26" extension="'||bck01d::text||'"/> <name>'||bck2.bck03||'</name>  <asOrganizationPartOf classCode="PART"> <wholeOrganization classCode="ORG" determinerCode="INSTANCE"> <id root="2.16.156.10011.1.27" extension="'||bck01c::text||'"/> <name>'||bck1.bck03||'</name>  <asOrganizationPartOf classCode="PART"> <wholeOrganization classCode="ORG" determinerCode="INSTANCE"> <id root="2.16.156.10011.1.5" extension="41734607-4"/> <name>安阳市肿瘤医院</name> </wholeOrganization> </asOrganizationPartOf> </wholeOrganization> </asOrganizationPartOf> </wholeOrganization> </asOrganizationPartOf> </wholeOrganization> </asOrganizationPartOf> </wholeOrganization> </asOrganizationPartOf> </serviceProviderOrganization> </healthCareFacility> </location> </encompassingEncounter> </componentOf>' from vae1 join bcq1 on bcq1.bck01a=vae1.bck01c and bcq1.bck01b=vae1.bck01d and bcq1.bcq04= vae1.BCQ04B join bck1 on vae1.bck01c= bck1.bck01 join bck1 bck2 on bck2.bck01=vae1.bck01d join emr_apply_file on treat_id='716199974265839616' and ID like 'cc970d21d8a249c3ae2e560b4d0c0b25' and COALESCE(del_flag, '') != '1' where vae01='716199974265839616' )|| (select '<component> <structuredBody classCode="DOCBODY" moodCode="EVN">  <component> <section> <code code="DE06.00.182.00" codeSystem="2.16.156.10011.2.2.1" codeSystemName="卫生信息数据元目录" displayName="病历摘要"/> <text><![CDATA['||CONTENT||']]></text> </section> </component>' from emr_app_element where element_name in ('病历摘要') and apply_file_id in (select id from emr_apply_file where treat_id='716199974265839616' and ID like 'cc970d21d8a249c3ae2e560b4d0c0b25' and COALES