create table PATRONIZER_INFO (PATRONIZER_ID varchar(10),START_DAY datetime, END_DAY datetime,DELETE_FLAG int,INJION_YEARS int)
insert PATRONIZER_INFO select 'f1' ,'2001/08/07' ,'2008/03/28' ,0 , 1
insert PATRONIZER_INFO select 'f2' ,'2001/08/07' ,'2008/03/28' ,0 , 2 create table SUPPORT_MONEY_INFO(PATRONIZER_ID varchar(10),STUDENT_ID varchar(10),AMOUNT int)
insert SUPPORT_MONEY_INFO select 'f1' , 'F1' ,100
insert SUPPORT_MONEY_INFO select 'f1' , 'F2' , 200
insert SUPPORT_MONEY_INFO select 'f2' , 'F3' , 200 select rtrim(a.INJION_YEARS)+'年' as INJION_YEARS,
count(distinct a.PATRONIZER_ID) total,
count(b.PATRONIZER_ID) PATRONIZER_ID,sum(b.AMOUNT) PATRONIZER_ID
from PATRONIZER_INFO a
join
SUPPORT_MONEY_INFO b
on a.PATRONIZER_ID=b.PATRONIZER_ID
AND a.START_DAY>='20010807' and a.END_DAY<='20080328'
and a.DELETE_FLAG =0
group by a. INJION_YEARSdrop table PATRONIZER_INFO,SUPPORT_MONEY_INFO/*
INJION_YEARS total PATRONIZER_ID PATRONIZER_ID
-------------- ----------- ------------- -------------
1年 1 2 300
2年 1 1 200(2 行受影响)
*/
insert PATRONIZER_INFO select 'f1' ,'2001/08/07' ,'2008/03/28' ,0 , 1
insert PATRONIZER_INFO select 'f2' ,'2001/08/07' ,'2008/03/28' ,0 , 2 create table SUPPORT_MONEY_INFO(PATRONIZER_ID varchar(10),STUDENT_ID varchar(10),AMOUNT int)
insert SUPPORT_MONEY_INFO select 'f1' , 'F1' ,100
insert SUPPORT_MONEY_INFO select 'f1' , 'F2' , 200
insert SUPPORT_MONEY_INFO select 'f2' , 'F3' , 200 select rtrim(a.INJION_YEARS)+'年' as INJION_YEARS,
count(distinct a.PATRONIZER_ID) total,
count(b.PATRONIZER_ID) PATRONIZER_ID,sum(b.AMOUNT) PATRONIZER_ID
from PATRONIZER_INFO a
join
SUPPORT_MONEY_INFO b
on a.PATRONIZER_ID=b.PATRONIZER_ID
AND a.START_DAY>='20010807' and a.END_DAY<='20080328'
and a.DELETE_FLAG =0
group by a. INJION_YEARSdrop table PATRONIZER_INFO,SUPPORT_MONEY_INFO/*
INJION_YEARS total PATRONIZER_ID PATRONIZER_ID
-------------- ----------- ------------- -------------
1年 1 2 300
2年 1 1 200(2 行受影响)
*/
insert PATRONIZER_INFO select 'f1' ,'2001/08/07' ,'2008/03/28' ,0 , 1
insert PATRONIZER_INFO select 'f2' ,'2001/08/07' ,'2008/03/28' ,0 , 1 create table SUPPORT_MONEY_INFO(PATRONIZER_ID varchar(10),STUDENT_ID varchar(10),AMOUNT int)
insert SUPPORT_MONEY_INFO select 'f1' , 'F1' ,100
insert SUPPORT_MONEY_INFO select 'f1' , 'F2' , 200
insert SUPPORT_MONEY_INFO select 'f2' , 'F3' , 200 select rtrim(a.INJION_YEARS)+'年' as INJION_YEARS,
count(distinct a.PATRONIZER_ID) total,
count(b.PATRONIZER_ID) PATRONIZER_ID,sum(b.AMOUNT) PATRONIZER_ID
from PATRONIZER_INFO a
join
SUPPORT_MONEY_INFO b
on a.PATRONIZER_ID=b.PATRONIZER_ID
AND a.START_DAY>='20010807' and a.END_DAY<='20080328'
and a.DELETE_FLAG =0
group by a. INJION_YEARSdrop table PATRONIZER_INFO,SUPPORT_MONEY_INFO/*
INJION_YEARS total PATRONIZER_ID PATRONIZER_ID
-------------- ----------- ------------- -------------
1年 2 3 500(1 行受影响)
*/
f1 2001/08/07 2008/03/28 0 1
f2 2001/08/07 2008/03/28 0 2
b
f1 F1 100
f1 F2 200
f2 F3 200
我要 计算 a的年数排序 PATRONIZER_ID的个数 STUDENT_ID的个数 AMOUNT的合计
以以下排序
1年 个数1 学生数2 金额300
2年 个数1 学生数1 金额200 如果数据是 a f1 2001/08/07 2008/03/28 0 1
f2 2001/08/07 2008/03/28 0 1
b f1 F1 100
f1 F2 200
f2 F3 200
我想要
1年 个数 2 学生数3 金额600
declare @a table(PATRONIZER_ID varchar(10),START_DAY datetime, END_DAY datetime,DELETE_FLAG int,INJION_YEARS int)
insert @a select 'f1' ,'2001/08/07' ,'2008/03/28' ,0 , 1
insert @a select 'f2' ,'2001/08/07' ,'2008/03/28' ,0 , 2 declare @b table (PATRONIZER_ID varchar(10),STUDENT_ID varchar(10),AMOUNT int)
insert @b select 'f1' , 'F1' ,100
insert @b select 'f1' , 'F2' , 200
insert @b select 'f2' , 'F3' , 200 select ltrim(INJION_YEARS)+'年'ID,
'个数'+ltrim(count(a.PATRONIZER_ID))SUM_COUNT,
'学生数'+ltrim(count(b.STUDENT_ID))STUDENT_COUNT,
'金额'+ltrim(sum(AMOUNT)) as AMOUT
from @a a left join @b b
on a.PATRONIZER_ID=b.PATRONIZER_ID
group by INJION_YEARS