mysql 交叉表别名问题使用的是dbutils

guotb007 2013-10-10 10:52:58

联系一段程序，发现个问题，解决了好久都没成功，请教达人！
一、我使用的是mysql数据库，其中dbutils已修正，可以识别别名。
二、但使用交叉表将行转换成列以后，dbutils中返回的List里面的map就不能通过列名（例如以下sql中的 1、2、3等）获取数值了。即使换成英文字符也不可以。但没有交叉的列（例如：ryfl.row_id,ryfl.cmid等通过别名是可以取到的）。

不知各位有没有遇到过该问题，请达人帮分析一下！！谢谢！！！

select ryfl.row_id,ryfl.cmid,ryfl.flid,cmxx.cmxm,cmxx.cmsfzh,flxx.flmc,flxx.jedw,cmflffxx.nf,
if(sum(case cmflffxx.yf when '1' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '1' then cmflffxx.yf else '' end))) as '1',
if(sum(case cmflffxx.yf when '2' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '2' then cmflffxx.yf else '' end))) as '2',
if(sum(case cmflffxx.yf when '3' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '3' then cmflffxx.yf else '' end))) as '3',
if(sum(case cmflffxx.yf when '4' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '4' then cmflffxx.yf else '' end))) as '4',
if(sum(case cmflffxx.yf when '5' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '5' then cmflffxx.yf else '' end))) as '5',
if(sum(case cmflffxx.yf when '6' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '6' then cmflffxx.yf else '' end))) as '6',
if(sum(case cmflffxx.yf when '7' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '7' then cmflffxx.yf else '' end))) as '7',
if(sum(case cmflffxx.yf when '8' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '8' then cmflffxx.yf else '' end))) as '8',
if(sum(case cmflffxx.yf when '9' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '9' then cmflffxx.yf else '' end))) as '9',
if(sum(case cmflffxx.yf when '10' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '10' then cmflffxx.yf else '' end))) as '10',
if(sum(case cmflffxx.yf when '11' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '11' then cmflffxx.yf else '' end))) as '11',
if(sum(case cmflffxx.yf when '12' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '12' then cmflffxx.yf else '' end))) as '12'
from ryfl
left join (select cmflffxx.yf,cmflffxx.nf,cmflffxx.cmid,cmflffxx.flid,cmflffxx.ryflid from cmflffxx where cmflffxx.nf = '2013') cmflffxx
on ryfl.cmid = cmflffxx.cmid and ryfl.flid = cmflffxx.flid and ryfl.row_id = cmflffxx.ryflid

...全文

110 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

guotb007 2013-10-11

打赏
举报

引用 4 楼 ssp2009 的回复:

虚拟表换个表名或者直接关联cmflffxx 表查询啊。

cmflffxx换个虚拟表也不行，效果是一样的。另：无法直接关联cmflffxx，因为该表的查询是需要条件的。谢谢你！

LongRui888 2013-10-11

打赏
举报

这样行不，试试看：

select ryfl.row_id,ryfl.cmid,ryfl.flid,cmxx.cmxm,cmxx.cmsfzh,flxx.flmc,flxx.jedw,cmflffxx.nf,
if(sum(case cmflffxx.yf when '1' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '1' then cmflffxx.yf else '' end))) as wc1,
if(sum(case cmflffxx.yf when '2' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '2' then cmflffxx.yf else '' end))) as wc2,
if(sum(case cmflffxx.yf when '3' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '3' then cmflffxx.yf else '' end))) as wc3,
if(sum(case cmflffxx.yf when '4' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '4' then cmflffxx.yf else '' end))) as wc4,
if(sum(case cmflffxx.yf when '5' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '5' then cmflffxx.yf else '' end))) as wc5,
if(sum(case cmflffxx.yf when '6' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '6' then cmflffxx.yf else '' end))) as wc6,
if(sum(case cmflffxx.yf when '7' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '7' then cmflffxx.yf else '' end))) as wc7,
if(sum(case cmflffxx.yf when '8' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '8' then cmflffxx.yf else '' end))) as wc8,
if(sum(case cmflffxx.yf when '9' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '9' then cmflffxx.yf else '' end))) as wc9,
if(sum(case cmflffxx.yf when '10' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '10' then cmflffxx.yf else '' end))) as wc10,
if(sum(case cmflffxx.yf when '11' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '11' then cmflffxx.yf else '' end))) as wc11,
if(sum(case cmflffxx.yf when '12' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '12' then cmflffxx.yf else '' end))) as wc12
from ryfl 
left join  (select cmflffxx.yf,cmflffxx.nf,cmflffxx.cmid,cmflffxx.flid,cmflffxx.ryflid from cmflffxx where cmflffxx.nf = '2013') cmflffxx
on ryfl.cmid = cmflffxx.cmid and ryfl.flid = cmflffxx.flid and ryfl.row_id = cmflffxx.ryflid

快溜 2013-10-11

