有两个表,BalanceRecord,costItem
在BalanceRecord中一个列是costItem中的外键,在BalanceRecord中有21条数据,在costItem中71条数据,我的查询如下:
select c.RecordId from costItem c inner join BalanceRecord b on c.RecordId=b.RecordId
结果得到的是71条数据,我想得到的只是21条数据,我要怎么来查询,因为后面那个表中71条数据中只有一部分RecordId在第一个表中,像我这样的查询的话,那么在结果中有71条数据,但是有好多的数据都是重复的,如果我加上一个distinct的话,那么
select c.RecordId from costItem c inner join BalanceRecord b on c.RecordId=b.RecordId
得到的倒是21条数据,这21条数据中我想把NAME这个字段中值相同的那几条数据中count 字段的值加起来,要怎么来做?
表:BalanceRecord字段
id ,recordid
1 1
2 2
3 3
4 4
表:costItem字段
id ,recordid,name,count
1 1 a1 25
2 1 a1 25
3 2 a2 29
4 2 a2 29
5 3 a3 27
6 4 a4 23
7 5 a5 26
8 6 a6 26
最后的结果是把recordid相同的值加起来,如recordId为1那么最后的结果就是50
在BalanceRecord中一个列是costItem中的外键,在BalanceRecord中有21条数据,在costItem中71条数据,我的查询如下:
select c.RecordId from costItem c inner join BalanceRecord b on c.RecordId=b.RecordId
结果得到的是71条数据,我想得到的只是21条数据,我要怎么来查询,因为后面那个表中71条数据中只有一部分RecordId在第一个表中,像我这样的查询的话,那么在结果中有71条数据,但是有好多的数据都是重复的,如果我加上一个distinct的话,那么
select c.RecordId from costItem c inner join BalanceRecord b on c.RecordId=b.RecordId
得到的倒是21条数据,这21条数据中我想把NAME这个字段中值相同的那几条数据中count 字段的值加起来,要怎么来做?
表:BalanceRecord字段
id ,recordid
1 1
2 2
3 3
4 4
表:costItem字段
id ,recordid,name,count
1 1 a1 25
2 1 a1 25
3 2 a2 29
4 2 a2 29
5 3 a3 27
6 4 a4 23
7 5 a5 26
8 6 a6 26
最后的结果是把recordid相同的值加起来,如recordId为1那么最后的结果就是50
这样?
from BalanceRecord m , costItem n
where m.recordid = n.recordid
group by m.id,m.recordid
select A.recordid,B.name,B.[count]
from BalanceRecord as A
left join
(select recordid,name,sum([count]) as [count]
from costItem
group by recordid,name) as B
on A.recordid=Brecordid
a.*b.name,b.count
from
BalanceRecord a
inner join
(select id,recordid,name,sum(count) as [count] group by id,recordid,name)b
on
a.id=b.id and a.recordid=b.recordid
insert into BalanceRecord values(1 ,1)
insert into BalanceRecord values(2 ,2)
insert into BalanceRecord values(3 ,3)
insert into BalanceRecord values(4 ,4)
create table costItem(id int,recordid int,name varchar(10),[count] int)
insert into costItem values(1, 1 , 'a1' , 25 )
insert into costItem values(2, 1 , 'a1' , 25 )
insert into costItem values(3, 2 , 'a2' , 29 )
insert into costItem values(4, 2 , 'a2' , 29 )
insert into costItem values(5, 3 , 'a3' , 27 )
insert into costItem values(6, 4 , 'a4' , 23 )
insert into costItem values(7, 5 , 'a5' , 26 )
insert into costItem values(8, 6 , 'a6' , 26 )
goselect m.id,m.recordid,sum(n.[count]) [count]
from BalanceRecord m , costItem n
where m.recordid = n.recordid
group by m.id,m.recordiddrop table BalanceRecord,costItem/*
id recordid count
----------- ----------- -----------
1 1 50
2 2 58
3 3 27
4 4 23(所影响的行数为 4 行)*/