主从报表数据不匹配

唐坤

主表的FID和从表的FZBID是主从关系，单数数据不匹配，按道理第一个主表对应的从表应该没数据才对，下面是主从表的sql

《-----主表-----》
public static Table selectFAZB(List range,String concept,String select,String from,String aggregate,String dataModel,String fnModel,String condition,Boolean distinct,String idColumn,String filter,Integer limit,Integer offset,String columns,String orderBy,String aggregateColumns,Map variables){
                String sql = "";
                sql = "SELECT b.FID AS FID,c.fDWID AS FXMBH,c.fDWMC AS FXMMC,a.FSGJDBM AS FSGJDBM,a.FSGJD AS FSGJD,a.FND AS FND,a.FYF AS FYF,a.FSL AS FZXQRS,a.FBZ AS  FBZ,b.FBMID AS FBMID,b.FBMMC AS FBMMC,b.FGWID AS FGWID,b.FBMMC||'/'||b.FGWMC AS FGWMC,b.FSL AS FDQGWXQRS,b.FZWJWRQ AS  FZWJWRQ,b.FZSJWRS AS FZSJWRS "+
                          "FROM HR_XMQZQRYJHCB a "+
                          "LEFT JOIN HR_XMQZQRYJHCCB b ON a.FID = b.FZBID "+
                          "LEFT JOIN HR_YGGLZB       c ON c.fID = a.FZBID "+
                          "WHERE b.FID IS NOT NULL AND c.fDWID = '"+filter+
                          "'ORDER BY a.FND ASC,a.FYF ASC ";
                System.out.println(sql);
                Table table = SQL.select(sql, null, "/baseCommon/data");
                return table;
        }
《-----从表-----》
public static Table selectFACB(List range,String concept,String select,String from,String aggregate,String dataModel,String fnModel,String condition,Boolean distinct,String idColumn,String filter,Integer limit,Integer offset,String columns,String orderBy,String aggregateColumns,Map variables){
                String sql = "";
                sql = "SELECT a.FID AS FID_CB,a.FZBID AS FZBID_CB,a.FYGID AS FYGID_CB,a.FYGXM AS FYGXM_CB,a.FDWID AS FDWID_CB,a.FDWMC AS FDWMC_CB,a.FGWID AS FGWID_CB,a.FGWMC AS FGWMC_CB "+
                          "FROM HR_XMRYPZFACB a "+
                          "WHERE EXISTS(SELECT 1 FROM HR_XMQZQRYJHCCB b LEFT JOIN HR_XMQZQRYJHCB c ON b.FZBID = c.FID LEFT JOIN HR_YGGLZB d ON c.FZBID = d.fID WHERE d.FID = c.FZBID AND a.FZBID = b.FID AND d.FDWID = '"+filter+"') ";
               
                System.out.println(sql);
                Table table = SQL.select(sql, null, "/baseCommon/data");
                return table;
        }

jishuang

从上设置主从过滤条件时不需要写从data.XX，直接写关系XX

唐坤

jishuang 发表于 2020-3-30 14:27
从上设置主从过滤条件时不需要写从data.XX，直接写关系XX

我重做了一遍，不行，

唐坤

找到原因了，第一列是年度，重复会影响，select对应列好像不能重复

jishuang

select列就相当于主键肯定不能重复