标题: MYSQL 销售数据按日期行转换为列 [打印本页] 作者: scosure 时间: 2009-11-10 11:56 标题: MYSQL 销售数据按日期行转换为列
MYSQL 销售数据按日期行转换为列 语句:
CREATE PROCEDURE `PROC_GET_SUM_Report`(
tag char(1),
btime varchar(10),
etime varchar(10))
BEGIN
declare $stm varchar(8000);
declare $rowcnt int;
declare $mycnt int;
declare $i int;
set $mycnt = 0;
set $i = 1;
set $stm='select storeno as 编号,storename as 店名,estorename as 店英文名';
if tag='0' then
select count(distinct workdate) into $rowcnt from xa_dg_temp_emp_sum_cross where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) = date(workdate);
WHILE $i=$rowcnt DO
set @i :=0;
select col from (select (@i := @i +1 ) as iden,CONCAT($stm,',SUM(case workdate when ''',workdate,''' then slmny else 0 end) as ''',workdate,'''') as col from (select distinct workdate from xa_dg_temp_emp_sum_cross where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) = date(workdate)) as tb) as tb1 where iden=$i into $stm;
Set $i:=$i+1;
END WHILE;
end if;
if tag='1' then
select count(distinct workdate) into $rowcnt from xa_dg_temp_emp_sum_cross where workdate>=btime and workdate=etime;
WHILE $i=$rowcnt DO
set @i :=0;
select col from (select (@i := @i +1 ) as iden,CONCAT($stm,',SUM(case workdate when ''',workdate,''' then slmny else 0 end) as ''',workdate,'''') as col from (select distinct workdate from xa_dg_temp_emp_sum_cross where workdate>=btime and workdate=etime) as tb) as tb1 where iden=$i into $stm;
Set $i:=$i+1;
END WHILE;
end if;
Set @stm=concat($stm,' from xa_dg_temp_emp_sum_cross where paycode=''01'' group by storeno,storename,estorename order by storeno;');
prepare s from @stm;
execute s;
deallocate prepare s;
END
原始数据图：
行转换列后效果图:
