数据求和问题

[gswuliu]

加入有一个表如下：
OrderID wareID city money
10001 112 上海 100
10001 112 北京 100
10002 114 上海 100
10002 115 上海 100


我想对money求和，条件是orderid相同但是wareID不同。如果OrderID和wareID都相同则不求和，
最后得出的字段只要OrderID和求和后的money，如上表最后得出的结果为
OrderID money
10001 100
10001 100
10002 200
请问怎么写SQL，多谢大家。

[唐诗三百首]

create table gsw
(OrderID int, wareID int, 
 city varchar(6), money int)   
 
insert into gsw
select 10001, 112, '上海', 100 union all
select 10001, 112, '北京', 100 union all
select 10002, 114, '上海', 100 union all
select 10002, 115, '上海', 100 union all
select 10002, 116, '上海', 100


with t as
(select distinct b1.OrderID,b1.wareID
 from gsw b1 inner join gsw b2
 on b1.OrderID=b2.OrderID and b1.wareID<>b2.wareID
)
select a.OrderID,a.money
from gsw a left join t b 
on a.OrderID=b.OrderID and a.wareID=b.wareID
where b.OrderID is null and b.wareID is null
union all
select a.OrderID,sum(a.money) 'money'
from gsw a inner join t b 
on a.OrderID=b.OrderID and a.wareID=b.wareID
group by a.OrderID

OrderID     money
----------- -----------
10001       100
10001       100
10002       300

(3 row(s) affected)

[Quote=引用 7 楼 的回复:]
如果有两条orderid相同但是wareid不同的数据您这样是正确的，但是如果有三条则会重复计算。比如
10002 114 上海 100
10002 115 上海 100
10002 116 上海 100
则最后数据是600.即最终数据=正确数据*（记录数-1），还请您帮忙查看，谢谢。
[/Quote]

[lingxiao200]

select orderID,sum(money) from table where orderid=orderid and wareID<>wareID

不晓得对不

[让你望见影子的墙]

select OrderID,wareid,money=money+isnull((select sum(money) from tb where orderid=t.orderid and wareid<>t.wareid),0)
from tb t

[裸奔在上海]

2楼正确，学习了

[wujianfeng32]

and wareID<>wareID

想想都不行。。。

[gswuliu]

如果有两条orderid相同但是wareid不同的数据您这样是正确的，但是如果有三条则会重复计算。比如
10002 114 上海 100
10002 115 上海 100
10002 116 上海 100
则最后数据是600.即最终数据=正确数据*（记录数-1），还请您帮忙查看，谢谢。

[Quote=引用 2 楼 的回复:]

SQL code

create table gsw
(OrderID int, wareID int,
city varchar(6), money int)

insert into gsw
select 10001, 112, '上海', 100 union all
select 10001, 112, '北京', 100 union all
select 10002, 11……
[/Quote]

[风飘扬]

顶2楼

[唐诗三百首]

OrderID     money
----------- -----------
10001       100
10001       100
10002       200

(3 row(s) affected)

[唐诗三百首]

create table gsw
(OrderID int, wareID int, 
 city varchar(6), money int)   
 
insert into gsw
select 10001, 112, '上海', 100 union all
select 10001, 112, '北京', 100 union all
select 10002, 114, '上海', 100 union all
select 10002, 115, '上海', 100  

with t as
(select b1.OrderID,b1.wareID
 from gsw b1 inner join gsw b2
 on b1.OrderID=b2.OrderID and b1.wareID<>b2.wareID
)
select a.OrderID,a.money
from gsw a left join t b 
on a.OrderID=b.OrderID and a.wareID=b.wareID
where b.OrderID is null and b.wareID is null
union all
select a.OrderID,sum(a.money) 'money'
from gsw a inner join t b 
on a.OrderID=b.OrderID and a.wareID=b.wareID
group by a.OrderID


OrderID     money
----------- -----------
10001       100
10001       100
10002       200

(3 row(s) affected)

[wxwlll]

反正肯定不能用Group by.

其次在连接操作时，要注意同一个表中的不同记录的同一属性之比较时，首先应对此基本表用两个别名。然后用这两个别名分别引用此属性，去比较！

[十三门徒]

with tb as (select distinct orderid,wareid,money from gsw )

select B.orderid,c.money from(select orderid from tb group by orderid having count(orderid)=1) b
join gsw  c on b.orderid=c.orderid
union all
select orderid,sum(money) from tb a group by orderid having count(wareID) >1