打赏
举报

虚拟表换个表名或者直接关联cmflffxx 表查询啊。

guotb007 2013-10-11

打赏
举报

引用 1 楼 yupeigu 的回复:

这样行不，试试看：

select ryfl.row_id,ryfl.cmid,ryfl.flid,cmxx.cmxm,cmxx.cmsfzh,flxx.flmc,flxx.jedw,cmflffxx.nf,
if(sum(case cmflffxx.yf when '1' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '1' then cmflffxx.yf else '' end))) as wc1,
if(sum(case cmflffxx.yf when '2' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '2' then cmflffxx.yf else '' end))) as wc2,
if(sum(case cmflffxx.yf when '3' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '3' then cmflffxx.yf else '' end))) as wc3,
if(sum(case cmflffxx.yf when '4' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '4' then cmflffxx.yf else '' end))) as wc4,
if(sum(case cmflffxx.yf when '5' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '5' then cmflffxx.yf else '' end))) as wc5,
if(sum(case cmflffxx.yf when '6' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '6' then cmflffxx.yf else '' end))) as wc6,
if(sum(case cmflffxx.yf when '7' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '7' then cmflffxx.yf else '' end))) as wc7,
if(sum(case cmflffxx.yf when '8' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '8' then cmflffxx.yf else '' end))) as wc8,
if(sum(case cmflffxx.yf when '9' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '9' then cmflffxx.yf else '' end))) as wc9,
if(sum(case cmflffxx.yf when '10' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '10' then cmflffxx.yf else '' end))) as wc10,
if(sum(case cmflffxx.yf when '11' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '11' then cmflffxx.yf else '' end))) as wc11,
if(sum(case cmflffxx.yf when '12' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '12' then cmflffxx.yf else '' end))) as wc12
from ryfl 
left join  (select cmflffxx.yf,cmflffxx.nf,cmflffxx.cmid,cmflffxx.flid,cmflffxx.ryflid from cmflffxx where cmflffxx.nf = '2013') cmflffxx
on ryfl.cmid = cmflffxx.cmid and ryfl.flid = cmflffxx.flid and ryfl.row_id = cmflffxx.ryflid

不可以，现在的原因应该是虚拟表和虚拟行的原因！只是我还没找到解决方法。

guotb007 2013-10-11

打赏
举报

引用楼主 guotb007 的回复:

联系一段程序，发现个问题，解决了好久都没成功，请教达人！一、我使用的是mysql数据库，其中dbutils已修正，可以识别别名。二、但使用交叉表将行转换成列以后，dbutils中返回的List里面的map就不能通过列名（例如以下sql中的 1、2、3等）获取数值了。即使换成英文字符也不可以。但没有交叉的列（例如：ryfl.row_id,ryfl.cmid等通过别名是可以取到的）。不知各位有没有遇到过该问题，请达人帮分析一下！！谢谢！！！ select ryfl.row_id,ryfl.cmid,ryfl.flid,cmxx.cmxm,cmxx.cmsfzh,flxx.flmc,flxx.jedw,cmflffxx.nf, if(sum(case cmflffxx.yf when '1' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '1' then cmflffxx.yf else '' end))) as '1', if(sum(case cmflffxx.yf when '2' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '2' then cmflffxx.yf else '' end))) as '2', if(sum(case cmflffxx.yf when '3' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '3' then cmflffxx.yf else '' end))) as '3', if(sum(case cmflffxx.yf when '4' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '4' then cmflffxx.yf else '' end))) as '4', if(sum(case cmflffxx.yf when '5' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '5' then cmflffxx.yf else '' end))) as '5', if(sum(case cmflffxx.yf when '6' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '6' then cmflffxx.yf else '' end))) as '6', if(sum(case cmflffxx.yf when '7' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '7' then cmflffxx.yf else '' end))) as '7', if(sum(case cmflffxx.yf when '8' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '8' then cmflffxx.yf else '' end))) as '8', if(sum(case cmflffxx.yf when '9' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '9' then cmflffxx.yf else '' end))) as '9', if(sum(case cmflffxx.yf when '10' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '10' then cmflffxx.yf else '' end))) as '10', if(sum(case cmflffxx.yf when '11' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '11' then cmflffxx.yf else '' end))) as '11', if(sum(case cmflffxx.yf when '12' then cmflffxx.yf else '' end)=0,'',floor(sum(case cmflffxx.yf when '12' then cmflffxx.yf else '' end))) as '12' from ryfl left join (select cmflffxx.yf,cmflffxx.nf,cmflffxx.cmid,cmflffxx.flid,cmflffxx.ryflid from cmflffxx where cmflffxx.nf = '2013') cmflffxx on ryfl.cmid = cmflffxx.cmid and ryfl.flid = cmflffxx.flid and ryfl.row_id = cmflffxx.ryflid

不可以，现在的原因应该是虚拟表和虚拟行的原因！只是我还没找到解决方法。