mysql报表展示需求转化:实现行转列,同时确定每条记录

原始需求:将一张二维表,展示在 css中,无奈前端展示部分数据处理功能太弱,只能重新考虑需求以及实现方式.

如下是最初的实现方式,但是和其他人员沟通之后说是不支持,(⊙o⊙)…,沟通也是生产力啊,下面的白费了.

select 
a.accident_month

,case when b.monthId=0  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim0 
,case when b.monthId=1  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim1 
,case when b.monthId=2  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim2 
,case when b.monthId=3  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim3 
,case when b.monthId=4  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim4 
,case when b.monthId=5  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim5 
,case when b.monthId=6  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim6 
,case when b.monthId=7  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim7 
,case when b.monthId=8  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim8 
,case when b.monthId=9  and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim9 
,case when b.monthId=10 and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim10
,case when b.monthId=11 and a.progress_month=b.accident_month then ( case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end) end as monthClaim11
 
from result_claim_rate_progress a 
join ( 
	select * from (select 0  as monthId, accident_month from  claim_rate_progress order by id asc limit 0,1 ) a union all 
	select * from (select 1  as monthId, accident_month from  claim_rate_progress order by id asc limit 1,1 ) a union all 
	select * from (select 2  as monthId, accident_month from  claim_rate_progress order by id asc limit 2,1 ) a union all 
	select * from (select 3  as monthId, accident_month from  claim_rate_progress order by id asc limit 3,1 ) a union all 
	select * from (select 4  as monthId, accident_month from  claim_rate_progress order by id asc limit 4,1 ) a union all 
	select * from (select 5  as monthId, accident_month from  claim_rate_progress order by id asc limit 5,1 ) a union all 
	select * from (select 6  as monthId, accident_month from  claim_rate_progress order by id asc limit 6,1 ) a union all 
	select * from (select 7  as monthId, accident_month from  claim_rate_progress order by id asc limit 7,1 ) a union all 
	select * from (select 8  as monthId, accident_month from  claim_rate_progress order by id asc limit 8,1 ) a union all 
	select * from (select 9  as monthId, accident_month from  claim_rate_progress order by id asc limit 9,1 ) a union all 
	select * from (select 10 as monthId, accident_month from  claim_rate_progress order by id asc limit 10,1) a union all 
	select * from (select 11 as monthId, accident_month from  claim_rate_progress order by id asc limit 11,1) a	
	
 ) b -- on a.accident_month=b.accident_month 

group by accident_month order by accident_month limit 144 ;

转化需求:将数据库中的144条记录,转换成列,并且知道每条记录的数据到底数据哪个位置.

 

思路:依据事故时间和进展时间排序,获得new_id,然后执行根据相应的id进行判断数据位置. 

select 
-- 处理表头
 max(case when new_id= 1   then progress_month end) as month_1
,max(case when new_id= 2   then progress_month end) as month_2
,max(case when new_id= 3   then progress_month end) as month_3
,max(case when new_id= 4   then progress_month end) as month_4
,max(case when new_id= 5   then progress_month end) as month_5
,max(case when new_id= 6   then progress_month end) as month_6
,max(case when new_id= 7   then progress_month end) as month_7
,max(case when new_id= 8   then progress_month end) as month_8
,max(case when new_id= 9   then progress_month end) as month_9
,max(case when new_id= 10  then progress_month end) as month_10
,max(case when new_id= 11  then progress_month end) as month_11
,max(case when new_id= 12  then progress_month end) as month_12

