Please use MySQL to Create DataBase like the language below<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
/*TabelName:inoutlist, Record the pass in and out of the storage*/
CREATE TABLE `inoutlist` (
/*Auto increment*/
`AutoID` int(11) NOT NULL auto_increment,
/*Time of pass in or out*/
`RecordTime` datetime NOT NULL default '0000-00-00 00:00:00',
/*ProductID*/
`ProductID` int(11) default NULL,
/*tag：in or out*/
`InOut` enum('out','in') default NULL,
/*the number of pass in or out*/
`Num` int(11) default NULL,
/*Primary Key*/
PRIMARY KEY (`AutoID`)
);
/*insert data to table inoutlist*/
insert into InOutList values (0,'2007-12-3 11:39:25',1,'out',3);
insert into InOutList values (0,'2007-12-4 10:39:25',2,'out',2);
insert into InOutList values (0,'2007-12-5 11:39:25',1,'out',5);
insert into InOutList values (0,'2007-12-5 11:39:26',2,'out',9);
insert into InOutList values (0,'2007-12-5 11:39:27',3,'out',3);
insert into InOutList values (0,'2007-12-5 09:39:25',2,'in',10);
insert into InOutList values (0,'2007-12-5 13:39:56',3,'in',5);
insert into InOutList values (0,'2007-12-6 09:39:30',2,'in',6);
insert into InOutList values (0,'2007-12-6 09:39:31',2,'in',8);
insert into InOutList values (0,'2007-12-6 09:39:32',1,'in',100);
insert into InOutList values (0,'2007-12-6 09:39:33',2,'in',50);
insert into InOutList values (0,'2007-12-6 09:39:34',3,'in',60);
insert into InOutList values (0,'2007-12-6 09:39:35',4,'in',10);
/*TableName:product, products in storage*/
CREATE TABLE `product` (
`ID` int(11) NOT NULL default '0',
`Name` varchar(255) default NULL,
PRIMARY KEY (`ID`)
) ;
/*insert data to table product*/
insert into product values (1,'Product1');
insert into product values (2,'Product2');
insert into product values (3,'Product3');
insert into product values (4,'Product4');
insert into product values (5,'Product5');
Application running as the form below：
Table:InOutList：
┌───┬─────────┬─────┬───┬───┐
│AutoID│RecordTime │ProductID │InOut │ Num │
├───┼─────────┼─────┼───┼───┤
│ 1 │2007-12-3 11:39:25│ 1 │ out │ 3 │
├───┼─────────┼─────┼───┼───┤
│ 2 │2007-12-4 10:39:25│ 2 │ out │ 2 │
├───┼─────────┼─────┼───┼───┤
│ 3 │2007-12-5 11:39:25│ 1 │ out │ 5 │
├───┼─────────┼─────┼───┼───┤
│ 4 │2007-12-5 11:39:26│ 2 │ out │ 9 │
├───┼─────────┼─────┼───┼───┤
│ 5 │2007-12-5 11:39:27│ 3 │ out │ 3 │
├───┼─────────┼─────┼───┼───┤
│ 6 │2007-12-5 09:39:25│ 2 │ in │ 10 │
├───┼─────────┼─────┼───┼───┤
│ 7 │2007-12-5 13:39:56│ 3 │ in │ 5 │
├───┼─────────┼─────┼───┼───┤
│ 8 │2007-12-6 09:39:30│ 2 │ in │ 6 │
├───┼─────────┼─────┼───┼───┤
│ 9 │2007-12-6 09:39:31│ 2 │ in │ 8 │
├───┼─────────┼─────┼───┼───┤
│ 10 │2007-12-6 09:39:32│ 1 │ in │ 100 │
├───┼─────────┼─────┼───┼───┤
│ 11 │2007-12-6 09:39:33│ 2 │ in │ 50 │
├───┼─────────┼─────┼───┼───┤
│ 12 │2007-12-6 09:39:34│ 3 │ in │ 60 │
├───┼─────────┼─────┼───┼───┤
│ 13 │2007-12-6 09:39:35│ 4 │ in │ 10 │
└───┴─────────┴─────┴───┴───┘
Table:Product：
┌───┬─────┐
│ ID │Name │
├───┼─────┤
│ 1 │Product1 │
├───┼─────┤
│ 2 │Product2 │
├───┼─────┤
│ 3 │Product3 │
├───┼─────┤
│ 4 │Product4 │
├───┼─────┤
│ 5 │Product5 │
└───┴─────┘
Demand: write out the SQL sentence which will run as the form below without changing of the original table and data
┌───┬────┬───┬───┬───┐
│ ID │Name │ In │ Out │ Save │
├───┼────┼───┼───┼───┤
│ 1 │Product1│ 100 │ 8 │ 92 │
├───┼────┼───┼───┼───┤
│ 2 │Product2│ 74 │ 11 │ 63 │
├───┼────┼───┼───┼───┤
│ 3 │Product3│ 65 │ 3 │ 62 │
├───┼────┼───┼───┼───┤
│ 4 │Product4│ 10 │ 0 │ 10 │
├───┼────┼───┼───┼───┤
│ 5 │Product5│ 0 │ 0 │ 0 │
└───┴────┴───┴───┴───┘
explanation: ID - ProductID
Name - ProductName
In - accumulative total of the Product pass in storage
Out - accumulative total of the Product pass out storage
Save - stocks of the product
answer:
select p.Id,
p.Name,
sum(case when io.inout='in' then io.num else 0 end) 'In',
sum(case when io.inout='out' then io.num else 0 end) 'Out',
case when sum(case when io.inout='in' then io.num else -io.num end) is null then 0 else sum(case when io.inout='in' then io.num else -io.num end) end 'Save'
from product p left join inoutlist io on p.id=io.productID group by p.id;