Duvida sobre SubQuery

[Ronnie Luiz]

Senhores estou tentando criar um código para gerar o consumo de equipamentos mas o resultado está errado.

Tenho as seguintes tabelas:

CREATE TABLE `consumo_tipo` (

`Id_Sup` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID suprimento',

`Descricao` varchar(20) DEFAULT NULL COMMENT 'Descriçao do suprimento',

`Status_Sup` int(1) DEFAULT NULL COMMENT 'Status do suprimento',

PRIMARY KEY (`Id_Sup`)

);

CREATE TABLE `consumo` (

`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Índice do registro',

`Mat_Lub` int(11) DEFAULT NULL COMMENT 'Matricula do responsável pelo abastecimento',

`ID_Aloc` int(11) DEFAULT NULL COMMENT 'Índice da alocação',

`ID_Cont` int(11) DEFAULT NULL COMMENT 'Índice do contrato',

`Turno` int(11) NOT NULL DEFAULT '0' COMMENT 'Índice do Turno',

`Data` date DEFAULT NULL COMMENT 'Data do Registro do abastecimento',

`ID_Equipe` int(11) DEFAULT NULL,

PRIMARY KEY (`Id`)

);

CREATE TABLE `consumo_equip` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`ID_Consumo` int(11) DEFAULT NULL,

`Serie_Equip` varchar(20) DEFAULT NULL COMMENT 'número de série do equipamento abastecido',

`Horimetro` double(7,1) DEFAULT NULL COMMENT 'Horímetro do registro',

PRIMARY KEY (`Id`),

KEY `FK_ConsEquip_Cons` (`ID_Consumo`),

CONSTRAINT `FK_ConsEquip_Cons` FOREIGN KEY (`ID_Consumo`) REFERENCES `consumo` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE `consumo_desc` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`ID_Cons_Equip` int(11) DEFAULT NULL,

`ID_Sup` int(11) DEFAULT NULL COMMENT 'Índice do suprimento',

`Qtde_Consumo` double(8,3) DEFAULT NULL COMMENT 'Quantidade consumida',

`Justificativa` varchar(200) DEFAULT NULL COMMENT 'Caso haja um abastecimento excessivo',

PRIMARY KEY (`Id`),

KEY `FK_ConsDesc_ConsEquip` (`ID_Cons_Equip`),

KEY `FK_ConsDesc_Tipos` (`ID_Sup`),

CONSTRAINT `FK_ConsDesc_ConsEquip` FOREIGN KEY (`ID_Cons_Equip`) REFERENCES `consumo_equip` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `FK_ConsDesc_Tipos` FOREIGN KEY (`ID_Sup`) REFERENCES `consumo_tipo` (`Id_Sup`)

);

INSERT INTO `consumo_tipo` VALUES (1,'Óleo Diesel',0);

INSERT INTO `consumo_tipo` VALUES (2,'Óleo Hidráulico',1);

INSERT INTO `consumo_tipo` VALUES (3,'Óleo de Corrente',1);

INSERT INTO `consumo_tipo` VALUES (4,'Lubrificante Motor',1);

INSERT INTO `consumo` VALUES (1,210,1,1,1,'2010-10-01',2);

INSERT INTO `consumo` VALUES (2,210,1,1,1,'2010-10-01',2);

INSERT INTO `consumo` VALUES (3,5,1,1,1,'2010-10-02',3);

INSERT INTO `consumo` VALUES (4,5,1,1,1,'2010-10-02',3);

INSERT INTO `consumo_equip` VALUES (13,1,'13',1000);

INSERT INTO `consumo_equip` VALUES (14,2,'12',2000);

INSERT INTO `consumo_equip` VALUES (15,2,'13',1020);

INSERT INTO `consumo_equip` VALUES (16,3,'12',2020);

INSERT INTO `consumo_equip` VALUES (17,4,'12',2030);

INSERT INTO `consumo_desc` VALUES (13,13,1,100,NULL);

INSERT INTO `consumo_desc` VALUES (14,13,2,10,NULL);

INSERT INTO `consumo_desc` VALUES (15,14,1,100,NULL);

INSERT INTO `consumo_desc` VALUES (16,14,2,10,NULL);

INSERT INTO `consumo_desc` VALUES (17,14,3,5,NULL);

INSERT INTO `consumo_desc` VALUES (18,14,4,2,NULL);

INSERT INTO `consumo_desc` VALUES (19,15,1,100,NULL);

INSERT INTO `consumo_desc` VALUES (20,15,2,10,NULL);

INSERT INTO `consumo_desc` VALUES (21,15,3,3,NULL);

INSERT INTO `consumo_desc` VALUES (22,16,1,100,NULL);

INSERT INTO `consumo_desc` VALUES (23,17,1,100,NULL);

# Segue o código que estou tentando executar.

SELECT serie_equip,

(SELECT SUM(Qtde_consumo) FROM consumo_desc

WHERE consumo_desc.ID_Sup = 1

AND consumo_desc.ID_Cons_Equip = consumo_equip.id) AS 'Diesel',

(SELECT SUM(Qtde_consumo) FROM consumo_desc

WHERE consumo_desc.ID_Sup = 2

AND consumo_desc.ID_Cons_Equip = consumo_equip.id) AS 'Hidr.'

FROM consumo_equip

GROUP BY Serie_Equip ;

Caso alguém possa me ajudar... Grato.

[Denis Courcy]

Oi, 'Ronnie Luiz'

A salada que você fez no select é muito grande e não dá para decifrar rápido.

Qual o resultado que você quer?

[Ronnie Luiz]

Olá Denis, desde já agradeço. O que preciso são relatórios de consumo de insumos tipo:

* Relatório de consumo de insumos

Equip----|---Diesel----|---Hidraulico--|--Ol. Motor

Maq 01........250,0...........12,1...............2,0

Maq 02........300,0...........15,3...............3,0

Maq 03........262,0...........13,2...............2,0

* Relatório de consumo de Diesel por dia

Equip----|----01----|----02----|----03----|----04----|----05 ...

Maq 01......120,0.......132,0.......110,2.......110,2......115,7

Maq 02......132,3.......126,8.......123,5.......143,2......142,1

O Select postado geraria um resultado parecido com o primeiro exemplo (Relatório de consumo de insumos). Só que ele não está somando os valores...

Editado Novembro 1, 2010 por Ronnie Luiz

[Denis Courcy]

Oi, 'Ronnie Luiz'!

Você vai precisar montar uma pivot table para obter este resultado.

Uma select simples para o primeiro resultado seria:

SELECT cd.id_Cons_Equip, ct.descricao, SUM(cd.qtde_consumo)
FROM consumo.consumo_desc cd
INNER JOIN consumo.consumo_tipo ct ON ct.id_sup = cd.id_sup
GROUP BY cd.id_Cons_Equip, ct.descricao;

Mas este não virá do jeito que você quer. Criando esta storage procedure:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `dmsase`.`pivotwizard`$$

CREATE DEFINER=`root`@`Localhost` PROCEDURE `pivotwizard`(
IN P_Row_Field VARCHAR(255), IN P_Column_Field VARCHAR(255), IN P_Value VARCHAR(255),
IN P_From VARCHAR(4000), IN P_Where VARCHAR(4000))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE M_Count_Columns int DEFAULT 0;
DECLARE M_Column_Field varchar(60);
DECLARE M_Columns VARCHAR(8000) DEFAULT '';
DECLARE M_sqltext VARCHAR(8000);
DECLARE M_stmt VARCHAR(8000);
DECLARE cur1 CURSOR FOR SELECT CAST(Column_Field AS CHAR) FROM Temp;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS Temp;
SET @M_sqltext = CONCAT('CREATE TEMPORARY TABLE Temp ', 
' SELECT DISTINCT ',P_Column_Field, ' AS Column_Field', 
' FROM ',P_From, 
' WHERE ',P_Where, 
' ORDER BY ', P_Column_Field);
PREPARE M_stmt FROM @M_sqltext;
EXECUTE M_stmt;
SELECT COUNT(*) INTO M_Count_Columns
FROM Temp
WHERE Column_Field IS NOT NULL;
IF (M_Count_Columns > 0) THEN
OPEN cur1;
REPEAT
FETCH cur1 INTO M_Column_Field;
IF (NOT done) and (M_Column_Field IS NOT NULL) THEN
SET M_Columns = CONCAT(M_Columns,
' SUM( CASE WHEN ',P_Column_Field,'=''',M_Column_Field,'''',
' THEN ',P_Value,
' ELSE 0 END) AS `', M_Column_Field ,'`,');
END IF;
UNTIL done END REPEAT;
SET M_Columns = Left(M_Columns,Length(M_Columns)-1);
SET @M_sqltext = CONCAT('SELECT ',P_Row_Field,',',M_Columns,
' FROM ', P_From,
' WHERE ', P_Where,
' GROUP BY ', P_Row_Field,
' ORDER BY ', P_Row_Field);
PREPARE M_stmt FROM @M_sqltext;
EXECUTE M_stmt;
END IF;
DEALLOCATE PREPARE stmt;
END$$

DELIMITER;$$

E passando os parâmetros certos (os parametros da consulta simples acima), teremos:

CALL pivotwizard('cd.id_Cons_Equip','ct.descricao','cd.qtde_consumo','consumo_desc cd
INNER JOIN consumo_tipo ct ON ct.id_sup = cd.id_sup', '1=1');

O resultado é o que você queria no primeiro exemplo dado no post anterior.

Para responder ao segundo exemplo eu preciso de mais dados. Estão faltando tabelas no modelo que você passou?

[Ronnie Luiz]

Denys mais uma vez gostaria de agradecer pela ajuda. Desculpe pela demora, é que fiquei uns dias sem computador...

O seu código ainda é um pouco complexo pra mim, sou iniciante na programação... Não consegui entender bem após o REPEAT... Mas no teste que fiz aqui o resultado ficou zerado, não somou os valores.

Fiz duas correções em seu código, no CAST a variável 'Column_Field' e no 'DEALLOCATE PREPARE stmt' para 'P_Column_Field' e 'M_stmt' respectivamente.

Abaixo segue o resultado da sua 'pivotwizard':

id_Cons_Equip______ct.descricao______ct.descricao______ct.descricao______ct.descricao

13_________________0.000_____________0.000___________0.000___________0.000

14_________________0.000_____________0.000___________0.000___________0.000

15_________________0.000_____________0.000___________0.000___________0.000

16_________________0.000_____________0.000___________0.000___________0.000

17_________________0.000_____________0.000___________0.000___________0.000