Select Author.author_aid,Author.author_name,(Select SUM(amount) from Opus Where Opus.author_id=Author.author_aid And Convert(Varchar(7),Opus.pubdate,120)='2000-10') As 稿费总和,(Select IsNull(Count(*),0) from Opus Where Opus.author_id=Author.author_aid And Convert(Varchar(7),Opus.pubdate,120)='2000-10') As 稿件数
from Author
Select
A.*,
(Select SUM(稿费总和) from 表2 Where 通讯员编号=A.通讯员编号 And Convert(Varchar(7),见报日期,120)='2000-10') As 稿费总和,
(Select IsNull(Count(*),0) from 表2 Where 通讯员编号=A.通讯员编号 And Convert(Varchar(7),见报日期,120)='2000-10') As 稿件数
from 表1 A
--建立测试环境
Create Table 表(通讯员编号 varchar(10),稿费 int)
--插入数据
insert into 表
select '001',100 union all
select '002',200 union all
select '003',300 union all
select '001',100 union all
select '002',200 union all
select '003','300'
select * from 表
--测试语句
select 通讯员编号, 稿费总和 = Sum(稿费), 稿件数 = count(*)
from 表
group by 通讯员编号