我现在有两个表
SA:number date
1 2010-1-4
2 2010-2-8
3 2010-5-9
4 2008-5-6
5 2009-6-5
6 2008-4-6
7 2008-4-6
…… ……
JA:number date
1 2010-3-4
2 2010-4-8
3 2010-5-9
4 2008-7-6
5 2009-8-5
6 2008-9-6
7 2008-6-6
…… ……
上面一个是受案表,一个是结案表。
现在我要统计每一年的每个月份,受案,结案各多少,该怎么写查询语句呢?
要实现的表如下:
date SAcount JAcount
2010-1 1 0
2010-2 1 0
2010-3 0 1
…… …… ……
SA:number date
1 2010-1-4
2 2010-2-8
3 2010-5-9
4 2008-5-6
5 2009-6-5
6 2008-4-6
7 2008-4-6
…… ……
JA:number date
1 2010-3-4
2 2010-4-8
3 2010-5-9
4 2008-7-6
5 2009-8-5
6 2008-9-6
7 2008-6-6
…… ……
上面一个是受案表,一个是结案表。
现在我要统计每一年的每个月份,受案,结案各多少,该怎么写查询语句呢?
要实现的表如下:
date SAcount JAcount
2010-1 1 0
2010-2 1 0
2010-3 0 1
…… …… ……
sum(case when t = 1 then 1 else 0 end) as sacount,
sum(case when t = 2 then 1 else 0 end) as jacount
from (select t=1,[date] from sa
union all
select 2,[date] from ja) a
group by convert(char(6),[date],120)
convert(varchar(7),[date],120),
sum(case id when 1 then 1 else 0 end) as sacount,
sum(case id when 2 then 1 else 0 end) as jacount
from
(select 1 as id,[date] from sa
union all
select 2 as id,[date] from ja)t
group by
convert(varchar(7),[date],120)
SELECT
substring(CONVERT(char(10), JA.[date], 23) ,1,7) as [date]
,SUM(SA.number) as SAcount
,SUM(JA.number) as JAcount
from SA
inner join JA on substring(CONVERT(char(10), SA.[date], 23) ,1,7)=substring(CONVERT(char(10), JA.[date], 23) ,1,7)
group by substring(CONVERT(char(10), JA.[date], 23) ,1,7)
--测试数据
if object_id('sa') is not null drop table sa
create table sa ([number] int,[date] datetime)
go
insert into sa
SELECT 1,'2010-1-4' UNION all
SELECT 2,'2010-2-8' UNION ALL
SELECT 3,'2010-5-9' UNION ALL
SELECT 4,'2008-5-6' UNION ALL
SELECT 5,'2009-6-5' UNION ALL
SELECT 6,'2008-4-6' UNION ALL
SELECT 7,'2008-4-6'if object_id('ja') is not null drop table ja
create table ja ([number] int,[date] datetime)
go
insert into ja
SELECT 1,'2010-3-4' UNION all
SELECT 2,'2010-4-8' UNION ALL
SELECT 3,'2010-5-9' UNION all
SELECT 4,'2008-7-6' UNION ALL
SELECT 5,'2009-8-5' UNION all
SELECT 6,'2008-9-6' UNION ALL
SELECT 7,'2008-6-6'--查询
select convert(varchar(7),[date],120) AS date,
sum(case when t = 1 then 1 else 0 end) as sacount,
sum(case when t = 2 then 1 else 0 end) as jacount
from (select t=1,[date] from sa
union all
select 2,[date] from ja) a
group by convert(varchar(7),[date],120)--结果
/*
date SAcount JAcount
--------------------------
2008-04 2 0
2008-05 1 0
2008-06 0 1
2008-07 0 1
2008-09 0 1
2009-06 1 0
2009-08 0 1
2010-01 1 0
2010-02 1 0
2010-03 0 1
2010-04 0 1
2010-05 1 1
*/
declare @year varchar(10)
set @year='2010'
select date,sacount=(select count(*) from SA where datediff(mm,date,a.date+'-01')=0),
JAcount=(select count(*) from JA where datediff(mm,date,a.date+'-01')=0)
(select date=@year+'-'+ltrim(number) from master..spt_values where type='p' and number between 1 and 12)a
--掉了from,
declare @year varchar(10)
set @year='2010'
select date,sacount=(select count(*) from SA where datediff(mm,date,a.date+'-01')=0),
JAcount=(select count(*) from JA where datediff(mm,date,a.date+'-01')=0)
from
(select date=@year+'-'+ltrim(number) from master..spt_values where type='p' and number between 1 and 12)a
if object_id('[SA]') is not null drop table [SA]
go
create table [SA]([number] int,[date] datetime)
insert [SA]
select 1,'2010-1-4' union all
select 2,'2010-2-8' union all
select 3,'2010-5-9' union all
select 4,'2008-5-6' union all
select 5,'2009-6-5' union all
select 6,'2008-4-6' union all
select 7,'2008-4-6'
if object_id('[JA]') is not null drop table [JA]
go
create table [JA]([number] int,[date] datetime)
insert [JA]
select 1,'2010-3-4' union all
select 2,'2010-4-8' union all
select 3,'2010-5-9' union all
select 4,'2008-7-6' union all
select 5,'2009-8-5' union all
select 6,'2008-9-6' union all
select 7,'2008-6-6'
---查询---
select isnull(a.[date],b.[date]) as [date],isnull(a.SAcount,0) SAcount,isnull(b.JAcount,0) JAcount
from
(
select convert(varchar(7),[date],120) as [date],count(1) as SAcount from SA group by convert(varchar(7),[date],120)
) a
full join
(
select convert(varchar(7),[date],120) as [date],count(1) as JAcount from JA group by convert(varchar(7),[date],120)
) b
on a.[date]=b.[date]---结果---
date SAcount JAcount
------- ----------- -----------
2008-04 2 0
2008-05 1 0
2008-06 0 1
2008-07 0 1
2008-09 0 1
2009-06 1 0
2009-08 0 1
2010-01 1 0
2010-02 1 0
2010-03 0 1
2010-04 0 1
2010-05 1 1(12 行受影响)
create table SA
(
[number] int,
[date] smalldatetime
)insert SA
select 1,'2010-1-4' union all
select 2,'2010-2-8' union all
select 3,'2010-5-9' union all
select 4,'2008-5-6' union all
select 5,'2009-6-5' union all
select 6,'2008-4-6' union all
select 7,'2008-4-6'create table JA
(
[number] int,
[date] smalldatetime
)insert JA
select 1,'2010-3-4' union all
select 2,'2010-4-8' union all
select 3,'2010-5-9' union all
select 4,'2008-7-6' union all
select 5,'2009-8-5' union all
select 6,'2008-9-6' union all
select 7,'2008-6-6'select [Date]=convert(varchar(7),date,120),SAcount=count(S),JAcount=count(J)
from
(
select [date],S=[number],J=null from SA
union all
select [date],S=null,J=[number] from JA
) A
group by convert(varchar(7),date,120)
Date SAcount JAcount
------- ----------- -----------
2008-04 2 0
2008-05 1 0
2008-06 0 1
2008-07 0 1
2008-09 0 1
2009-06 1 0
2009-08 0 1
2010-01 1 0
2010-02 1 0
2010-03 0 1
2010-04 0 1
2010-05 1 1
sum(case when sa.number is null then 0 else 1 end) as sanumber,
sum(case when ja.number is null then 0 else 1 end) as janumber
from sa full join ja on CONVERT(char(7),sa.[date],120)=CONVERT(char(7),ja.[date],120)
group by isnull(convert(varchar(7),sa.[date],120),convert(varchar(7),ja.[date],120))date sanumber janumber
------- ----------- -----------
2008-04 2 0
2008-05 1 0
2008-06 0 1
2008-07 0 1
2008-09 0 1
2009-06 1 0
2009-08 0 1
2010-01 1 0
2010-02 1 0
2010-03 0 1
2010-04 0 1
2010-05 1 1(12 行受影响)
insert [SA]
select 1,'2010-1-4' union all
select 2,'2010-2-8' union all
select 3,'2010-5-9' union all
select 4,'2008-5-6' union all
select 5,'2009-6-5' union all
select 6,'2008-4-6' union all
select 7,'2008-4-6'
create table [JA]([number] int,[date] datetime)
insert [JA]
select 1,'2010-3-4' union all
select 2,'2010-4-8' union all
select 3,'2010-5-9' union all
select 4,'2008-7-6' union all
select 5,'2009-8-5' union all
select 6,'2008-9-6' union all
select 7,'2008-6-6'
goselect isnull(m.date,n.date) date,
isnull(m.SAcount,0) SAcount,
isnull(n.JAcount,0) JAcount
from
(select convert(varchar(7),date,120) date , count(1) SAcount from sa group by convert(varchar(7),date,120)) m
full join
(select convert(varchar(7),date,120) date , count(1) JAcount from ja group by convert(varchar(7),date,120)) n
on m.date = n.date
order by m.datedrop table sa , ja/*
date SAcount JAcount
------- ----------- -----------
2008-04 2 0
2008-05 1 0
2008-06 0 1
2008-07 0 1
2008-09 0 1
2009-06 1 0
2009-08 0 1
2010-01 1 0
2010-02 1 0
2010-03 0 1
2010-04 0 1
2010-05 1 1(所影响的行数为 12 行)
*/