jquery结合ibatis构造复杂表格
1、先构造好表头，即一级表头。表头的标题要跨多少行或者列需先写好，写死标题简单些，查数据库比较复杂。
例如：var col="[[{field:'AREANAME',title:'地市',width:50,rowspan:2} ,";
col += "{title:'一级表头1',align:'right',colspan:"+3+" },";
col += "{title:'一级表头2',align:'right',colspan:"+4+" },";
2、结合sum case sql语句和jquery的表格显示格式构造二级表头和表体。jquery的filed：name，name对于sql语句的字段名，sql语句的case when=“第一级标题名称”。
例如：
for(var i=0;i<2;i++){//找出需要显示的列，这里比如只有表头1和表头2
var index = i+1;
col+="{field:'YUESHIJIZHI"+index+"',title:'实际值',width:60,align:'right'},";
caseSql+='sum(case when t3.timeCd='一级表头1' then t3.YUESHIJIZHI else 0 end) YUESHIJIZHI'+index+' ,';
col+="{field:'YUEWANCHENGJINDU"+index+"',title:'月完成进度',width:60,align:'right'},";
caseSql+='sum(case when t3.timeCd='一级表头2' then t3.YUEWANCHENGJINDU else 0 end) YUEWANCHENGJINDU'+index+' ,';
}
3、代码示例：
/**生成数据区表格----获取月份列**/
function makeDataTable() {
var month = $('#month').val();
var monthSection = $('#monthSection').val();
if (monthSection == "" || monthSection == null) {
monthSection = 2;
}
var paramStr = "month="+removeYearMonth(month)+"&monthSection="+monthSection;
$.ajax({
type: "post",
contentType: "application/x-www-form-urlencoded; charset=utf-8",
url : "/com.linkage.bi3.operateMoniter.module.KPIQueryModule.gotoJson(kpi_query,getAllMonth).db(tdssyn).invoke",
dataType:'json',
data: paramStr,
success: function(json){
var rowsed = json.rows;
if (rowsed.length > 0 && attrArr.length > 0) {
makeDataGrid(rowsed);
}
}
});
}
/**生成数据区表格----获取数据值**/
function makeDataGrid(rowsed) {
var rowFlag = true;
var caseSql = "";
var col="[[{field:'AREANAME',title:'地市',width:50,rowspan:2} ,";
for(var i=0;i<rowsed.length;i++){
if(i>0) {
if(rowsed[i].TIMECD == rowsed[i-1].TIMECD) {
rowFlag = false;
}
if(rowsed[i].TIMECD != rowsed[i-1].TIMECD) {
rowFlag = true;
}
}
var colspan = 0;
for (var k = 0; k < attrArr.length; k++) {
if (attrArr[k] == 1) {
colspan++;
}
}
if(rowFlag) {
col += "{title:'"+rowsed[i].TIMECD+"',align:'right',colspan:"+colspan+" },";
}
}
col=col.substring(0,col.length-1);
col+="],[";
for(var i=0;i<rowsed.length;i++){//找出需要显示的列
var index = i+1;
if (attrArr[0] == 1) {
col+="{field:'YUESHIJIZHI"+index+"',title:'实际值',width:60,align:'right',formatter:function(val,rec){return formatInte(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.YUESHIJIZHI else 0 end) YUESHIJIZHI'+index+' ,';
}
if (attrArr[1] == 1) {
col+="{field:'YUEWANCHENGJINDU"+index+"',title:'月完成进度',width:60,align:'right',formatter:function(val,rec){return formatNuber(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.YUEWANCHENGJINDU else 0 end) YUEWANCHENGJINDU'+index+' ,';
}
if (attrArr[2] == 1) {
col+="{field:'YUEMUBIAOCHAJU"+index+"',title:'月目标差距',width:60,align:'right',formatter:function(val,rec){return formatInte(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.YUEMUBIAOCHAJU else 0 end) YUEMUBIAOCHAJU'+index+' ,';
}
if (attrArr[3] == 1) {
col+="{field:'YUEHUANBI"+index+"',title:'月环比',width:60,align:'right',formatter:function(val,rec){return formatNuber(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.YUEHUANBI else 0 end) YUEHUANBI'+index+' ,';
}
if (attrArr[4] == 1) {
col+="{field:'YUETONGBI"+index+"',title:'月同比',width:60,align:'right',formatter:function(val,rec){return formatNuber(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.YUETONGBI else 0 end) YUETONGBI'+index+' ,';
}
if (attrArr[5] == 1) {
col+="{field:'YUEZHANBI"+index+"',title:'月占比',width:60,align:'right',formatter:function(val,rec){return formatNuber(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.YUEZHANBI else 0 end) YUEZHANBI'+index+' ,';
}
if (attrArr[6] == 1) {
col+="{field:'LEIJISHIJIZHI"+index+"',title:'累计实际值',width:60,align:'right',formatter:function(val,rec){return formatInte(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.LEIJISHIJIZHI else 0 end) LEIJISHIJIZHI'+index+' ,';
}
if (attrArr[7] == 1) {
col+="{field:'LEIJIWANCHENGJINDU"+index+"',title:'累计完成进度',width:60,align:'right',formatter:function(val,rec){return formatNuber(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.LEIJIWANCHENGJINDU else 0 end) LEIJIWANCHENGJINDU'+index+' ,';
}
if (attrArr[8] == 1) {
col+="{field:'LEIJIMUBIAOCHAJU"+index+"',title:'累计目标差距',width:60,align:'right',formatter:function(val,rec){return formatInte(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.LEIJIMUBIAOCHAJU else 0 end) LEIJIMUBIAOCHAJU'+index+' ,';
}
if (attrArr[9] == 1) {
col+="{field:'LEIJITONGBI"+index+"',title:'累计同比',width:60,align:'right',formatter:function(val,rec){return formatNuber(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.LEIJITONGBI else 0 end) LEIJITONGBI'+index+' ,';
}
if (attrArr[10] == 1) {
col+="{field:'LEIJIZHANBI"+index+"',title:'累计占比',width:60,align:'right',formatter:function(val,rec){return formatNuber(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.LEIJIZHANBI else 0 end) LEIJIZHANBI'+index+' ,';
}
if (attrArr[11] == 1) {
col+="{field:'LEIJILADONGLV"+index+"',title:'累计拉动率',width:60,align:'right',formatter:function(val,rec){return formatNuber(val)}},";
caseSql+='sum(case when t3.timeCd='+rowsed[i].TIMECD+' then t3.LEIJILADONGLV else 0 end) LEIJILADONGLV'+index+' ,';
}
}
caseSql=caseSql.substring(0,caseSql.length-1);
col=col.substring(0,col.length-1);
col+="]]";
//alert(col);
var fieldArr = setDataFiledSql(attrArr);
var selField = fieldArr[0];
var caseField2 = fieldArr[1];
var month = $('#month').val();
var monthSection = $('#monthSection').val();
if (monthSection == "" || monthSection == null) {
monthSection = 2;
}
var areaId = $('#area').val();
if (areaId == null || areaId == '') {
areaId = userAreaId;
}
var dimensionId = $('#dimension').val();
if (dimensionId == null || dimensionId == '') {
dimensionId = allDimensionId;
}
var dimensionValueId = $('#dimensionValue').val();
if (dimensionValueId == null || dimensionValueId == '') {
dimensionValueId = allDimensionValueId;
}
var whereArea = "";
if (areaId != '1') {//选的地区如果是地市，那么sql加地市条件
whereArea = " and t.std_area_id="+areaId;
}
$('#dataTable').datagrid({
title:'数据区',
loadMsg:"数据加载中，请稍等！..",
width:newWih,
//height:600,
nowrap:false,
striped:true,
collapsible:true,
url:'/com.linkage.bi3.operateMoniter.module.KPIQueryModule.gotoJson(kpi_query,getMeasureAllData).db(tdssyn).invoke?',
queryParams:{"caseField":caseSql,"selField":selField,"caseField2":caseField2,"measureCd":measureCd,"month":removeYearMonth(month),"whereArea":whereArea,"dimensionId":dimensionId,"dimensionValueId":dimensionValueId,"allDimensionId":allDimensionId,"allDimensionValueId":allDimensionValueId,"monthSection":monthSection},
sortName:'AREAID',
remoteSort: false,
columns:eval(col),
onLoadSuccess:function (){
}
});
}
