GROOVY数据列变行
在开发中遇到了列变行的问题，简单记录如下：tb_case 记录了 用例ID 用例名称。tb_ret 记录了执行结果，执行时间结果状态。
要求查询一段时间内的记录获取如下数据报表：
1点 2点 3点 4点 5点 ............. 24点
用例1 1 2 1 4 3 4
用例5 1 3 1 4 3 4
用例4 1 2 1 4 3 4
用groovy代码可以很容易实现，不超过50行，样例如下：
import groovy.sql.Sqldef sql = Sql.newInstance('JDBCURL', '用户名', '密码','驱动')def String s_in = '1, 2, 3, 51, 9999'def String st_case = """SELECT c_id, c_name FROM tb_case WHERE c_id IN ($s_in)"""def String st_query = """SELECT c_id, to_char(r_start, 'yyyymmddhh24') rtime, SUM(r_stat) statFROM tb_ret WHERE c_id IN ($s_in) AND r_start BETWEEN to_date('20100710', 'yyyymmdd') AND to_date('20100720', 'yyyymmdd') GROUP BY c_id, to_char(r_start, 'yyyymmddhh24') """def cases = [:]sql.eachRow(st_case){ ret -> cases[(Integer) ret]=ret}def rets = [:]def times = [:]sql.eachRow(st_query){ def (c_id, rtime, stat) = [(Integer) it, it, it ] if (!times.containsKey(rtime)){ times = -1 } if (!rets.containsKey(c_id)){ rets = [:] } rets = stat}print "名称".padRight(16)times.sort().each{ k, v-> print "$k\t" }println ""//补充缺少的用例cases.each{ k, v -> if (!rets.containsKey(k)) rets=[:]}rets.sort().each{ k, v -> //补充缺少的时间点 def fulltime = times.plus(v) print k print cases.padRight(16) fulltime.sort().each{ k1, v1 -> print "\t$v1" } println "" }
页:
[1]