a:
id danid roomname shuliang jiage
1 2 222 1 3
2 23 333 1 5b:
id danid roomname type
23 2 222 23
24 2 222 23
25 23 333 35
a表和b表通过roomname和danid关联,a表里面的id和b表的id都是唯一的
我想实现
a表里面的sum(shuliang *jiage ),但前提是b里面的type<>34SElect isnull(sum(shuliang*jiage),0) as jine from a, b where a.danid =b.danid
and b.roomname =a.roomname
我这样写的问题是b里面有danid 和roomname重复的记录是上面的求和就会多算
请问我该怎么写?
谢了
id danid roomname shuliang jiage
1 2 222 1 3
2 23 333 1 5b:
id danid roomname type
23 2 222 23
24 2 222 23
25 23 333 35
a表和b表通过roomname和danid关联,a表里面的id和b表的id都是唯一的
我想实现
a表里面的sum(shuliang *jiage ),但前提是b里面的type<>34SElect isnull(sum(shuliang*jiage),0) as jine from a, b where a.danid =b.danid
and b.roomname =a.roomname
我这样写的问题是b里面有danid 和roomname重复的记录是上面的求和就会多算
请问我该怎么写?
谢了
insert a select 1,2,222,1,3
union all select 2,23,333,1,5create table b(id int,danid int,roomname int,type int)
insert b select 23,2,222,23
union all select 24,2,222,23
union all select 25,23,333,35select shuliang*jiage from
(select distinct a.danid,a.roomname,a.shuliang,a.jiage from a,b where a.danid=b.danid and a.roomname=b.roomname)p
(select distinct a.danid,a.roomname,a.shuliang,a.jiage from a,b where a.danid=b.danid and a.roomname=b.roomname)p
select sum(isnull(shuliang*jiage,0)) from
(select distinct a.danid,a.roomname,a.shuliang,a.jiage from a,b where a.danid=b.danid and a.roomname=b.roomname and b.type<>34)p
C.danid ,
C.roomname,
isNull(sum(shuliang*jiage),0) As jine
From
(Select Distinct danid,roomname From B Where type<>34) C Inner Join A
On C.danid=A.danid And C.roomname=A.roomname
Group By C.danid,C.roomname
create table a(id int,danid int,roomname int,shuliang int,jiage int)
insert a select 1,2,222,1,3
union all select 2,23,333,1,5
union all select 2,22,333,1,5create table b(id int,danid int,roomname int,type int)
insert b select 23,2,222,23
union all select 24,2,222,23
union all select 25,23,333,35
union all select 25,22,333,34
GO
Select
C.danid ,
C.roomname,
isNull(sum(shuliang*jiage),0) As jine
From
(Select Distinct danid,roomname From B Where type<>34) C Inner Join A
On C.danid=A.danid And C.roomname=A.roomname
Group By C.danid,C.roomname
GO
Drop Table A,B
--Result
/*
danid roomname jine
2 222 3
23 333 5
*/