自定义queryAction

361874552

我要把2个表的数据合并起来，看论坛的帖子，使用下面的代码，但是提示我的ksql语句错误，我找不出来错误地点。
        public static Table queryMZ_MZYPGL1(String concept,String select,String from,String aggregate,String dataModel,String fnModel,String condition,List range,Boolean distinct,String idColumn,String filter,Integer limit,Integer offset,String columns,String orderBy,String aggregateColumns,Map variables){
                //将UI传过来的过滤条件放入ksql
        String where = "";
        if(filter != null && !"".equals(filter)){
                where = " and " + filter;
        }
        //String sql = " from ER_WPXX ER_WPXX " + where;

        //计算出总记录数
        int cnt = 0;
        Table tCount = KSQL.select("select count(a.MD5) as cnt from (select M_mzddgl.fKHMC as fKHMC,M_ddfypc.D3 as D3,M_mzddgl.MD5 as MD5,M_ddfypc.D2 as D2,M_ddfypc.D5 as D5,M_ddfypc.D6 as D6,M_ddfypc.D12 as D12,M_ddfypc from M_ddfypc M_ddfypc optional join M_mzddgl M_mzddgl on M_ddfypc.D1 = M_mzddgl where M_ddfypc.D12 > :addDays( :currentDate() , 0 - 30) AND M_mzddgl is not null "+where+" union select '' as fKHMC,'' as D3,MZ_MZYPGL.Y1 as MD5,MZ_MZYPGL.Y5 as D2,1 as D5,2 as D6,MZ_MZYPGL.Y3 as D12,MZ_MZYPGL from MZ_MZYPGL MZ_MZYPGL where MZ_MZYPGL.Y4 <> '原煤仓' and MZ_MZYPGL.Y4 <> '精煤仓' and MZ_MZYPGL.Y3 > :addDays( :currentDate() , 0 - 5)"+where+") a group by a.MD5", variables, dataModel, null);
        Iterator<Row> it1 = tCount.iterator();
        if(it1.hasNext()){
            Row r = it1.next();
                cnt = r.getInt("cnt");
        }        

        //加上offset和limit实现分页
        Table table = KSQL.select("  select M_mzddgl.fKHMC as fKHMC,M_ddfypc.D3 as D3,M_mzddgl.MD5 as MD5,M_ddfypc.D2 as D2,M_ddfypc.D5 as D5,M_ddfypc.D6 as D6,M_ddfypc.D12 as D12,M_ddfypc from M_ddfypc M_ddfypc optional join M_mzddgl M_mzddgl on M_ddfypc.D1 = M_mzddgl where M_ddfypc.D12 > :addDays( :currentDate() , 0 - 30) AND M_mzddgl is not null "+where+" union select '' as fKHMC,'' as D3,MZ_MZYPGL.Y1 as MD5,MZ_MZYPGL.Y5 as D2,1 as D5,2 as D6,MZ_MZYPGL.Y3 as D12,MZ_MZYPGL from MZ_MZYPGL MZ_MZYPGL where MZ_MZYPGL.Y4 <> '原煤仓' and MZ_MZYPGL.Y4 <> '精煤仓' and MZ_MZYPGL.Y3 > :addDays( :currentDate() , 0 - 5)"+where+" order by D12 desc" + " limit "+Integer.toString(offset)+","+Integer.toString(limit), variables, dataModel, null);
        //不需要设置主键
        //table.getProperties().put(Table.PROP_NAME_ROWID, "ER_WPXX");
        //设置总记录数
        table.getProperties().put(Table.PROP_DB_COUNT, cnt);
        return table;
        }
}

daniel007

出错的是什么.如果你要查询两张表的信息,可以直接调用Action(自定义),直接用SQL 语句就可以进行查询了(select a.*,b.* from 表 a  表 b where)Sql语句你可以在网上搜索就行.

361874552

daniel007 发表于 2014-10-29 15:19
出错的是什么.如果你要查询两张表的信息,可以直接调用Action(自定义),直接用SQL 语句就可以进行查询了(sele ...

我是把2个结果合并到一起，不增加列，数据在行上增加

361874552

select M_mzddgl.fKHMC as fKHMC,M_ddfypc.D3 as D3,M_mzddgl.MD5 as MD5,M_ddfypc.D2 as D2,M_ddfypc.D5 as D5,M_ddfypc.D6 as D6,M_ddfypc.D12 as D12,M_ddfypc
    from M_ddfypc M_ddfypc optional
    join M_mzddgl M_mzddgl on M_ddfypc.D1 = M_mzddgl
    where M_ddfypc.D12 > :addDays( :currentDate() , 0 - 30) AND M_mzddgl is not null union select '' as fKHMC,'' as D3,MZ_MZYPGL.Y1 as MD5,MZ_MZYPGL.Y5 as D2,1 as D5,2 as D6,MZ_MZYPGL.Y3 as D12,MZ_MZYPGL from MZ_MZYPGL MZ_MZYPGL where MZ_MZYPGL.Y4 <> '原煤仓' and MZ_MZYPGL.Y4 <> '精煤仓' and MZ_MZYPGL.Y3 > :addDays( :currentDate() , 0 - 5)


这个执行正确，但是 count（）就不行了

361874552

361874552 发表于 2014-10-29 15:37
我是把2个结果合并到一起，不增加列，数据在行上增加

select count(a.MD5) as cnt
    from (select M_mzddgl.fKHMC as fKHMC,M_ddfypc.D3 as D3,M_mzddgl.MD5 as MD5,M_ddfypc.D2 as D2,M_ddfypc.D5 as D5,M_ddfypc.D6 as D6,M_ddfypc.D12 as D12,M_ddfypc from M_ddfypc M_ddfypc optional
    join M_mzddgl M_mzddgl on M_ddfypc.D1 = M_mzddgl
    where M_ddfypc.D12 > :addDays( :currentDate() , 0 - 30) AND M_mzddgl is not null union select '' as fKHMC,'' as D3,MZ_MZYPGL.Y1 as MD5,MZ_MZYPGL.Y5 as D2,1 as D5,2 as D6,MZ_MZYPGL.Y3 as D12,MZ_MZYPGL from MZ_MZYPGL MZ_MZYPGL where MZ_MZYPGL.Y4 <> '原煤仓' and MZ_MZYPGL.Y4 <> '精煤仓' and MZ_MZYPGL.Y3 > :addDays( :currentDate() , 0 - 5)) a
    group by a.MD5

说是缺少右括号，不知道错在哪

暗夜的忧伤

使用countAll试下

361874552

暗夜的忧伤 发表于 2014-10-29 16:04
使用countAll试下

不行

361874552

重写queryaction，gird 怎么加载啊

暗夜的忧伤

使用这个com.justep.system.data.SQL代替KSQL呢

jishuang

361874552 发表于 2014-10-29 16:15
重写queryaction，gird 怎么加载啊

比较复杂的语句推荐用sql实现

重写了queryAction后，bizData的reader关联重写的这个action
grid绑定这个bizData就能显示出数据