Description:
I have next database:
# phpMyAdmin SQL Dump
# version 2.5.5-pl1
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Jan 15, 2004 at 10:20 AM
# Server version: 5.0.0
# PHP Version: 4.3.4
#
# Database : `r003`
#
CREATE DATABASE `r003`;
USE r003;
# --------------------------------------------------------
#
# Table structure for table `materialcatalog`
#
CREATE TABLE `materialcatalog` (
`id` int(11) NOT NULL auto_increment,
`parent` int(11) default NULL,
`name` varchar(64) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
#
# Dumping data for table `materialcatalog`
#
INSERT INTO `materialcatalog` VALUES (1, NULL, 'Òêàíè');
INSERT INTO `materialcatalog` VALUES (2, NULL, 'Íèòêè');
# --------------------------------------------------------
#
# Table structure for table `materialcost`
#
CREATE TABLE `materialcost` (
`id` int(11) NOT NULL auto_increment,
`material_id` int(11) NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`cost1` double NOT NULL default '0',
`cost2` double NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `Material` (`material_id`),
KEY `Date` (`date`),
KEY `CostGetting` (`material_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
#
# Dumping data for table `materialcost`
#
INSERT INTO `materialcost` VALUES (1, 3, '2004-01-01', '1', '2');
INSERT INTO `materialcost` VALUES (2, 4, '2004-01-01', '10', '20');
INSERT INTO `materialcost` VALUES (3, 1, '2004-01-01', '10', '20');
INSERT INTO `materialcost` VALUES (4, 3, '2004-01-02', '2', '4');
# --------------------------------------------------------
#
# Table structure for table `materials`
#
CREATE TABLE `materials` (
`id` int(11) NOT NULL auto_increment,
`catalog` int(11) NOT NULL default '0',
`name` varchar(64) NOT NULL default '',
`attr1` varchar(16) NOT NULL default '',
`attr2` varchar(16) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `catalog` (`catalog`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
#
# Dumping data for table `materials`
#
INSERT INTO `materials` VALUES (1, 1, 'Òêàíü õëîïêîâàÿ', 'Ò001', 'Êã.');
INSERT INTO `materials` VALUES (2, 1, 'Òêàíü øåëêîâàÿ', 'Ò002', 'Êã.');
INSERT INTO `materials` VALUES (3, 2, 'Íèòêè õëîïêîâûå', 'Í001', 'Êã.');
INSERT INTO `materials` VALUES (4, 2, 'Íèòêè øåëêîâûå', 'Í002', 'Êã.');
# --------------------------------------------------------
#
# Table structure for table `productcardcost`
#
CREATE TABLE `productcardcost` (
`id` int(11) NOT NULL auto_increment,
`card_id` int(11) NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`cost` double NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `Cards` (`card_id`),
KEY `Date` (`date`),
KEY `CostGetting` (`card_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
#
# Dumping data for table `productcardcost`
#
INSERT INTO `productcardcost` VALUES (1, 1, '2004-01-01', '100');
INSERT INTO `productcardcost` VALUES (2, 2, '2004-01-14', '12');
# --------------------------------------------------------
#
# Table structure for table `productcardjob`
#
CREATE TABLE `productcardjob` (
`id` int(11) NOT NULL auto_increment,
`card_id` int(11) NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`time` double NOT NULL default '0',
`cost` double NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `Cards` (`card_id`),
KEY `Date` (`date`),
KEY `CostGetting` (`card_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
#
# Dumping data for table `productcardjob`
#
INSERT INTO `productcardjob` VALUES (1, 1, '2004-01-01', '0.5', '25');
INSERT INTO `productcardjob` VALUES (2, 2, '2004-01-01', '1', '0.13');
# --------------------------------------------------------
#
# Table structure for table `productcardmaterial`
#
CREATE TABLE `productcardmaterial` (
`id` int(11) NOT NULL auto_increment,
`card_id` int(11) NOT NULL default '0',
`material_id` int(11) NOT NULL default '0',
`num` double NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `Materials` (`card_id`,`material_id`),
KEY `Cards` (`card_id`),
KEY `CommonMaterials` (`material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
#
# Dumping data for table `productcardmaterial`
#
INSERT INTO `productcardmaterial` VALUES (1, 1, 3, '1');
INSERT INTO `productcardmaterial` VALUES (2, 1, 1, '1000');
INSERT INTO `productcardmaterial` VALUES (3, 2, 4, '10');
INSERT INTO `productcardmaterial` VALUES (4, 2, 2, '10');
# --------------------------------------------------------
#
# Table structure for table `productcards`
#
CREATE TABLE `productcards` (
`id` int(11) NOT NULL auto_increment,
`product_id` int(11) NOT NULL default '0',
`name` varchar(64) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `product_id_2` (`product_id`,`name`),
KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
#
# Dumping data for table `productcards`
#
INSERT INTO `productcards` VALUES (1, 1, 'Íîâàÿ òåõíîëîãè÷åñêàÿ êàðòà.');
INSERT INTO `productcards` VALUES (2, 2, 'Îñíîâíàÿ òåõíîëîãè÷åñêàÿ êàðòà');
# --------------------------------------------------------
#
# Table structure for table `productcatalog`
#
CREATE TABLE `productcatalog` (
`id` int(11) NOT NULL auto_increment,
`parent` int(11) default NULL,
`name` varchar(64) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
#
# Dumping data for table `productcatalog`
#
INSERT INTO `productcatalog` VALUES (1, NULL, 'Äåòñêèé òðèêîòàæ');
INSERT INTO `productcatalog` VALUES (2, 1, 'Ìàéêè');
# --------------------------------------------------------
#
# Table structure for table `production`
#
CREATE TABLE `production` (
`id` int(11) NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`card_id` int(11) NOT NULL default '0',
`num` double NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `Production` (`date`,`card_id`),
KEY `Dates` (`date`),
KEY `Cards` (`card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
#
# Dumping data for table `production`
#
INSERT INTO `production` VALUES (1, '2004-01-01', 1, '1');
INSERT INTO `production` VALUES (2, '2004-01-02', 1, '10');
INSERT INTO `production` VALUES (3, '2004-01-01', 2, '50');
# --------------------------------------------------------
#
# Table structure for table `productionmaterial`
#
CREATE TABLE `productionmaterial` (
`id` int(11) NOT NULL auto_increment,
`production_id` int(11) NOT NULL default '0',
`product_material_id` int(11) NOT NULL default '0',
`material_num` double NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `Materials` (`production_id`,`product_material_id`),
KEY `Production` (`production_id`),
KEY `CommonMaterials` (`product_material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
#
# Dumping data for table `productionmaterial`
#
INSERT INTO `productionmaterial` VALUES (4, 1, 2, '1002');
# --------------------------------------------------------
#
# Table structure for table `products`
#
CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`catalog` int(11) NOT NULL default '0',
`Name` varchar(64) NOT NULL default '',
`attr1` varchar(32) NOT NULL default '',
`attr2` varchar(32) NOT NULL default '',
`attr3` varchar(32) NOT NULL default '',
`attr4` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `catalog` (`catalog`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
#
# Dumping data for table `products`
#
INSERT INTO `products` VALUES (1, 2, 'Ìàéêà äåòñêàÿ', 'ÄÌ001', 'äì001', '23', 'Áåëûé');
INSERT INTO `products` VALUES (2, 2, 'Ìàéêà äåòñêàÿ øåëêîâàÿ', 'ÌÄØ001', 'ìäø001', '24', 'Ñèíèé');
#
# Constraints for dumped tables
#
#
# Constraints for table `materialcatalog`
#
ALTER TABLE `materialcatalog`
ADD CONSTRAINT `0_170` FOREIGN KEY (`parent`) REFERENCES `materialcatalog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
#
# Constraints for table `materialcost`
#
ALTER TABLE `materialcost`
ADD CONSTRAINT `0_176` FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`) ON UPDATE CASCADE;
#
# Constraints for table `materials`
#
ALTER TABLE `materials`
ADD CONSTRAINT `0_174` FOREIGN KEY (`catalog`) REFERENCES `materialcatalog` (`id`) ON UPDATE CASCADE;
#
# Constraints for table `productcardcost`
#
ALTER TABLE `productcardcost`
ADD CONSTRAINT `0_184` FOREIGN KEY (`card_id`) REFERENCES `productcards` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
#
# Constraints for table `productcardjob`
#
ALTER TABLE `productcardjob`
ADD CONSTRAINT `0_186` FOREIGN KEY (`card_id`) REFERENCES `productcards` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
#
# Constraints for table `productcardmaterial`
#
ALTER TABLE `productcardmaterial`
ADD CONSTRAINT `0_182` FOREIGN KEY (`card_id`) REFERENCES `productcards` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
#
# Constraints for table `productcards`
#
ALTER TABLE `productcards`
ADD CONSTRAINT `0_180` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
#
# Constraints for table `productcatalog`
#
ALTER TABLE `productcatalog`
ADD CONSTRAINT `0_172` FOREIGN KEY (`parent`) REFERENCES `productcatalog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
#
# Constraints for table `production`
#
ALTER TABLE `production`
ADD CONSTRAINT `0_188` FOREIGN KEY (`card_id`) REFERENCES `productcards` (`id`) ON UPDATE CASCADE;
#
# Constraints for table `productionmaterial`
#
ALTER TABLE `productionmaterial`
ADD CONSTRAINT `0_190` FOREIGN KEY (`production_id`) REFERENCES `production` (`id`) ON UPDATE CASCADE,
ADD CONSTRAINT `0_191` FOREIGN KEY (`product_material_id`) REFERENCES `productcardmaterial` (`id`) ON UPDATE CASCADE;
#
# Constraints for table `products`
#
ALTER TABLE `products`
ADD CONSTRAINT `0_178` FOREIGN KEY (`catalog`) REFERENCES `productcatalog` (`id`) ON UPDATE CASCADE;
And next query crash the server.
SELECT
`Products`.`id` ,
`Products`.`name` ,
`Products`.`attr1` ,
`Products`.`attr2` ,
`Products`.`attr3` ,
`Products`.`attr4` ,
`ProductCards`.`name` ,
SUM( `ProductCardMaterialCost`.`num` ) ,
SUM( `ProductCardMaterialCost`.`Cost` ) ,
SUM( `ProductCardMaterialCost`.`FactCost` ) ,
SUM( IFNULL( `ProductCardCost`.`cost` , 0 ) * `ProductCardMaterialCost`.`num` )
FROM
`Production` ,
`Products` ,
`ProductCards` ,
(
SELECT
`Production`.`date` ,
`Production`.`card_id` ,
SUM( IFNULL( `ProductCardMaterial`.`num` , 0 ) * `Production`.`num`
* `MaterialCost`.`cost1` )
+ ( `Production`.`num` * `ProductCardJob`.`cost` ) AS Cost ,
SUM( IFNULL( `ProductionMaterial`.`material_num` ,
IFNULL( `ProductCardMaterial`.`num` , 0 )
* `Production`.`num` ) * `MaterialCost`.`cost1` )
+ ( `Production`.`num` * `ProductCardJob`.`cost` ) AS FactCost ,
`Production`.`num`
FROM
`Production`
LEFT JOIN
`ProductCardMaterial`
ON
`ProductCardMaterial`.`card_id` = `Production`.`card_id`
LEFT JOIN
`MaterialCost`
ON
`MaterialCost`.`material_id` = `ProductCardMaterial`.`material_id`
AND
`MaterialCost`.`date` =
(
SELECT
MAX( `date` )
FROM
`MaterialCost`
WHERE
`MaterialCost`.`material_id` = `ProductCardMaterial`.`material_id`
AND
`MaterialCost`.`date` <= `Production`.`date`
)
LEFT JOIN
`ProductCardJob`
ON
`ProductCardJob`.`card_id` = `Production`.`card_id`
AND
`ProductCardJob`.`date` =
(
SELECT
MAX( `date` )
FROM
`ProductCardJob`
WHERE
`ProductCardJob`.`card_id` = `Production`.`card_id`
AND
`ProductCardJob`.`date` <= `Production`.`date`
)
LEFT JOIN
`ProductionMaterial`
ON
`ProductionMaterial`.`production_id` = `Production`.`id`
AND
`ProductionMaterial`.`product_material_id` = `ProductCardMaterial`.`id`
WHERE
`Production`.`date` >= '2001-01-01'
AND
`Production`.`date` <= '2005-01-01'
GROUP BY `Production`.`date` , `Production`.`card_id`
) ProductCardMaterialCost
LEFT JOIN
`ProductCardCost`
ON
`ProductCardCost`.`card_id` = `Production`.`card_id`
AND
`ProductCardCost`.`date` =
(
SELECT
MAX( `date` )
FROM
`ProductCardCost`
WHERE
`ProductCardCost`.`card_id` = `Production`.`card_id`
AND
`ProductCardCost`.`date` <= `Production`.`date`
)
WHERE
`Production`.`date` >= '2001-01-01'
AND
`Production`.`date` <= '2005-01-01'
AND
`ProductCards`.`id` = `Production`.`card_id`
AND
`Products`.`id` = `ProductCards`.`product_id`
AND
`ProductCardMaterialCost`.`card_id` = `Production`.`card_id`
AND
`ProductCardMaterialCost`.`date` = `Production`.`date`
GROUP BY `Production`.`card_id`
ORDER BY `Products`.`attr1`
How to repeat:
any time
