求一语句!!

[koukoujiayi]

请教大虾!!非常感谢!!
表ITEM
CALL_NO IN_DATE
A00001 2005
A00001 2006
A00001 2006
A00001 2006
A00001 2007
A00001 2007
A00001 2008
A00001 2008
A00001 2009
A00002 2006
A00002 2006
A00002 2007
A00002 2008
A00002 2009
A00003 2006
A00003 2007
A00003 2007
A00004 2005
A00004 2005
A00005 2009
A00005 2009
B00001
C00001
............

输入2007,想获得

类 2007(种数) 2007(数量) 截至上年总的(种数) 截至上年总的(数量) 截至当前年总的(种数) 截至当前年总的(数量)
A 3 5 4 9 5 21
B.......
C.......

注： 截至上年是指输入的2007-1年；截至当前年就是指本年度2009年

[子陌红尘]

create table ITEM(CALL_NO varchar(10),IN_DATE int)
insert into ITEM select 'A00001',2005 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2007 
insert into ITEM select 'A00001',2007 
insert into ITEM select 'A00001',2008 
insert into ITEM select 'A00001',2008 
insert into ITEM select 'A00001',2009 
insert into ITEM select 'A00002',2006 
insert into ITEM select 'A00002',2006 
insert into ITEM select 'A00002',2007 
insert into ITEM select 'A00002',2008 
insert into ITEM select 'A00002',2009 
insert into ITEM select 'A00003',2006 
insert into ITEM select 'A00003',2007 
insert into ITEM select 'A00003',2007 
insert into ITEM select 'A00004',2005 
insert into ITEM select 'A00004',2005 
insert into ITEM select 'A00005',2009 
insert into ITEM select 'A00005',2009 
go

declare @year int
set @year=2007

select 
    left(CALL_NO,1),
    count(distinct case IN_DATE when @year then CALL_NO end) ,
    sum(case IN_DATE when @year then 1 else 0 end),
    count(distinct case IN_DATE when @year-1 then CALL_NO end),
    sum(case IN_DATE when @year-1 then 1 else 0 end),
    count(distinct case IN_DATE when year(getdate()) then CALL_NO end),
    sum(case IN_DATE when year(getdate()) then 1 else 0 end)
from
    ITEM
group by
    left(CALL_NO,1)
go

drop table ITEM
go

[lg3605119]

create table ITEM(CALL_NO varchar(10),IN_DATE int)
insert into ITEM select 'A00001',2005 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2007 
insert into ITEM select 'A00001',2007 
insert into ITEM select 'A00001',2008 
insert into ITEM select 'A00001',2008 
insert into ITEM select 'A00001',2009 
insert into ITEM select 'A00002',2006 
insert into ITEM select 'A00002',2006 
insert into ITEM select 'A00002',2007 
insert into ITEM select 'A00002',2008 
insert into ITEM select 'A00002',2009 
insert into ITEM select 'A00003',2006 
insert into ITEM select 'A00003',2007 
insert into ITEM select 'A00003',2007 
insert into ITEM select 'A00004',2005 
insert into ITEM select 'A00004',2005 
insert into ITEM select 'A00005',2009 
insert into ITEM select 'A00005',2009 
go

declare @year int
set @year=2007

select 
    left(CALL_NO,1),
    count(distinct case IN_DATE when @year then CALL_NO end) ,
    sum(case IN_DATE when @year then 1 else 0 end),
    count(distinct case  when IN_DATE<=@year-1 then CALL_NO end),
    sum(case when IN_DATE <=@year-1 then 1 else 0 end),
    count(distinct case  when IN_DATE<=year(getdate()) then CALL_NO end),
    sum(case when IN_DATE <= year(getdate()) then 1 else 0 end)
from
    ITEM
group by
    left(CALL_NO,1)
go

/*
A	3	5	4	9	5	21
*/
drop table ITEM
go

[koukoujiayi]

谢谢楼上诸位!!
3楼大虾的有点问题哦!!
即如果现在是2010年,而表中无2010年记录,则最后两列均为零!!
望指教!!谢谢!!

[koukoujiayi]

[Quote=引用 1 楼 liangCK 的回复:]
种数,数量是什么?
[/Quote]
种数:是相同的CALL_NO记录
数量:是CALL_NO第一个字母相同的记录

