一个分组统计问题,比较困惑

Question

艾，想了无数久，终于想出来了/*create table t1
( seller varchar(4),
  noseller varchar(4)
)insert into t1 select 'A','B'
union select 'A','C'
union select 'A','D'
union select 'B','A'
union select 'B','C'
union select 'B','D'
union select 'C','A'
union select 'C','B'
union select 'C','D'
union select 'D','A'
union select 'D','B'
union select 'D','C'create table t2
(seller varchar(3),
 coupon float,
  bal float)
insert into t2
select 'a', 9.0, 100
union select 'b',    9.0, 200
union select 'c', 9.0, 300
union select 'd', 9.0, 400
union select 'a', 9.5, 100
union select 'b', 9.5, 20
union select 'a', 10.0, 80
*/select  a.seller,b.coupon,convert(int,sum(isnull(bal,0))) from(select distinct seller from t1) a 
cross join (select distinct coupon from t2) b join t1 c on a.seller = c.seller
left join t2 d on b.coupon = d.coupon and c.noseller = d.seller
group by b.coupon,a.seller
order by b.coupon,a.seller

Answer 1

create table tb(SELLER varchar(10),COUPON decimal(8,2),BAL int)
insert into tb
select 'a',9.0,100 union all
select 'b',9.0,200 union all
select 'c',9.0,300 union all
select 'd',9.0,400 union all
select 'a',9.5,100 union all
select 'b',9.5,20 union all
select 'a',10.0,80select a.[NON-SELLER],a.coupon,[SUM(BAL)]=a.sum1-isnull(b.bal,0)
from (
select * from 
(
select 'A' as 'NON-SELLER'  union all
select 'B' as 'NON-SELLER'  union all
select 'C' as 'NON-SELLER'  union all
select 'D' as 'NON-SELLER') a  ,
(select coupon,sum1=sum(bal) from tb group by COUPON) b ) a
left join tb b on a.[NON-SELLER]=b.SELLER and a.coupon=b.coupondrop table tb
/*
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
*/

Answer 2

--借用一楼的cross join用法
create table tb(SELLER varchar(10),COUPON decimal(8,2),BAL int)
insert into tb
select 'a',9.0,100 union all
select 'b',9.0,200 union all
select 'c',9.0,300 union all
select 'd',9.0,400 union all
select 'a',9.5,100 union all
select 'b',9.5,20 union all
select 'a',10.0,80select a.[NON-SELLER],b.coupon,[SUM(BAL)]=b.sum1-isnull(c.bal,0)
from 
(
select 'A' as 'NON-SELLER'  union all
select 'B' as 'NON-SELLER'  union all
select 'C' as 'NON-SELLER'  union all
select 'D' as 'NON-SELLER') a  cross join
(select coupon,sum1=sum(bal) from tb group by COUPON) b 
left join tb c on a.[NON-SELLER]=c.SELLER and b.coupon=c.coupondrop table tb
/*
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
*/

Answer 3

wgsasd311(自强不息) ( ) 信誉：100 
你的sql语句只能面对特殊情况：即 a 由(b,c,d),b(a,c,d),c(a,b,d),d(a,b,c) 如果表t1发生变化比如说A只对应B,C的话，就会出问题
----------------------------------------------------------------------
楼上的意思使用cross join 得到coupon(group by)的和，然后再减去自身所对应的coupon

Answer 4

--测试表及数据
declare @t1 table
( seller varchar(4),
  NON_SELLER varchar(4)
)insert into @t1 select 'A','B'
union select 'A','C'
union select 'A','D'
union select 'B','A'
union select 'B','C'
union select 'B','D'
union select 'C','A'
union select 'C','B'
union select 'C','D'
union select 'D','A'
union select 'D','B'
union select 'D','C'declare @t2 table
(seller varchar(3),
 coupon float,
  bal float)
insert into @t2
select 'a', 9.0, 100
union select 'b',    9.0, 200
union select 'c', 9.0, 300
union select 'd', 9.0, 400
union select 'a', 9.5, 100
union select 'b', 9.5, 20
union select 'a', 10.0, 80
--处理语句
select ta.NON_SELLER,ta.coupon,isnull(tb.bal,0) as 'SUM(BAL)'
from 
(select new1.NON_SELLER,new2.coupon
from (select distinct NON_SELLER from @t1) new1,
(select distinct coupon from @t2) new2) ta left join 
(select NON_SELLER,coupon,bal=isnull(sum(newbal),0)
from 
(select a.NON_SELLER,b.coupon,newbal=(select sum(bal) from @t2 where seller=a.seller and coupon=b.coupon)
from @t1 a,@t2 b
where a.seller=b.seller) new
group by NON_SELLER,coupon
) tb
on ta.NON_SELLER=tb.NON_SELLER and ta.coupon=tb.coupon
order by ta.coupon
/*结果
non_seller coupon sum(bal)
---------------------------------
A 9.0 900.0
B 9.0 800.0
C 9.0 700.0
D 9.0 600.0
A 9.5 20.0
B 9.5 100.0
C 9.5 120.0
D 9.5 120.0
A 10.0 0.0
B 10.0 80.0
C 10.0 80.0
D 10.0 80.0*/

Answer 5

谢谢大家,如果表t1发生变化的话,楼上的语句调整一下就可以了,楼上的把SELLER  NON_SELLER  搞混了,呵呵
ok,再次表示感谢 tianhxk,wgsasd311,rivery(river)老兄的热心