广州某公司笔试题(英文)-sql

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');

 

 

 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;