Please use MySQL to Create DataBase like the language below/*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');
insert into product values (@a,'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
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');
insert into product values (@a,'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
+--------+---------------------+-----------+-------+------+
| AutoID | RecordTime | ProductID | InOut | Num |
+--------+---------------------+-----------+-------+------+
| 1 | 2007-12-03 11:39:25 | 1 | out | 3 |
| 2 | 2007-12-04 10:39:25 | 2 | out | 2 |
| 3 | 2007-12-05 11:39:25 | 1 | out | 5 |
| 4 | 2007-12-05 11:39:26 | 2 | out | 9 |
| 5 | 2007-12-05 11:39:27 | 3 | out | 3 |
| 6 | 2007-12-05 09:39:25 | 2 | in | 10 |
| 7 | 2007-12-05 13:39:56 | 3 | in | 5 |
| 8 | 2007-12-06 09:39:30 | 2 | in | 6 |
| 9 | 2007-12-06 09:39:31 | 2 | in | 8 |
| 10 | 2007-12-06 09:39:32 | 1 | in | 100 |
| 11 | 2007-12-06 09:39:33 | 2 | in | 50 |
| 12 | 2007-12-06 09:39:34 | 3 | in | 60 |
| 13 | 2007-12-06 09:39:35 | 4 | in | 10 |
+--------+---------------------+-----------+-------+------+
13 rows in set (0.08 sec)mysql> select * from product;
+----+----------+
| ID | Name |
+----+----------+
| 1 | Product1 |
| 2 | Product2 |
| 3 | Product3 |
| 4 | Product4 |
| 5 | Product5 |
+----+----------+
5 rows in set (0.00 sec)mysql> select p.id,p.name,
-> sum(if(a.InOut='in',Num,0)) as `In`,
-> sum(if(a.InOut='out',Num,0)) as `Out`,
-> sum(if(a.InOut='in',Num,0))-sum(if(a.InOut='out',Num,0)) as `Save`
-> from product p left join InOutList a on p.id=a.ProductID
-> group by p.id,p.name;
+----+----------+------+------+------+
| 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 |
+----+----------+------+------+------+
5 rows in set (0.06 sec)mysql>
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html