-- 处理表数据
,max(case when new_id=1   then claim_rate end) as data_1
,max(case when new_id=2   then claim_rate end) as data_2
,max(case when new_id=3   then claim_rate end) as data_3
,max(case when new_id=4   then claim_rate end) as data_4
,max(case when new_id=5   then claim_rate end) as data_5
,max(case when new_id=6   then claim_rate end) as data_6
,max(case when new_id=7   then claim_rate end) as data_7
,max(case when new_id=8   then claim_rate end) as data_8
,max(case when new_id=9   then claim_rate end) as data_9
,max(case when new_id=10  then claim_rate end) as data_10
,max(case when new_id=11  then claim_rate end) as data_11
,max(case when new_id=12  then claim_rate end) as data_12
,max(case when new_id=13  then claim_rate end) as data_13
,max(case when new_id=14  then claim_rate end) as data_14
,max(case when new_id=15  then claim_rate end) as data_15
,max(case when new_id=16  then claim_rate end) as data_16
,max(case when new_id=17  then claim_rate end) as data_17
,max(case when new_id=18  then claim_rate end) as data_18
,max(case when new_id=19  then claim_rate end) as data_19
,max(case when new_id=20  then claim_rate end) as data_20
,max(case when new_id=21  then claim_rate end) as data_21
,max(case when new_id=22  then claim_rate end) as data_22
,max(case when new_id=23  then claim_rate end) as data_23
,max(case when new_id=24  then claim_rate end) as data_24
,max(case when new_id=25  then claim_rate end) as data_25
,max(case when new_id=26  then claim_rate end) as data_26
,max(case when new_id=27  then claim_rate end) as data_27
,max(case when new_id=28  then claim_rate end) as data_28
,max(case when new_id=29  then claim_rate end) as data_29
,max(case when new_id=30  then claim_rate end) as data_30
,max(case when new_id=31  then claim_rate end) as data_31
,max(case when new_id=32  then claim_rate end) as data_32
,max(case when new_id=33  then claim_rate end) as data_33
,max(case when new_id=34  then claim_rate end) as data_34
,max(case when new_id=35  then claim_rate end) as data_35
,max(case when new_id=36  then claim_rate end) as data_36
,max(case when new_id=37  then claim_rate end) as data_37
,max(case when new_id=38  then claim_rate end) as data_38
,max(case when new_id=39  then claim_rate end) as data_39
,max(case when new_id=40  then claim_rate end) as data_40
,max(case when new_id=41  then claim_rate end) as data_41
,max(case when new_id=42  then claim_rate end) as data_42
,max(case when new_id=43  then claim_rate end) as data_43
,max(case when new_id=44  then claim_rate end) as data_44
,max(case when new_id=45  then claim_rate end) as data_45
,max(case when new_id=46  then claim_rate end) as data_46
,max(case when new_id=47  then claim_rate end) as data_47
,max(case when new_id=48  then claim_rate end) as data_48
,max(case when new_id=49  then claim_rate end) as data_49
,max(case when new_id=50  then claim_rate end) as data_50
,max(case when new_id=51  then claim_rate end) as data_51
,max(case when new_id=52  then claim_rate end) as data_52
,max(case when new_id=53  then claim_rate end) as data_53
,max(case when new_id=54  then claim_rate end) as data_54
,max(case when new_id=55  then claim_rate end) as data_55
,max(case when new_id=56  then claim_rate end) as data_56
,max(case when new_id=57  then claim_rate end) as data_57
,max(case when new_id=58  then claim_rate end) as data_58
,max(case when new_id=59  then claim_rate end) as data_59
,max(case when new_id=60  then claim_rate end) as data_60
,max(case when new_id=61  then claim_rate end) as data_61
,max(case when new_id=62  then claim_rate end) as data_62
,max(case when new_id=63  then claim_rate end) as data_63
,max(case when new_id=64  then claim_rate end) as data_64
,max(case when new_id=65  then claim_rate end) as data_65
,max(case when new_id=66  then claim_rate end) as data_66
,max(case when new_id=67  then claim_rate end) as data_67
,max(case when new_id=68  then claim_rate end) as data_68
,max(case when new_id=69  then claim_rate end) as data_69
,max(case when new_id=70  then claim_rate end) as data_70
,max(case when new_id=71  then claim_rate end) as data_71
,max(case when new_id=72  then claim_rate end) as data_72
,max(case when new_id=73  then claim_rate end) as data_73
,max(case when new_id=74  then claim_rate end) as data_74
,max(case when new_id=75  then claim_rate end) as data_75
,max(case when new_id=76  then claim_rate end) as data_76
,max(case when new_id=77  then claim_rate end) as data_77
,max(case when new_id=78  then claim_rate end) as data_78
,max(case when new_id=79  then claim_rate end) as data_79
,max(case when new_id=80  then claim_rate end) as data_80
,max(case when new_id=81  then claim_rate end) as data_81
,max(case when new_id=82  then claim_rate end) as data_82
,max(case when new_id=83  then claim_rate end) as data_83
,max(case when new_id=84  then claim_rate end) as data_84
,max(case when new_id=85  then claim_rate end) as data_85
,max(case when new_id=86  then claim_rate end) as data_86
,max(case when new_id=87  then claim_rate end) as data_87
,max(case when new_id=88  then claim_rate end) as data_88
,max(case when new_id=89  then claim_rate end) as data_89
,max(case when new_id=90  then claim_rate end) as data_90
,max(case when new_id=91  then claim_rate end) as data_91
,max(case when new_id=92  then claim_rate end) as data_92
,max(case when new_id=93  then claim_rate end) as data_93
,max(case when new_id=94  then claim_rate end) as data_94
,max(case when new_id=95  then claim_rate end) as data_95
,max(case when new_id=96  then claim_rate end) as data_96
,max(case when new_id=97  then claim_rate end) as data_97
,max(case when new_id=98  then claim_rate end) as data_98
,max(case when new_id=99  then claim_rate end) as data_99
,max(case when new_id=100 then claim_rate end) as data_100
,max(case when new_id=101 then claim_rate end) as data_101
,max(case when new_id=102 then claim_rate end) as data_102
,max(case when new_id=103 then claim_rate end) as data_103
,max(case when new_id=104 then claim_rate end) as data_104
,max(case when new_id=105 then claim_rate end) as data_105
,max(case when new_id=106 then claim_rate end) as data_106
,max(case when new_id=107 then claim_rate end) as data_107
,max(case when new_id=108 then claim_rate end) as data_108
,max(case when new_id=109 then claim_rate end) as data_109
,max(case when new_id=110 then claim_rate end) as data_110
,max(case when new_id=111 then claim_rate end) as data_111
,max(case when new_id=112 then claim_rate end) as data_112
,max(case when new_id=113 then claim_rate end) as data_113
,max(case when new_id=114 then claim_rate end) as data_114
,max(case when new_id=115 then claim_rate end) as data_115
,max(case when new_id=116 then claim_rate end) as data_116
,max(case when new_id=117 then claim_rate end) as data_117
,max(case when new_id=118 then claim_rate end) as data_118
,max(case when new_id=119 then claim_rate end) as data_119
,max(case when new_id=120 then claim_rate end) as data_120
,max(case when new_id=121 then claim_rate end) as data_121
,max(case when new_id=122 then claim_rate end) as data_122
,max(case when new_id=123 then claim_rate end) as data_123
,max(case when new_id=124 then claim_rate end) as data_124
,max(case when new_id=125 then claim_rate end) as data_125
,max(case when new_id=126 then claim_rate end) as data_126
,max(case when new_id=127 then claim_rate end) as data_127
,max(case when new_id=128 then claim_rate end) as data_128
,max(case when new_id=129 then claim_rate end) as data_129
,max(case when new_id=130 then claim_rate end) as data_130
,max(case when new_id=131 then claim_rate end) as data_131
,max(case when new_id=132 then claim_rate end) as data_132
,max(case when new_id=133 then claim_rate end) as data_133
,max(case when new_id=134 then claim_rate end) as data_134
,max(case when new_id=135 then claim_rate end) as data_135
,max(case when new_id=136 then claim_rate end) as data_136
,max(case when new_id=137 then claim_rate end) as data_137
,max(case when new_id=138 then claim_rate end) as data_138
,max(case when new_id=139 then claim_rate end) as data_139
,max(case when new_id=140 then claim_rate end) as data_140
,max(case when new_id=141 then claim_rate end) as data_141
,max(case when new_id=142 then claim_rate end) as data_142
,max(case when new_id=143 then claim_rate end) as data_143
,max(case when new_id=144 then claim_rate end) as data_144 
from ( 
	SELECT
		id
		,accident_month
		,progress_month
		, case when claim_rate ='0' then '-' else concat(round(claim_rate*100,2),'%') end as claim_rate 
		,(@i :=@i + 1) AS new_id
	FROM
        claim_rate_progress,
		(SELECT @i := 0) AS it
	order by accident_month,progress_month 
 ) a  
 

没有什么复杂的业务是技术搞不定的,O(∩_∩)O哈哈~,sql写多了能成神!!!