<think>我们面对一个复杂的多表联合查询需求,需要分步骤构建SQL语句。根据描述,我们需要连接三个表,并根据不同条件进行统计和计算。由于需求包含多个条件分支,我们可以考虑使用多个子查询或条件聚合(CASEWHEN)来实现。步骤:1.表连接:先左连接OA-PATROL-INFO(记为A)和OA-PATROL-DEPT-INFO(记为B)条件为A.ID=B.PATROLINFOID;然后将结果再左连接OA-PATROL-RECTIFICATION-INFO(记为C)条件为B.BEINSPECTEDID=C.BEINSPECTEDID。2.对于LINKNAME='4'的情况:-统计BEINSPECTEDID、EXT3、EXT4的数量(注意:这里可能是去重统计?需求没有明确,但根据后续操作,可能是指去重计数)-统计EXT14为'已完成'和'未完成'的数量(这里应该是按行统计?还是按BEINSPECTEDID去重?需求没有明确,但根据上下文,可能是按每个BEINSPECTEDID的状态行数)-对EXT7、EXT8、EXT9分别求和(同样,按行求和?)3.对于LINKNAME='6'的情况,需要做更复杂的处理:-先筛选出LINKNAME='6'的记录,然后对于同一个BEINSPECTEDID和PATROLINFOID,只取EXT17最大的一组记录(即每组保留一条,使用窗口函数或子查询)。-然后,对这样的记录进行统计:a.EXT14全部为'已完成'的BEINSPECTEDID数量(注意:这里按BEINSPECTEDID去重计数,并且要求该BEINSPECTEDID对应的所有记录中EXT14都是'已完成')b.EXT14存在'未完成'的BEINSPECTEDID数量(同样按BEINSPECTEDID去重计数,并且只要有一条是'未完成'就算)c.EXT3的数量(可能是去重数量,也可能是所有记录数?这里根据需求,应该是统计该组记录中EXT3的数量?但描述不够清晰,我们暂时理解为每个BEINSPECTEDID对应的一条记录(取EXT17最大的那一条)的EXT3,然后进行计数。但后面又说计算占总数的比例,这个总数是指什么?需求描述中说是占总数的比例,总数可能是所有BEINSPECTEDID的数量?)-然后计算比例:注意,这里要求分别计算存在'未完成'的BEINSPECTEDID数量占总BEINSPECTEDID数量(即满足LINKNAME='6'并且按条件分组后的BEINSPECTEDID总数)的比例,以及EXT3的数量占总数的比例?这里需求描述有些模糊,我们将总数理解为满足LINKNAME='6'并且按条件分组后的BEINSPECTEDID总数(即上面a和b的总和,因为每个BEINSPECTEDID要么全部完成,要么存在未完成,所以总数就是a+b)。但是注意,EXT3的计数可能不是BEINSPECTEDID的数量,而是记录数(因为我们每组取一条,所以一条记录对应一个EXT3,那么EXT3的数量其实就是我们处理的记录数,也就是BEINSPECTEDID的数量(因为按BEINSPECTEDID分组取一条))。因此,比例就是:存在未完成的BEINSPECTEDID数量/总数(a+b),以及EXT3的数量(也就是记录数,这里等于a+b)占总数的比例(那就是100%?)。这显然不对。重新理解需求:当LINKNAME='6'时,我们得到一组记录(每个BEINSPECTEDID和PATROLINFOID取EXT17最大的一条),然后:-对BEINSPECTEDID进行统计:全部完成的个数(记为completed_count),存在未完成的个数(记为uncompleted_count)-同时,还要统计这些记录中的EXT3的数量?这里可能是指这些记录中EXT3列非空的记录数?或者就是记录数(因为每组一条,记录数等于BEINSPECTEDID的数量)?然后计算:uncompleted_count/(completed_count+uncompleted_count)作为第一个比例(未完成占比)。以及EXT3的数量?这里EXT3的数量是指什么?需求说“EXT3的数量”,然后又要求计算其占总数的比例。总数是什么?如果是所有BEINSPECTEDID的数量,那么EXT3的数量应该是记录数(也就是completed_count+uncompleted_count),那么比例就是1?这显然不对。另一种理解:在LINKNAME='6'的情况下,我们取EXT17最大的一条记录,然后统计这条记录中的EXT3字段的值(可能是数值型)?但需求说的是“数量”,因此可能是计数?或者是指该条记录中EXT3有值(非空)?我们无法确定。由于需求描述存在歧义,我们只能基于假设来构建。为了推进工作,我们做如下假设:对于LINKNAME='4'的部分:-统计BEINSPECTEDID的数量:这里应该是去重计数,即COUNT(DISTINCTBEINSPECTEDID)-EXT3、EXT4的数量:可能是去重计数?也可能是有多少条记录就有多少个?需求未明确。但考虑到与BEINSPECTEDID并列,可能是去重计数?但是后续对EXT3又计算比例,所以我们需要明确。由于需求不明确,我们将按照去重计数处理。-统计EXT14为'已完成'的数量:即当EXT14='已完成'时,计数(按记录计数,不按去重)?因为同一个BEINSPECTEDID可能有多个记录,所以这里可能是COUNT(CASEWHENEXT14='已完成'THEN1END)。同样,未完成的也是类似。-求和:EXT7,EXT8,EXT9是数值型字段,直接SUM(EXT7)等。对于LINKNAME='6'的部分:-先过滤LINKNAME='6',然后按BEINSPECTEDID,PATROLINFOID分组,取EXT17最大的一条(可以使用ROW_NUMBER()窗口函数)。-然后,我们得到这样的一批记录。接着,对这批记录进行统计:全部为'已完成'的BEINSPECTEDID数量:注意,这里不是按记录计数,而是按BEINSPECTEDID去重,并且要求该BEINSPECTEDID在之前取出的唯一一条记录上的EXT14为'已完成'?但是注意,我们取EXT17最大的一条记录,那么每个BEINSPECTEDID只有一条记录。所以这里可以直接根据该条记录的EXT14来判断。存在'未完成':即该条记录的EXT14为'未完成'?但是需求说“存在'未完成'的BEINSPECTEDID”,但这里每个BEINSPECTEDID只有一条记录,所以该条记录是'未完成'就算。因此,统计的是每个BEINSPECTEDID对应的那条记录的EXT14是否是'未完成'。那么,统计全部完成的数量:COUNT(CASEWHENEXT14='已完成'THENBEINSPECTEDIDEND)但是要注意去重?因为每个BEINSPECTEDID只出现一次,所以直接计数即可。存在未完成的数量:COUNT(CASEWHENEXT14='未完成'THENBEINSPECTEDIDEND)EXT3的数量:这里我们理解是统计这些记录(每组一条的记录)中,EXT3字段非空的数量?即COUNT(EXT3)(注意,如果是非空计数,那么使用COUNT(EXT3))然后计算两个比例:1.存在未完成的BEINSPECTEDID数量占总BEINSPECTEDID数量(即所有取出来的记录对应的BEINSPECTEDID的数量)的比例:uncompleted_count/total_count2.EXT3的数量同样占总BEINSPECTEDID数量(即total_count)的比例?但是EXT3的数量可能不是BEINSPECTEDID的数量,而是这些记录中EXT3非空的数量。所以比例就是:非空EXT3的记录数/总记录数(即COUNT(EXT3)/COUNT(*))但是需求原文:“分别计算EXT14存在'未完成'的BEINSPECTEDID和EXT3占总数的比例”,这里两个比例:比例1:存在未完成的BEINSPECTEDID数量/总数(即总BEINSPECTEDID数)比例2:EXT3的数量/总数(这个总数也是指总BEINSPECTEDID数?还是其他?需求没有明确说明。根据上文,我们假设总数都是指总BEINSPECTEDID数量(也就是总记录数,因为每个BEINSPECTEDID对应一条记录))。因此,比例1就是:存在未完成的数量/总记录数。比例2:EXT3(非空)的数量/总记录数。注意:在LINKNAME='6'的情况下,统计的BEINSPECTEDID都是去重的(每个BEINSPECTEDID只出现一次),所以总记录数就是总BEINSPECTEDID数。由于需求复杂,我们将整个查询拆分为两个主要部分,然后可能分别查询,也可以合并为一个查询。考虑到两个部分都是基于相同的连接表,我们可以分别处理然后通过UNION或者分别展示。但是需求并没有说明两个部分是分开统计还是合并在一起。所以我们可以分别用两个查询来写,但也可以在一个查询中同时计算。但是注意,这两个部分是基于两个不同的条件:LINKNAME='4'和LINKNAME='6'。所以我们可以分别针对这两个条件进行统计。然而,在同一个查询中同时处理LINKNAME='4'和LINKNAME='6'可能会因为数据量大而影响性能,而且逻辑复杂。我们可以分开写两个查询,但根据需求描述,似乎需要一条SQL语句?因此我们尝试在一个查询中完成。步骤:1.先完成表连接。2.然后分别处理两个条件。使用条件聚合,但是注意两个条件的过滤条件不同,而且对于LINKNAME='6'的部分还需要先分组取最大值,因此我们需要分别构造子查询。由于LINKNAME='6'的部分需要先按照BEINSPECTEDID,PATROLINFOID分组取EXT17最大的一条记录,我们可以先对这部分数据单独处理,然后再与主查询进行连接或者作为子查询。具体方案:方案1:分别计算两个条件的统计结果,然后使用两个子查询分别统计,最后放在一行中(或者两行)。这里我们使用两个子查询,然后交叉连接(因为没有关联键)形成一行(如果结果需要一行展示)或者分别输出两行(如果是两个统计部分)。方案2:在同一个查询中使用条件聚合,但LINKNAME='6'的部分需要先进行额外处理(分组取最大),因此我们可以先构建一个公共表表达式(CTE),将连接后的数据分成两个部分:一部分是LINKNAME='4'的数据,另一部分是经过处理(分组取最大EXT17)的LINKNAME='6'的数据,然后分别聚合。但注意,这两个部分的数据是相互独立的,所以我们可以分别处理然后UNIONALL,但是聚合结果的形式不同(需要统计的字段不同),所以可能不太适合UNION。因此,我们采用方案1:在一个查询中分别计算两个部分,然后通过CROSSJOIN(或者没有连接)放在一行中(用多个列同时展示)。但是这样会使得查询结果只有一行,且列非常多。但是,需求中并没有说明输出格式,我们按照两个部分分开输出两行?也没有具体说明。为了避免混淆,我们输出两行:第一行是LINKNAME='4'的统计结果,第二行是LINKNAME='6'的统计结果。这样我们就可以用不同的列表示不同的统计项。具体步骤:1.构建一个连接表的CTE(CommonTableExpression),记为base。2.对于LINKNAME='4'的部分,直接从base中筛选LINKNAME='4',然后进行聚合。3.对于LINKNAME='6'的部分,我们需要先对base表中LINKNAME='6'的数据进行分组(BEINSPECTEDID,PATROLINFOID)取EXT17最大的一条(使用ROW_NUMBER),然后在这个结果集上再进行聚合。然后,我们将两个部分的结果进行UNIONALL,但两个部分的输出列不同,所以我们需要对齐列,不足的列置为NULL。但这样会增加复杂度。或者我们分别计算两个部分,然后使用两个子查询,将结果放在同一行中(作为不同列)。这样输出只有一行,两个部分的结果作为不同的列。但是这样要求两个部分都只返回一行(即统计值都是标量)。由于需求没有明确,我们选择分两行输出,每行代表一个部分,然后每行有各自的统计列。这样我们可以使用多个列,对于不相关的列置NULL。但是这样需要保证两个部分的列数一致。但两个部分的统计项不同,列数可能不一致,所以我们把两个部分各自的统计项都列出来,然后将另一部分置为NULL。这样输出列的设计:第一部分(LINKNAME='4')的统计结果包含以下字段:-stat_type:标记为'LINKNAME=4'-count_be_inspected_id:BEINSPECTEDID数量-count_ext3:EXT3数量-count_ext4:EXT4数量-count_ext14_completed:EXT14为'已完成'的数量-count_ext14_uncompleted:EXT14为'未完成'的数量-sum_ext7:EXT7求和-sum_ext8:EXT8求和-sum_ext9:EXT9求和对于第二部分需要的列,这里全部置NULL(我们暂时不输出,因为这一行只展示第一部分)第二部分(LINKNAME='6')的统计结果包含:-stat_type:标记为'LINKNAME=6'-completed_count:EXT14全部为'已完成'的BEINSPECTEDID数量(注意,这里因为每条记录代表一个BEINSPECTEDID,所以直接计数完成即可)-uncompleted_count:EXT14存在'未完成'的BEINSPECTEDID数量(这里我们按该条记录的EXT14为未完成计数?注意,我们只取了一条记录,所以每个BEINSPECTEDID的状态就是这条记录的状态。因此,我们可以直接按这条记录的EXT14进行判断)-count_ext3:EXT3非空的数量(COUNT(EXT3))-uncompleted_ratio:未完成的BEINSPECTEDID比例(即uncompleted_count除以总记录数)-ext3_ratio:EXT3的数量占记录总数的比例(即count_ext3除以总记录数)对于第一部分需要的列,全部置为NULL。但是这样输出列不同,我们可以分别输出两个结果集,但是用户要求一条SQL语句,所以我们可以使用UNIONALL,并且将两个结果集使用不同列,并用NULL补全对方需要的列。但必须保证两个SELECT语句输出的列数量相同且类型兼容。因此,我们设计输出列如下(共11列):stat_type,count_be_inspected_id,count_ext3,count_ext4,count_ext14_completed,count_ext14_uncompleted,sum_ext7,sum_ext8,sum_ext9,completed_count,uncompleted_count,count_ext3_for_linkname6,uncompleted_ratio,ext3_ratio这样,第一部分的查询中,前9列有值,后5列为NULL;第二部分的查询中,前8列为NULL(除了stat_type),后5列有值(但注意第二部分的count_ext3_for_linkname6不是前面的count_ext3,所以我们单独命名)。但是这样列太多,而且部分列的含义在两种情况下不同,可能造成混淆。所以我们分别输出两个统计结果,并在结果中用stat_type区分。然而,由于UNIONALL要求列数相同,所以我们只能使用尽可能多的列,把两个部分的结果都囊括进去。我们定义以下列(15列):stat_type,--字符串,标记是哪个部分count_be_inspected_id,count_ext3,count_ext4,count_ext14_completed,count_ext14_uncompleted,sum_ext7,sum_ext8,sum_ext9,completed_count,uncompleted_count,count_ext3_for_linkname6,total_count_for_linkname6,uncompleted_ratio,ext3_ratio解释:total_count_for_linkname6是第二部分的记录总数(即用于计算比例的分母),虽然可以通过completed_count+uncompleted_count得到,但为了清晰,我们也单独计算。这样,对于第一部分:除了stat_type外,只有前8列有值(count_be_inspected_id到sum_ext9),其余列(从completed_count到ext3_ratio)均为NULL。对于第二部分:stat_type有值,completed_count,uncompleted_count,count_ext3_for_linkname6,total_count_for_linkname6有值,uncompleted_ratio=uncompleted_count/total_count_for_linkname6,ext3_ratio=count_ext3_for_linkname6/total_count_for_linkname6其他列(即前8列)为NULL。现在,我们分别构建两个部分的子查询:第一部分(LINKNAME='4'):从base表(连接后的表)中选出LINKNAME='4'的数据,然后计算:count_be_inspected_id=COUNT(DISTINCTBEINSPECTEDID)count_ext3=COUNT(DISTINCTEXT3)count_ext4=COUNT(DISTINCTEXT4)count_ext14_completed=COUNT(CASEWHENEXT14='已完成'THEN1END)count_ext14_uncompleted=COUNT(CASEWHENEXT14='未完成'THEN1END)sum_ext7=SUM(EXT7)sum_ext8=SUM(EXT8)sum_ext9=SUM(EXT9)注意:这里是否使用DISTINCT要根据需求。由于需求没有明确,我们假设需要去重。但EXT14的计数不需要去重(按行统计)。第二部分(LINKNAME='6'):先处理base表中LINKNAME='6'的数据,按照(BEINSPECTEDID,PATROLINFOID)分组,每组取EXT17最大的一条记录(如果EXT17相同,则取任意一条)。我们可以使用ROW_NUMBER()窗口函数。然后基于这个结果集,进行统计:total_count_for_linkname6=COUNT(BEINSPECTEDID)--总记录数(也即总BEINSPECTEDID数)completed_count=COUNT(CASEWHENEXT14='已完成'THENBEINSPECTEDIDEND)--注意,这里每个BEINSPECTEDID只出现一次,所以COUNT(条件)即可,不需要去重uncompleted_count=COUNT(CASEWHENEXT14='未完成'THENBEINSPECTEDIDEND)count_ext3_for_linkname6=COUNT(EXT3)--非空计数uncompleted_ratio=uncompleted_count/total_count_for_linkname6--注意:使用小数表示,可能需要用浮点数ext3_ratio=count_ext3_for_linkname6/total_count_for_linkname6但是,注意:在第二部分中,我们得到的是一个临时结果集(每个BEINSPECTEDID只出现一次,所以我们直接使用聚合函数即可)。由于在SQL中除法可能整除,为了避免截断,可以显式乘以1.0转换成浮点数,或者使用CAST。下面我们开始编写SQL:注意:表名和字段名用方括号或双引号,但这里为了通用,我们使用反引号(如果有需要)。但根据用户提供的信息,表名是大写字母和横杠,在SQL中通常需要用双引号或方括号。这里我们假设数据库支持使用双引号,例如:"OA-PATROL-INFO"。步骤:1.构建基本连接表(CTE)2.对第二部分的数据单独处理(使用ROW_NUMBER)形成子查询3.分别进行两个部分的聚合注意:在连接表中,可能有多个表有同一个字段名(如BEINSPECTEDID),我们需要明确指定来自哪个表。根据连接条件,OA-PATROL-DEPT-INFO(B表)中有BEINSPECTEDID,而OA-PATROL-RECTIFICATION-INFO(C表)也有BEINSPECTEDID,我们使用的是B.BEINSPECTEDID=C.BEINSPECTEDID。所以后续使用BEINSPECTEDID时,我们可以用B表的,也可以用C表的(在左连接时,如果C表没有连接上,则C.BEINSPECTEDID为NULL,但B.BEINSPECTEDID还有值)。但在LINKNAME='4'和'6'时,都需要使用C.LINKNAME(因为条件在C表上),所以我们使用C.BEINSPECTEDID(但注意,如果连接不上,C.BEINSPECTEDID为NULL,那么LINKNAME也是NULL。因此,我们的连接条件要确保在LINKNAME='4'或'6'时,C表必须存在(所以严格来说,我们只关心C表存在且LINKNAME符合的情况)。但需求是左连接,所以C表可能不存在匹配的行。所以我们在筛选LINKNAME='4'或'6'时,也隐含着筛选了C表存在匹配的行。因此,在连接表中,我们使用C表上的字段时,如果是NULL,那么LINKNAME就不会为'4'或'6',所以不会出现在我们的统计中。下面开始编写CTE:由于需求复杂,我们使用多个CTE来提高可读性。注意:由于数据量较大,这样写可能会影响性能,但为了清晰,我们先按逻辑编写。具体SQL语句:WITHbaseAS(SELECTA.*,--实际使用时,应该只选取需要的字段,避免性能问题。但这里为了演示,我们使用*,实际开发中要明确字段B.BEINSPECTEDIDASB_BEINSPECTEDID,B.PATROLINFOIDASB_PATROLINFOID,...,C.BEINSPECTEDIDASC_BEINSPECTEDID,C.LINKNAME,C.EXT3,C.EXT4,C.EXT14,C.EXT7,C.EXT8,C.EXT9,C.EXT17FROM"OA-PATROL-INFO"ALEFTJOIN"OA-PATROL-DEPT-INFO"BONA.ID=B.PATROLINFOIDLEFTJOIN"OA-PATROL-RECTIFICATION-INFO"CONB.BEINSPECTEDID=C.BEINSPECTEDID),--第一部分:直接筛选LINKNAME='4'的数据part4AS(SELECTCOUNT(DISTINCTC_BEINSPECTEDID)AScount_be_inspected_id,COUNT(DISTINCTEXT3)AScount_ext3,COUNT(DISTINCTEXT4)AScount_ext4,COUNT(CASEWHENEXT14='已完成'THEN1END)AScount_ext14_completed,COUNT(CASEWHENEXT14='未完成'THEN1END)AScount_ext14_uncompleted,SUM(EXT7)ASsum_ext7,SUM(EXT8)ASsum_ext8,SUM(EXT9)ASsum_ext9FROMbaseWHERELINKNAME='4'),--第二部分:处理LINKNAME='6',先分组取EXT17最大的一条记录part6_prepAS(SELECT*,ROW_NUMBER()OVER(PARTITIONBYB_BEINSPECTEDID,B_PATROLINFOIDORDERBYEXT17DESC)ASrnFROMbaseWHERELINKNAME='6'),part6_dataAS(SELECT*FROMpart6_prepWHERErn=1--每个分组取EXT17最大的一条),part6AS(SELECTCOUNT(C_BEINSPECTEDID)AStotal_count,COUNT(CASEWHENEXT14='已完成'THENC_BEINSPECTEDIDEND)AScompleted_count,COUNT(CASEWHENEXT14='未完成'THENC_BEINSPECTEDIDEND)ASuncompleted_count,COUNT(EXT3)AScount_ext3_for_linkname6,--计算比例COUNT(CASEWHENEXT14='未完成'THENC_BEINSPECTEDIDEND)*1.0/COUNT(C_BEINSPECTEDID)ASuncompleted_ratio,COUNT(EXT3)*1.0/COUNT(C_BEINSPECTEDID)ASext3_ratioFROMpart6_data),--合并两个部分的结果--我们为每个部分单独构造一行resultAS(SELECT'LINKNAME=4'ASstat_type,count_be_inspected_id,count_ext3,count_ext4,count_ext14_completed,count_ext14_uncompleted,sum_ext7,sum_ext8,sum_ext9,NULLAScompleted_count,NULLASuncompleted_count,NULLAScount_ext3_for_linkname6,NULLAStotal_count,NULLASuncompleted_ratio,NULLASext3_ratioFROMpart4UNIONALLSELECT'LINKNAME=6'ASstat_type,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,completed_count,uncompleted_count,count_ext3_for_linkname6,total_count,uncompleted_ratio,ext3_ratioFROMpart6)SELECT*FROMresult;注意:如果某个部分没有数据,那么该部分返回0条记录?我们的part4和part6子查询没有数据时,会返回一行(count为0)?聚合函数在没有数据时返回NULL(或0?如果字段为数字,则为0)?在part4中,所有聚合函数在没有数据时返回0(因为COUNT返回0,SUM返回NULL,但我们可以用COALESCE(SUM(EXT7),0)来避免NULL)?所以,对于part4和part6,我们可能需要用COALESCE确保没有数据时返回0。因此,在part4中:将所有的COUNT和SUM都加上COALESCE,这样没有数据时就不会是NULL。例如:COALESCE(COUNT(DISTINCTC_BEINSPECTEDID),0)AScount_be_inspected_id,但COUNT在没有行的时候返回0,所以不需要。但是SUM需要:COALESCE(SUM(EXT7),0)另外,在part6中,total_count在没有记录时为0(COUNT(C_BEINSPECTEDID)),其他类似。但是,如果没有数据时,part4会返回一行(0值),part6也会返回一行(0值)?所以我们最后会有两行。但是,如果两个部分都没有数据,那么我们就会有两行(每行都是0值)?或者part4和part6都是0行?我们需要确保即使没有数据,part4和part6也要返回一行,因此可以使用:在part4中,如果没有数据,我们也要返回一行0值。可以使用LEFTJOIN一个虚拟行,或者通过UNION一个空行,但不需要,因为COUNT和SUM在没有行的时候会返回0或NULL(需要COALESCE)。因此,在part4中,我们只需确保即使FROM没有数据,我们也能返回0。使用聚合函数时,如果没有数据,COUNT返回0,其他聚合函数返回NULL。我们调整part4:part4AS(SELECTCOALESCE(COUNT(DISTINCTC_BEINSPECTEDID),0)AScount_be_inspected_id,COALESCE(COUNT(DISTINCTEXT3),0)AScount_ext3,COALESCE(COUNT(DISTINCTEXT4),0)AScount_ext4,COALESCE(COUNT(CASEWHENEXT14='已完成'THEN1END),0)AScount_ext14_completed,COALESCE(COUNT(CASEWHENEXT14='未完成'THEN1END),0)AScount_ext14_uncompleted,COALESCE(SUM(EXT7),0)ASsum_ext7,COALESCE(SUM(EXT8),0)ASsum_ext8,COALESCE(SUM(EXT9),0)ASsum_ext9FROMbaseWHERELINKNAME='4'),但是,COUNT函数在有数据的情况下不会返回NULL,没有数据时返回0,所以只有SUM需要这样处理?而COUNT不需要。但上面语句中,COUNT不会返回NULL,只有SUM会返回NULL,所以只需要对SUM使用COALESCE即可。调整:将COUNT相关的聚合去掉COALESCE(改为COUNT本身),只对SUM使用COALESCE。同理,在part6中,除了比率,其他字段如果为0时,比率应该是0/0(即NULL)。因此,我们可能需要用CASEWHEN来避免除数为0的情况。修改part6的比率计算:CASEWHENCOUNT(C_BEINSPECTEDID)>0THENCOUNT(CASEWHENEXT14='未完成'THENC_BEINSPECTEDIDEND)*1.0/COUNT(C_BEINSPECTEDID)ELSENULLENDASuncompleted_ratio,其他比率类似。由于需求复杂,并且我们不知道数据库类型(不同的数据库系统对数据类型和除数为0的处理可能不同),我们做了以上处理。最终,我们将以上SQL语句整理为一条完整的SQL。注意,实际应用中可能需要调整字段和表别名,确保每个字段的来源表正确。由于用户没有提供具体字段对应的表,我们在base中使用了*,这可能会引发列名冲突(如BEINSPECTEDID在B表和C表中都有),因此我们给它们起了别名(B_BEINSPECTEDID和C_BEINSPECTEDID)。但后续计算中,我们使用的是C_BEINSPECTEDID(因为条件是C.LINKNAME,所以使用C表字段)。在连接后,如果C表无匹配,则C_BEINSPECTEDID为NULL,但LINKNAME也为NULL,所以不会进入筛选。在LINKNAME='4'或'6'的情况下,C_BEINSPECTEDID一定不为空,因为C表匹配到了。以上SQL在复杂场景下可能需要进一步优化性能,但功能上满足需求。考虑到SQL长度,我们将其完整写出,并适当注释。由于在文本编辑器中,我们可能会遇到数据库系统保留字的问题,所以建议在表名和字段名上加双引号(或方括号)。以下是完整SQL语句:</think>
以下是根据需求编写的复杂SQL语句,结合了多表连接、条件聚合、窗口函数和比例计算:
```sql
WITH JoinedData AS (
SELECT
info.ID,
dept.BEINSPECTEDID,
rect.LINKNAME,
rect.EXT3,
rect.EXT4,
rect.EXT7,
rect.EXT8,
rect.EXT9,
rect.EXT14,
rect.EXT17
FROM [OA-PATROL-INFO] info
LEFT JOIN [OA-PATROL-DEPT-INFO] dept
ON info.ID = dept.PATROLINFOID
LEFT JOIN [OA-PATROL-RECTIFICATION-INFO] rect
ON dept.BEINSPECTEDID = rect.BEINSPECTEDID
), Link4Data AS (
SELECT
COUNT(DISTINCT BEINSPECTEDID) AS CountBeInspected,
COUNT(DISTINCT EXT3) AS CountExt3,
COUNT(DISTINCT EXT4) AS CountExt4,
COUNT(CASE WHEN EXT14 = '已完成' THEN 1 END) AS CompletedCount,
COUNT(CASE WHEN EXT14 = '未完成' THEN 1 END) AS UncompletedCount,
SUM(EXT7) AS SumExt7,
SUM(EXT8) AS SumExt8,
SUM(EXT9) AS SumExt9
FROM JoinedData
WHERE LINKNAME = '4'
), Link6Filtered AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY BEINSPECTEDID, ID
ORDER BY EXT17 DESC
) AS RowNum
FROM JoinedData
WHERE LINKNAME = '6'
), Link6Data AS (
SELECT
COUNT(DISTINCT BEINSPECTEDID) AS TotalBeInspected,
COUNT(DISTINCT CASE
WHEN EXISTS (
SELECT 1 FROM JoinedData j
WHERE j.BEINSPECTEDID = Link6Filtered.BEINSPECTEDID
AND j.EXT14 = '未完成'
)
THEN BEINSPECTEDID END
) AS AnyUncompletedBeInspected,
COUNT(DISTINCT CASE
WHEN NOT EXISTS (
SELECT 1 FROM JoinedData j
WHERE j.BEINSPECTEDID = Link6Filtered.BEINSPECTEDID
AND j.EXT14 <> '已完成'
)
THEN BEINSPECTEDID END
) AS AllCompletedBeInspected,
COUNT(EXT3) AS CountExt3
FROM Link6Filtered
WHERE RowNum = 1
)
SELECT
'LINKNAME=4' AS Condition,
CountBeInspected,
CountExt3,
CountExt4,
CompletedCount,
UncompletedCount,
SumExt7,
SumExt8,
SumExt9,
NULL AS AnyUncompletedBeInspected,
NULL AS AllCompletedBeInspected,
NULL AS AnyUncompletedRatio,
NULL AS Ext3Ratio
FROM Link4Data
UNION ALL
SELECT
'LINKNAME=6' AS Condition,
TotalBeInspected,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
AnyUncompletedBeInspected,
AllCompletedBeInspected,
ROUND(AnyUncompletedBeInspected * 1.0 / NULLIF(TotalBeInspected, 0), 4) AS AnyUncompletedRatio,
ROUND(CountExt3 * 1.0 / NULLIF(TotalBeInspected, 0), 4) AS Ext3Ratio
FROM Link6Data;
```
### 关键步骤说明:
1. **基础连接 (JoinedData CTE)**:
- 三个表左连接:`OA-PATROL-INFO` → `OA-PATROL-DEPT-INFO` → `OA-PATROL-RECTIFICATION-INFO`
- 使用字段:`ID`、`BEINSPECTEDID`、`LINKNAME`、以及各EXT字段
2. **LINKNAME=4处理 (Link4Data CTE)**:
```sql
COUNT(DISTINCT CASE WHEN EXT14='已完成' THEN 1 END) AS CompletedCount
```
- 统计`BEINSPECTEDID`、`EXT3`、`EXT4`的**去重计数**
- 按`EXT14`状态分类计数
- 对`EXT7/8/9`进行`SUM`求和
3. **LINKNAME=6处理**:
- **数据过滤 (Link6Filtered CTE)**:
```sql
ROW_NUMBER() OVER (PARTITION BY BEINSPECTEDID, ID ORDER BY EXT17 DESC)
```
- 分组取`EXT17`最大值的记录
- **统计计算 (Link6Data CTE)**:
```sql
COUNT(DISTINCT CASE WHEN NOT EXISTS (...) THEN BEINSPECTEDID END)
```
- 使用子查询判断状态完成情况
- 计算全部完成/存在未完成的`BEINSPECTEDID`数量
- 计算`EXT3`计数和占比:
```sql
ROUND(CountExt3 * 1.0 / NULLIF(TotalBeInspected, 0), 4)
```
4. **结果合并 (UNION ALL)**:
- 两个条件的结果统一结构输出
- 使用`NULLIF`防止除零错误
- 比例计算保留4位小数
### 注意事项:
1. 使用`DISTINCT COUNT`确保统计项不重复计数
2. 窗口函数`ROW_NUMBER()`确保每组取唯一记录
3. 子查询`EXISTS/NOT EXISTS`精确判断状态
4. `NULLIF(TotalBeInspected, 0)`避免除零错误
5. `ROUND(value * 1.0 / total, 4)`计算精确比例