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

Question

select  t1.non_seller,t2.coupon,count(*),sum(bal) from t1 cross join t2 where t2.seller=t1.seller  group by t1.non_seller,t2.coupon显示结果集为：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
B 10 80
C 10 80
D 10 80但是少了 A 10 0 行
暂无完美方案

Answer 1

你说了一大通，是不是这个意思
select  NON-SELLER,COUPON ,SUM(BAL)  as sumbal  from  t1,t2  where  t1.SELLER =t2.SELLER 
group by NON-SELLER,COUPON

Answer 2

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

Answer 3

應該這樣更準確一些：select *,ISNULL((select sum(BAL) from t2 where SELLER IN (select NON_SELLER from t1 where SELLER=a.SELLER) and COUPON=b.COUPON),0) SUMBAL 
from 
(select distinct SELLER from t1) a,
(select distinct COUPON from t2) b 
order by COUPON,SELLER

Answer 4

--猜的,不知道是否这个意思
select a.NON_SELLER
,COUPON=isnull(b.COUPON,0)
,[SUM(BAL)]=isnull(b.BAL,0)
from t1 a
left join(
select SELLER,COUPON
,BAL=sum(BAL)
from t2 
group by SELLER,COUPON
)b on a.NON_SELLER=b.SELLER

Answer 5

t2先按COUPON 分組求和，然后再與t1連接，關把t1表的數據減出去

Answer 6

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'--刪除表
drop table t1,t2--查詢
SELECT  A.*,BAL=sum(BAL)-ISNULL((SELECT BAL FROM t2 WHERE SELLER=A.SELLER AND COUPON=A.COUPON),0)
FROM
(select * from (select distinct SELLER from t1)a,(select distinct COUPON from t2)b)A 
LEFT JOIN t2 B ON A.COUPON=B.COUPON
GROUP BY A.SELLER,A.COUPON
ORDER BY 2--結果
SELLER   COUPON   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

Answer 7

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

Answer 8

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

Answer 9

left join    and   inner join

Answer 10

我想应该是对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

Answer 11

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

Answer 12

TO：hdhai9451(Water Space--海洋空間)t1中，若SELLER为A时，其对应的 NON_SELLER只包含(C和D)，而没有B 的话，那你的数据就会累加多B的了

Answer 13

回复人： 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
标准答案，呵呵
 

Answer 14

参考独孤寒梅的答案，只用t2select 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

Answer 15

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

Answer 16

凑个热闹给两种解法:解法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;

Answer 17

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这个应该没问题

Answer 18

性能最快之寫法
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