[子陌红尘]

select 
    left(CALL_NO,1),
    count(distinct case IN_DATE when @year then CALL_NO end) ,
    sum(case IN_DATE when @year then 1 else 0 end),
    count(distinct case IN_DATE when @year-1 then CALL_NO end),
    sum(case IN_DATE when @year-1 then 1 else 0 end),
    isnull(count(distinct case IN_DATE when year(getdate()) then CALL_NO end),0),
    sum(case IN_DATE when year(getdate()) then 1 else 0 end)
from
    ITEM
group by
    left(CALL_NO,1)

[koukoujiayi]

[Quote=引用 7 楼 libin_ftsafe 的回复:]
SQL codeselect
left(CALL_NO,1),
count(distinct case IN_DATE when @year then CALL_NO end) ,
sum(case IN_DATE when @year then 1 else 0 end),
count(distinct case IN_DATE when @year-1 then CALL_NO end),
sum(case IN_DATE when @year-1 then 1 else 0 end),
isnull(count(distinct case IN_DATE when year(getdate()) then CALL_NO end),0),
sum(case IN_DATE when year(getdate(…
[/Quote]
将日期设置为2010,最后两列还是为零!!
望指教!!谢谢!!

[feixianxxx]

create table ITEM(CALL_NO varchar(10),IN_DATE int)
insert into ITEM select 'A00001',2005 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2007 
insert into ITEM select 'A00001',2007 
insert into ITEM select 'A00001',2008 
insert into ITEM select 'A00001',2008 
insert into ITEM select 'A00001',2009 
insert into ITEM select 'A00002',2006 
insert into ITEM select 'A00002',2006 
insert into ITEM select 'A00002',2007 
insert into ITEM select 'A00002',2008 
insert into ITEM select 'A00002',2009 
insert into ITEM select 'A00003',2006 
insert into ITEM select 'A00003',2007 
insert into ITEM select 'A00003',2007 
insert into ITEM select 'A00004',2005 
insert into ITEM select 'A00004',2005 
insert into ITEM select 'A00005',2009 
insert into ITEM select 'A00005',2009 
insert into ITEM select 'B00001',2008
insert into ITEM select 'B00002',2007
insert into ITEM select 'B00002',2008
insert into ITEM select 'C00001',2007
go

declare @year int
set @year=2010

select 
     left(CALL_NO,1)as 种类,
     count(distinct case IN_DATE when @year then CALL_NO end) as 种数,
     sum(case in_date when @year then 1 else 0 end)as 的数量,
     count(distinct case when  IN_DATE<@year then call_no end) as 截止上年的种数,
     sum(case  when in_date <@year then 1 else 0 end ) as 截止上年的数量,
     count (distinct case  when in_date <=year(getdate()) then call_no end) AS 截至当前年总的种树,
     sum(case when IN_DATE <= year(getdate()) then 1 else 0 end)  截至当前年总的数量  
from
    ITEM
group by
    left(CALL_NO,1)

结果：
A	0	0	5	21	5	21
B	0	0	2	3	2	3
C	0	0	1	1	1	1

[hwhunter]

table ITEM(CALL_NO varchar(10),IN_DATE int)
insert into ITEM select 'A00001',2005
insert into ITEM select 'A00001',2006
insert into ITEM select 'A00001',2006
insert into ITEM select 'A00001',2006
insert into ITEM select 'A00001',2007
insert into ITEM select 'A00001',2007
insert into ITEM select 'A00001',2008
insert into ITEM select 'A00001',2008
insert into ITEM select 'A00001',2009
insert into ITEM select 'A00002',2006
insert into ITEM select 'A00002',2006
insert into ITEM select 'A00002',2007
insert into ITEM select 'A00002',2008
insert into ITEM select 'A00002',2009
insert into ITEM select 'A00003',2006
insert into ITEM select 'A00003',2007
insert into ITEM select 'A00003',2007
insert into ITEM select 'A00004',2005
insert into ITEM select 'A00004',2005
insert into ITEM select 'A00005',2009
insert into ITEM select 'A00005',2009
go

declare @year int

[koukoujiayi]

9楼结果完全正确,谢谢!!
问题是速度非常慢!!在sql查询分析器中测试12秒种左右,(数据量目前是10多万条)
这个速度可能不行!!!!
有没有可改进的地方??

[nalnait]

[Quote=引用 11 楼 koukoujiayi 的回复:]
9楼结果完全正确,谢谢!!
问题是速度非常慢!!在sql查询分析器中测试12秒种左右,(数据量目前是10多万条)
这个速度可能不行!!!!
有没有可改进的地方??
[/Quote]
钻钻的很好啊

[koukoujiayi]

钻钻的很好啊
[/Quote]
结果不对哦!!

[feixianxxx]

[Quote=引用 13 楼 koukoujiayi 的回复:]
钻钻的很好啊
[/Quote]
结果不对哦!!
[/Quote]

结果不对么？

[koukoujiayi]

[Quote=引用 14 楼 feixianxxx 的回复:]
结果不对么？
[/Quote]
你提供的结果是对的!!但速度太慢了!!有没有其他方法??

[--小F--]

进来仰望大侠

[cch1010]

学习

[usher_gml]

全是高手...

[koukoujiayi]

非常感谢楼上各位!!
测试了17楼钻钻的代码!速度和9楼一样,12秒左右!!
只好放弃了,现在分三次统计,然后合并,因为最多26个字母,26条记录,
这样一来分开统计,速度是可接受的5秒左右,就是代码多了点!!
再次感谢各位!!加100分!!

[feixianxxx]

慢估计是慢在统计总和 ~~ 具体只能等高手啦。。。。

[子陌红尘]

明白楼主的意图了，后面四列都是求截止某个年份，也就是把之前的年份都统计上，修改一下：

create table ITEM(CALL_NO varchar(10),IN_DATE int)
insert into ITEM select 'A00001',2005 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2006 
insert into ITEM select 'A00001',2007 
insert into ITEM select 'A00001',2007 
insert into ITEM select 'A00001',2008 
insert into ITEM select 'A00001',2008 
insert into ITEM select 'A00001',2009 
insert into ITEM select 'A00002',2006 
insert into ITEM select 'A00002',2006 
insert into ITEM select 'A00002',2007 
insert into ITEM select 'A00002',2008 
insert into ITEM select 'A00002',2009 
insert into ITEM select 'A00003',2006 
insert into ITEM select 'A00003',2007 
insert into ITEM select 'A00003',2007 
insert into ITEM select 'A00004',2005 
insert into ITEM select 'A00004',2005 
insert into ITEM select 'A00005',2009 
insert into ITEM select 'A00005',2009 
go

declare @year int
set @year=2007

select 
    left(CALL_NO,1) as 类型,
    count(distinct case IN_DATE when @year then CALL_NO end) as [2007(种数)],
    sum(case IN_DATE when @year then 1 else 0 end) as [2007(数量)],
    count(distinct case when IN_DATE<@year then CALL_NO end) as [截至上年总的(种数)],
    sum(case when IN_DATE<@year-1 then 1 else 0 end) as [截至上年总的(数量)],
    count(distinct case when IN_DATE<=year(getdate()) then CALL_NO end) as [截至当前年总的(种数)],
    sum(case when IN_DATE<=year(getdate()) then 1 else 0 end) as [截至当前年总的(数量)]
from
    ITEM
group by
    left(CALL_NO,1)
go

/*
类型   2007(种数)    2007(数量)    截至上年总的(种数)  截至上年总的(数量)  截至当前年总的(种数) 截至当前年总的(数量) 
---- ----------- ----------- ----------- ----------- ----------- ----------- 
A    3           5           4           3           5           21
*/

drop table ITEM
go

[sysuzzj]

create procedure select_year @year int
as
select
left(CALL_NO,1) as 类型,
count(distinct case IN_DATE when @year then CALL_NO end) as [2007(种数)],
sum(case IN_DATE when @year then 1 else 0 end) as [2007(数量)],
count(distinct case when IN_DATE<@year then CALL_NO end) as [截至上年总的(种数)],
sum(case when IN_DATE<@year-1 then 1 else 0 end) as [截至上年总的(数量)],
count(distinct case when IN_DATE<=year(getdate()) then CALL_NO end) as [截至当前年总的(种数)],
sum(case when IN_DATE<=year(getdate()) then 1 else 0 end) as [截至当前年总的(数量)]
from
ITEM
group by
left(CALL_NO,1)
go
exec select_year 2007