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

来源：互联网发布：电影产业数据编辑：程序博客网时间：2024/04/30 18:30

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;

//出处: http://blog.csdn.net/lenotang/archive/2008/08/26/2835270.aspx

case的用法:

CASE WHEN condition THEN result     [WHEN ...]     [ELSE result]END

SQL CASE 是一种通用的条件表达式，类似于其他语言里的 if/else 语句． CASE 子句可以用于任何表达式可以有效存在的地方． condition 是一个返回boolean 的表达式．如果结果为真，那么 CASE 表达式的结果就是 result．如果结果为假，那么以相同方式搜寻任何随后的 WHEN 子句．如果没有 WHEN condition 为真，那么 case 表达式的结果就是在 ELSE 子句里的值．如果省略了 ELSE 子句而且没有匹配的条件，结果为 NULL．

例子：

=> SELECT * FROM test; a--- 1 2 3=> SELECT a,CASE WHEN a=1 THEN 'one'WHEN a=2 THEN 'two'ELSE 'other'ENDFROM test; a | case---+------- 1 | one 2 | two 3 | other

所有 result 表达式的数据的类型都必须可以转换成单一的输出类型．参阅 Section 7.5 获取细节．

CASE expression    WHEN value THEN result    [WHEN ...]    [ELSE result]END

这个"简单的" CASE 表达式是上面的通用形式的一个特殊的变种．先计算 expression 的值，然后与所有在WHEN 子句里的 value 对比，直到找到一个相等的．如果没有找到匹配的，则返回在 ELSE 子句里的 result （或者 NULL）．这个类似于 C 里的 switch 语句．

上面的例子可以用简单 CASE 语法来写：

=> SELECT a,CASE aWHEN 1 THEN 'one'WHEN 2 THEN 'two'ELSE 'other'ENDFROM test; a | case---+------- 1 | one 2 | two 3 | other

//from http://docs.huihoo.com/postgresql/pgsql-doc-7.3/functions-conditional.html

答案:

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',

sum(case when io.inout='in' then io.num when io.inout='out' then -io.num else 0 end) 'Save'

from product p left join inoutlist io

on p.id=io.productID group by p.id;

SAVE中有错:

select p.Id,
p.Name,
case when
(select sum(case when io1.inout='in' then io1.num else 0 end)
from inoutlist io1 where p.id=io1.productID ) is null then 0
else (select sum(case when io1.inout='in' then io1.num else 0 end)
from inoutlist io1 where p.id=io1.productID )
end
as 'In',
case when
(select sum(case when io2.inout='out' then io2.num else 0 end)
from inoutlist io2 where p.id=io2.productID )
is null then 0
else
(select sum(case when io2.inout='out' then io2.num else 0 end)
from inoutlist io2 where p.id=io2.productID )
end
as 'Out',
('In' - 'Out') as 'Save'
from product p ;