Mysql(case/coalesce) 通过产品表和出入库表实现产品出入库统计

ONE.  DataBase

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

参考答案：

 

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',
coalesce(sum(case when io.inout='in' then io.num else -io.num end),0) 'Save'
from product p left join inoutlist io on p.id=io.productID group by p.id;