高薪软件公司的笔试压轴题（求解）

[xuboying]

有两个表, t1, t2,
Table t1:

SELLER | NON_SELLER
----- -----

A B
A C
A D
B A
B C
B D
C A
C B
C D
D A
D B
D C


Table t2:

SELLER | COUPON | BAL
----- --------- ---------
A 9 100
B 9 200
C 9 300
D 9 400
A 9.5 100
B 9.5 20
A 10 80



要求用SELECT 语句列出如下结果:------如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和.......
且用的方法不要增加数据库负担,如用临时表等.

NON-SELLER| COUPON | SUM(BAL) ------- --------
A 9 900
B 9 800
C 9 700
D 9 600
A 9.5 20
B 9.5 100
C 9.5 120
D 9.5 120
A 10 0
B 10 80
C 10 80
D 10 80

[peng1014]

study

[zhang_yzy]

select
a.non-seller,
a.coupon,
sum(case when c.bal is null then 0 else c.bal end) as 'sum(bal)'
from
t2 a
inner join t1 b on a.non-seller=b.seller
left join (select
seller,coupon,sum(bal) as bal
from
t2
group by
seller,coupon) c on b.seller=c.seller and a.coupon=c.coupon
group by
a.non-seller,
a.coupon
order by
a.non-seller,
a.coupon

[lsxaa]

left join and inner join

[vinsonshen]

我想应该是对t1.SELLER和t2.COUPON分组，且t1.NON_SELLER决定t1.SELLER的求和列:

select c.*,(select isnull(sum(bal),0) from t2 where coupon=c.coupon and seller in (select NON_SELLER from t1 where seller=c.seller)) as [SUM(BAL)] from
(select a.seller,b.coupon from t1 as a cross join (select coupon from t2 group by coupon) as b group by a.seller,b.coupon) as c order by cast(coupon as float),c.seller

[vinsonshen]

create table t1(SELLER varchar(5), NON_SELLER varchar(5))
Insert into t1
select 'A','B'
union all select 'A','C'
union all select 'A','D'
union all select 'B','A'
union all select 'B','C'
union all select 'B','D'
union all select 'C','A'
union all select 'C','B'
union all select 'C','D'
union all select 'D','A'
union all select 'D','B'
union all select 'D','C'
-----------------------
create table t2(SELLER varchar(5),COUPON numeric(10,1), BAL numeric(10))
Insert into t2
select 'A','9','100'
union all select 'B','9','200'
union all select 'C','9','300'
union all select 'D','9','400'
union all select 'A','9.5','100'
union all select 'B','9.5','20'
union all select 'A','10','80'
------------------------------------
seller coupon SUM(BAL)
------ ------------ ----------------------------------------
A 9.0 900
B 9.0 800
C 9.0 700
D 9.0 600
A 9.5 20
B 9.5 100
C 9.5 120
D 9.5 120
A 10.0 0
B 10.0 80
C 10.0 80
D 10.0 80

（所影响的行数为 12 行）
-------------------
drop table t1,t2

[vinsonshen]

TO：hdhai9451(Water Space--海洋空間)

t1中，若SELLER为A时，其对应的 NON_SELLER只包含(C和D)，而没有B 的话，那你的数据就会累加多B的了

[yugang1219]

高薪软件公司是多高?说出来了解哈行情撒

[shouzheng]

学习中,复杂在从前表到后表的联系

[swellyu]

关注

[ouyangwu]

回复人： xiaomeixiang(独孤寒梅) ( ) 信誉：100 2004-12-11 14:48:00 得分: 0


應該這樣更準確一些：

select a.SELLER NON_SELLER ,b.COUPON,(select ISNULL(sum(BAL),0) from t2 where SELLER IN (select NON_SELLER from t1 where SELLER=a.SELLER) and COUPON=b.COUPON) SUMBAL
from
(select distinct SELLER from t1) a,
(select distinct COUPON from t2) b
order by COUPON,SELLER


标准答案，呵呵

[crlonline]

楼上的，你有没有试过你的答案啊？你的答案好像有语法错误哦！

[chengran]

参考独孤寒梅的答案，只用t2

select SELLER,COUPON,
ISNULL((select sum(BAL) from t2 where SELLER<>a.SELLER and COUPON=b.COUPON),0)SUMBAL
from
(select distinct SELLER from t2) a,
(select distinct COUPON from t2) b
order by COUPON,SELLER

[Keown]

笔试测试自己一下，也只是得到11条记录的结果，唉~~挂掉

[空闲独处]

凑个热闹给两种解法:

解法1:
SELECT A.SELLER, B.COUPON, (SELECT IIF(ISNULL(SUM(BAL)),0,SUM(BAL)) FROM T2 WHERE SELLER IN (SELECT NON_SELLER FROM T1 WHERE SELLER=A.SELLER) AND COUPON=B.COUPON) AS SUMBAL
FROM
(SELECT DISTINCT SELLER FROM T1) A,
(SELECT DISTINCT COUPON FROM T2) B
ORDER BY COUPON,SELLER

解法2:
SELECT T.SELLER, T.COUPON, IIF(IsNULL(Sum(T2.BAL)) ,0,Sum(T2.BAL)) AS BALの合計
FROM T2 RIGHT JOIN [SELECT T1.SELLER, T1.NON_SELLER, T2.COUPON
FROM T1, T2
GROUP BY T1.SELLER, T1.NON_SELLER,T2.COUPON
ORDER BY T2.COUPON]. AS T ON (T2.COUPON = T.COUPON) AND (T2.SELLER = T.NON_SELLER)
GROUP BY T.SELLER, T.COUPON
ORDER BY T.COUPON;

[ocean9b]

select 'NON-SELLER'=A.SELLER,B.COUPON,'SUM(BAL)'=isnull((select sum(BAL) from t2 where COUPON=B.COUPON and SELLER in (select NON_SELLER from t1 where SELLER=A.SELLER)),0) from (select distinct SELLER from t1) A,(select distinct COUPON from t2) B order by B.COUPON,A.SELLER

这个应该没问题

[MINDNET]

学习

[yyyjff]

select
temp1.SELLER,
temp2.COUPON,
[SUM(BAL)] =
isnull((select sum(BAL) from t2
where SELLER in
(select NON_SELLER
from t1 where t1.SELLER = temp1.SELLER) and t2.COUPON=temp2.COUPON)
,0)
from
(select distinct SELLER from t1)temp1,
(select distinct COUPON from t2)temp2
order by temp2.COUPON,temp1.SELLER

[yesyesyes]

咳,没希望进高薪公司,连题意都没看懂,惭愧!

[bobohuang]

性能最快之寫法
select a.SELLER,a.coupon,sum(isnull(c.bal,0)) sum from
(select distinct t1.seller,non_seller,t2.coupon from t1,t2) a
left join t2 c
on a.non_SELLER=c.SELLER and a.coupon=c.coupon
group by a.SELLER,a.coupon

[xuboying]

帮同学问的,看过大家的答案他已经会做了,谢谢大家了,
ps,估计高薪工作没戏了,呵呵