下面是在oracle下实现的一个函数,求大牛帮转成mysql的CREATE OR REPLACE FUNCTION ROW_TO_COL_FUNC (tabname in varchar2,
v_xmbh in varchar2,
v_dwbh in varchar2,
v_lrsj_begin in varchar2 default to_char( Trunc(sysdate,'month'),'yyyy-mm-dd'),
v_lrsj_end in varchar2 default to_char(sysdate,'yyyy-mm-dd') )return varchar2
Authid Current_User
as
sqlstr varchar2(2000):='select AAB034,AA011,AA001,AAB001,';
ub sys_refcursor;
tab varchar2(100);
vardate varchar2(20):='yyyy-mm-dd';
begin
open ub for 'select distinct AA014 from '||tabname||' where AA011='''||v_xmbh||''' and AAB001= '''||v_dwbh||'''';
loop
fetch ub into tab;
exit when ub%notfound;
sqlstr:=sqlstr||',max(case when AA014='''||tab||''' then AA015 else '''||''' end) '||tab;
end loop ;
close ub;
sqlstr :=sqlstr||',AAE216,AAE218,AA013,AA016 from '||tabname||' where to_char(AAE216,'''||vardate||''') between '''||v_lrsj_begin||''' and '''||v_lrsj_end||''' and AA011='''||v_xmbh||''' and AAB001='''||v_dwbh||''' group by AAB034,AA011,AA001,AAB001,AAE216,AAE218,AA013,AA016';
return sqlstr;
end row_to_col_func;
MySQL行转列
v_xmbh in varchar2,
v_dwbh in varchar2,
v_lrsj_begin in varchar2 default to_char( Trunc(sysdate,'month'),'yyyy-mm-dd'),
v_lrsj_end in varchar2 default to_char(sysdate,'yyyy-mm-dd') )return varchar2
Authid Current_User
as
sqlstr varchar2(2000):='select AAB034,AA011,AA001,AAB001,';
ub sys_refcursor;
tab varchar2(100);
vardate varchar2(20):='yyyy-mm-dd';
begin
open ub for 'select distinct AA014 from '||tabname||' where AA011='''||v_xmbh||''' and AAB001= '''||v_dwbh||'''';
loop
fetch ub into tab;
exit when ub%notfound;
sqlstr:=sqlstr||',max(case when AA014='''||tab||''' then AA015 else '''||''' end) '||tab;
end loop ;
close ub;
sqlstr :=sqlstr||',AAE216,AAE218,AA013,AA016 from '||tabname||' where to_char(AAE216,'''||vardate||''') between '''||v_lrsj_begin||''' and '''||v_lrsj_end||''' and AA011='''||v_xmbh||''' and AAB001='''||v_dwbh||''' group by AAB034,AA011,AA001,AAB001,AAE216,AAE218,AA013,AA016';
return sqlstr;
end row_to_col_func;
MySQL行转列
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
CREATE TABLE `c_wssb_zz` (
`aa011` varchar(10) default NULL,
`aa001` varchar(20) default NULL,
`aa014` varchar(20) default NULL,
`aa015` varchar(200) default NULL,
`aab001` varchar(20) default NULL,
`aa010` varchar(30) default NULL,
`aae216` date default NULL,
`aa013` varchar(3) default NULL,
`aa016` varchar(10) default NULL,
`aae218` date default NULL,
`aab034` varchar(20) default NULL,
`aab901` varchar(255) default NULL
) 加入这是要转换的表,表名不确定,作为一个参数, aab001、AA011这两个也不确定,也是参数,显示的结果把AA014这一列的内容横过来内容,对应AA015, 最后形成的数据格式如下
AAB034,AA011,AA001,AAB001,max(case when aa014='col1' then aa015 else '' end)as col1,max(case when aa014='col2' then aa015 else '' end)as col2,....max(case when aa014='colN' then aa015 else '' end)as colN,AAE216,AAE218,AA013,AA016