任何人都可以帮助我使用动态数据透视表的两个表的总和 [英] Can anyone help me with the sum of two tables with a dynamic pivot

问题描述

任何人都可以通过动态支点帮助我使用两张桌子的总和。





表格：

horas_trabajadas



equ_codigo | tur_codigo | con_fecha | hor_tra_horas



horas_paradas



equ_codigo | tur_codigo | con_fecha | hor_par_hora



添加小时数是相同的代码，对应相同的日期和班次。



< br $> b $ b

Can anyone help me with the sum of two tables with a dynamic pivot.


tables:
horas_trabajadas

equ_codigo | tur_codigo | con_fecha | hor_tra_horas

horas_paradas

equ_codigo | tur_codigo | con_fecha | hor_par_hora

Add hours to be the same code and corresponding to the same date and shift.

begin
declare finish int default 0;
declare cdate date;
declare str, str2 varchar(10000) default "select DISTINCT equ_codigo,";

declare curs cursor for SELECT DISTINCT equ_codigo FROM horas_trabajadas INNER JOIN horas_paradas ON horas_trabajadas.con_fecha=horas_paradas.con
WHERE horas_trabajadas.equ_codigo=horas_paradas.equ_codigo between sdate and edate group by con_fecha;
declare continue handler for not found set finish = 1;
open curs;
my_loop:loop
fetch curs into cdate;
if finish = 1 then
leave my_loop;
end if;
set str = concat(str, "sum(case when con_fecha.horas_trabajadas = '",cdate,"' then hor_tra_horas else null end) as `",cdate,"`,");
set str2 = concat(str2, "sum(case when con_fecha.horas_paradas = '",cdate,"' then hor_par_hora else null end) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set str2 = substr(str2,1,char_length(str)-1);
set @str = concat(str," from horas_trabajadas
            group by equ_codigo");
set @str2 = concat(str2," from horas_paradas
            group by equ_codigo");

set @query = concat(str, str2);

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end

Thanx。

Thanx.

推荐答案

Hi
请打开以下链接



SQL Server中的静态和动态数据透视表，没有临时表 [ ^ ]

这篇关于任何人都可以帮助我使用动态数据透视表的两个表的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！