一道高难度的sql面试题目
昨天去海淀区知春路上的一家公司面试。有下面一道题目，好像在哪里见过，不过我没有做出来，偷偷把题目抄了下来。请大家帮我看一下，谢谢了！
有表A，结构如下：
A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为：
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。
[解决办法]
A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
--------------
select p_id, s1_id =case when s_id= '01 ' then p_num else 0 end,
s2_id =case when s_id= '02 ' then p_uum else 0 end,
s3_id =case when s_id= '03 ' then p_num else 0 end
from t
[解决办法]
declare @tb table(p_ID int,p_Num int,s_id varchar(2))
insert @tb select 1,10, '01 '
union all select 1,12, '02 '
union all select 2,8, '02 '
union all select 3,11, '01 '
union all select 3,8, '03 '
select * from @tb
--select p_ID,case from @tb group by p_ID
select p_id, sum(case when s_id= '01 ' then p_Num else 0 end) s1_id,
sum(case when s_id= '02 ' then p_Num else 0 end) s2_id,
sum(case when s_id= '03 ' then p_Num else 0 end) s3_id
from @tb group by p_id
[解决办法]
if object_id( 'test1 ') is not null
drop table test1
create table test1(
p_id int,
p_Num int,
s_id varchar(10)
)
insert into test1
select 1,10, '01 ' union
select 1,12, '02 ' union
select 2,8, '01 ' union
select 3,11, '01 ' union
select 3,8, '03 '
select * from test1
select p_id,s1_id=sum(case when s_id= '01 ' then p_num else '0 ' end),
s2_id=sum(case when s_id= '02 ' then p_num else '0 ' end),
s3_id=sum(case when s_id= '03 ' then p_num else '0 ' end)
from test1 group by p_id
[解决办法]
SQL> select tt.p_id,
2 max(decode(tt.s_id, '01 ',tt.p_num,0)) as s_id_01,
3 max(decode(tt.s_id, '02 ',tt.p_num,0)) as s_id_02,
4 max(decode(tt.s_id, '03 ',tt.p_num,0)) as s_id_03
5 from (select 1 as p_ID,10 as p_Num, '01 ' as s_id from dual
6 union all
7 select 1 as p_ID,12 as p_Num, '02 ' as s_id from dual
8 union all
9 select 2 as p_ID,8 as p_Num, '01 ' as s_id from dual
10 union all
11 select 3 as p_ID,11 as p_Num, '01 ' as s_id from dual
12 union all
13 select 3 as p_ID,8 as p_Num, '03 ' as s_id from dual
14 )tt
15 group by tt.p_id;
P_ID S_ID_01 S_ID_02 S_ID_03
---------- ---------- ---------- ----------
1 10 12 0
2 8 0 0
3 11 0 8
[解决办法]
--寫個動態的
create table t(p_ID int,p_Num int,s_id varchar(2))
insert t select 1,10, '01 '
union all select 1,12, '02 '
union all select 2,8, '01 '
union all select 3,11, '01 '
union all select 3,8, '03 '
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',sum(case when s_id= ' ' '+s_id+ ' ' ' then p_Num else 0 end) as [s '+rtrim(convert(int,s_id))+ '_id] '
from t
group by s_id
select @sql= 'select p_ID '+@sql+ ' from t group by p_ID '
exec (@sql)
/*
p_ID s1_id s2_id s3_id
----------- ----------- ----------- -----------
1 10 12 0
2 8 0 0
3 11 0 8
*/
drop table t
[解决办法]
哪家公司啊我去
select p_id,sum(case when s_id= '01 ' then p_num else 0 end) as s1_id,
sum(case when s_id= '02 ' then p_num else 0 end) as s2_id,
sum(case when s_id= '03 ' then p_num else 0 end) as s3_id
from # group by p_id
[解决办法]
create table test1(
p_id int,
p_Num int,
s_id varchar(10)
)
select distinct p_id,s1_id = isnull((select sum(p_Num) from test1 where p_id = a.p_id and s_id = '01 '),0)
,s2_id = isnull((select sum(p_Num) from test1 where p_id = a.p_id and s_id = '02 '),0)
,s3_id = isnull((select sum(p_Num) from test1 where p_id = a.p_id and s_id = '03 '),0)
from test1 a
---------------------------------------
110120
2800
31108
[解决办法]
declare @tb table(p_ID int,p_Num int,s_id varchar(2))
insert @tb select 1,10, '01 '
union all select 1,12, '02 '
union all select 2,8, '01 '
union all select 3,11, '01 '
union all select 3,8, '03 '
select p_ID,s1_id=sum(case s_id when 01 then p_num else 0 end),
s2_id=sum(case s_id when 02 then p_num else 0 end),
s2_id=sum(case s_id when 03 then p_num else 0 end)
from @tb
group by p_ID
[解决办法]
select p_id,sum(case when s_id=1 then p_num else 0 end) as s1_id,
sum(case when s_id=2 then p_num else 0 end) as s2_id,
sum(case when s_id=3 then p_num else 0 end) as s3_id from test1
group by p_id
结果：
p_id s1_id s2_id s3_id
----------- ----------- ----------- -----------
1 10 12 0
2 8 0 0
3 11 0 8
[解决办法]
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',sum(case when s_id= '+convert(varchar(4),s_id)+' then p_Num else 0 end) as [s'+rtrim(convert(varchar(10),s_id))+'_id] '
from a
group by s_id
select @sql= 'select p_ID '+@sql+ ' from a group by p_ID '
exec (@sql)
[解决办法]
和查学生成绩单一样
select distinct x.p_ID,isnull((select p_Num from table_name where s_id='1'and p_ID=x.p_ID),'0') as s1_id,
isnull((select P_Num from table_name where s_id='2'and p_ID = x.P_ID),'0') as s2_id,
isnull((select p_Num from table_name where s_id='3'and P_ID= x.p_id ),'0') as s3_id
from table_name as x
