日常小SQL

2019-11-08 20:48:34

字体：大中小

来源：转载

供稿：网友

1.层次化递归查询实现行转列

如果市公司下面的供电所有一个指标作弊，则市公司该指标得0分。

select t1.c_org_no,/n t1.c_org_name,/n t1.ym,/n t1.zb_id,/n t1.zbz,/n CASE/n WHEN ',' || t2.zb_id like '%' || t1.zb_id || '%' THEN/n 0/n ELSE/n t1.df/n END df,/n t1.tj_type/n from (select t.* from tablename t where t.ym = ' ym ') t1,/n (select x_org_no,/n --ltrim(max(sys_connect_by_path(zb_id, ',')), ',') as zb_id/n max(sys_connect_by_path(zb_id, ',')) as zb_id/n from (select x_org_no,/n zb_id,/n rn1,/n lead(rn1) over(partition by x_org_no order by rn1) rn2 from (/n select t.*, row_number() over(order by x_org_no) rn1 from (select distinct t.x_org_no, t.zb_id/n from zb_pj_s t/n where t.ym = ' ym '/n and t.isfake = '1'/n order by x_org_no) t) t2) tmp/n start with rn2 is null/n connect by rn2 = PRior rn1/n group by x_org_no) t2/n where t1.x_org_no = t2.x_org_no(+) 市公司下面是县公司，县公司下面是供电所，isfake='1'说明该供电所这项指标作弊

语句分析：

select distinct t.x_org_no, t.zb_id/n from zb_pj_s t/n where t.ym = ' ym '/n and t.isfake = '1'/n order by x_org_no先给这个月作弊供电所的按县公司和指标分组，按县公司排序

数据应该是这样：

县公司编号指标编号

36451 1

36451 3

36451 5

36452 1

36452 2

36455 2

36455 7

然后

row_number() over(order by x_org_no) rn1给每个县公司的的记录赋予行号

县公司编号指标编号 rn1

36451 1 1

36451 3 2

36451 5 3

36452 1 1

36452 2 2

36455 2 1

36455 7 2

再给出分组里行号的下一个行号

lead(rn1) over(partition by x_org_no order by rn1) rn2

县公司编号指标编号 rn1 rn2

36451 1 1 2

36451 3 2 3

36451 5 3 null

36452 1 1 2

36452 2 2 null

36455 2 1 2

36455 7 2 null

在从rn2为null开始分层次递归查询把查询的路径中某个字段用逗号连接起来

sys_connect_by_path(zb_id, ',') as zb_id/n  start with rn2 is null/n  connect by rn2 = prior rn1/n  group by x_org_no) t2/n  
x_org_no          zb_id        
36451                5,3,1                                   
36452                2,1                                 
36455                7,2                
实现了行转列。
然后判断某指标如果被包含其中，则得0分 CASE/n     WHEN ',' || t2.zb_id like '%' || t1.zb_id || '%' THEN/n     0/n     ELSE/n    t1.df/n   END df,/n 2.MySQL实现rank() over()功能
候选人排行得分情况统计：SELECT	obj_new.rank,	obj_new.candidate,        obj_new.count,	obj_new.scoreFROM	(		SELECT			obj.candidate,                        obj.count,			obj.score,			@rownum := @rownum + 1 AS rownum,			@incrnum := CASE		WHEN @rowtotal = obj.score THEN			@incrnum		WHEN @rowtotal := obj.score THEN			@rownum		END AS rank		FROM		(			SELECT			candidate,                        count(*) count,			SUM(t.score) score			FROM				t_vote_temp t			WHERE				t.type = '3'			GROUP BY				t.candidate			ORDER BY				score DESC	    ) AS obj,	    (		    SELECT			@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0	    ) r    ) AS obj_new
3.根据字段的不同数据值进行统计
	SELECT		max(t.voter_name) voter_name,--投票人		max(t.voter_orgname) voter_orgname,--单位		SUM(			CASE			WHEN t.type = '1' THEN				1			ELSE				0			END		) aa,                         --奖项类型是1投了多少人		SUM(			CASE			WHEN t.type = '2' THEN				1			ELSE				0			END		) bb,                        --奖项类型是2投了多少人		SUM(			CASE			WHEN t.type = '3' THEN				1			ELSE				0			END		) cc                      --奖项类型是2投了多少人	FROM		t_vote_temp t	GROUP BY		t.voter