转：棘手的（ＳＱＬ）面试题 马士兵 oracle 教程小作业

转：棘手的（ＳＱＬ）面试题 马士兵 oracle 教程小作业

转自:http://hi.baidu.com/kqogje

一个简单的表TABLE   有100条以上的信息，其中包括：  
  产品                   颜色                           数量  
  产品1                   红色                           123  
  产品1                   蓝色                           126  
  产品2                   蓝色                           103  
  产品2                   红色                           NULL  
  产品2                   红色                           89  
  产品1                   红色                           203  
  。。。。。。。。。。。。  
  请用SQL语句完成以下问题：  
    
    
  1。按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量：  
    
    
    
    
    
  2。按产品分类，将数据按下列方式进行统计显示  
              产品                   红色                       蓝色  

下面是在sql server2000上调试的语句。
create table table4(
cname varchar(10),
color varchar(10),
quanty varchar(10))
insert into table4(cname,color,quanty)values(1,'red',123)
insert into table4(cname,color,quanty)values(1,'blue',126)
insert into table4(cname,color,quanty)values(2,'blue',103)
insert into table4(cname,color,quanty)values(2,'red',null)
insert into table4(cname,color,quanty)values(2,'red',89)
insert into table4(cname,color,quanty)values(1,'red',203)

select * from table4

select a.cname,
sum( case when a.color='red' then quanty else 0 end)as 'red',
sum( case when a.color='blue' then quanty else 0 end)as 'blue'
from table4 a
group by cname
select a.cname,sum( case when a.color='red' then quanty else 0 end)-sum( case when a.color='blue' then quanty else 0 end)as '差额'
from table4 a
group by cname
having sum( case when a.color='red' then quanty else 0 end)>sum( case when a.color='blue' then quanty else 0 end)

 

以上是网上的一个面试题目以及网友针对这个题目给出的答案，虽然说是sqlserver2000上的，其实mysql也是一样的。

首先分析一下这个问题为什么棘手。

大部分的初，中级程序员在实际工作中，根本用不到组函数中的分支逻辑语句，即 case when then else end 这个语法，因为大多数的公司奉行一个原则，就是数据库仅仅作为一个存储数据的仓库，不进行逻辑运算。

对于此类问题，通常的解决方案是，进行多次查询，然后把数据存入程序数组中，再进行逻辑运算。

然后说一下这个问题的要点。

cnamecolorquanty1red1231blue1262blue1032redNULL2red891red203

这里是样例数据，如果我们想知道1和2两个产品谁的红色产品多，首先我们就要知道，1的红色商品有多少个，1的蓝色商品有多少个，2的红色商品有多少个，2的蓝色商品有多少个，我们第一步肯定会毫不犹豫的按照cname来分组，接下来问题就出现了，我们只看分组1的数据

cname  color  quanty 
1      red    123
1      blue   126

1      red    203

分组后，我们想要知道1的红色产品有多少，需要求和，但是如何分别求出red 和blue的和呢。这时候我们就可以使用组函数sum中的分支语法 case when a.color='red' then quanty else 0 end

意思是，如果颜色是红色的时候 累加 quanty列的数据 否则 累加0，这样一来就把红色和蓝色的商品总数给分开了，

相应的，我们sum两次

sum( case when a.color='red' then quanty else 0 end)as 'red',
sum( case when a.color='blue' then quanty else 0 end)as 'blue'
这样在查询的结果中，我们就可以用两列来显示red和blue 两种颜色分别的商品总数

 

select a.cname,
sum( case when a.color='red' then quanty else 0 end)as 'red',
sum( case when a.color='blue' then quanty else 0 end)as 'blue'
from table4 a
group by cname

整个这条语句的结果就非常清晰了如下：

cnameredblue1326126289103

这个一目了然了，我们想要的结果就是1，因为1的红色产品要多于蓝色产品。

当然，这个和题目的要求还有一些小的差距，

题目要求我们查询出来产品的名称以及红色产品和蓝色产品的差值，也就是说，题目要求我们得到的是一条记录，而不是两条，

这里有两种方式来实现，第一种，比较直观，子查询

针对这个结果进一步查询，sql如下

select cname, red - blue from (

select a.cname,
sum( case when a.color='red' then quanty else 0 end)as 'red',
sum( case when a.color='blue' then quanty else 0 end)as 'blue'
from table4 a
group by cname

) as aaa

where red > blue

结果如下

cname red - blue 1200

另外一种就是上边原文所给出的方式，直接一次查询，使用having进行过滤

 

select a.cname,sum( case when a.color='red' then quanty else 0 end)-sum( case when a.color='blue' then quanty else 0 end)as '差额'
from table4 a
group by cname
having sum( case when a.color='red' then quanty else 0 end)>sum( case when a.color='blue' then quanty else 0 end)

 

结果是

cname 差额 1200

ok 这么简单的问题墨迹了半天

总结一下感想，作为程序猿的我们，没事要多在网上搜索一些面试题目，这样才能不会阴沟翻船。