求一语句!!

2009-06-02 05:12:00 369 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【JS】! !! !!!区别 !是逻辑与运算，并且可以与任何变量进行逻辑与将其转化为布尔值，“!!”则是逻辑与运算的取反运算，尤其后者在判断类型时代码简洁高效，省去了多次判断null、undefined和空字符串的冗余代码。 !!常常用来做类型判断，在第一步!（变量）之后再做逻辑取反运算，在js中新手常常会写这样臃肿的代码：判断变量a为非空，未定义或者非空串才能执行方法体的内容 !!!没有什么意义，和一个感叹号的效果是一样的阅读详情

请教大虾!!非常感谢!!
表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年

...全文

【ISAR成像】基于Matlab的飞机点目标模型仿真与图像优化本文详细介绍了如何使用Matlab进行ISAR成像仿真，重点讲解了从构建飞机点目标模型到生成清晰图像的完整流程。通过模拟雷达回波、距离压缩、运动补偿及方位压缩等核心信号处理步骤，并结合相位梯度自聚焦等优化技巧，有效提升了ISAR图像的清晰度与分辨率。文章还分享了雷达参数调优、加窗处理等实战经验，为雷达信号处理与目标识别研究提供了实用指导。阅读详情

koukoujiayi

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

sysuzzj 2009-06-03

打赏
举报

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

子陌红尘 2009-06-03

打赏
举报

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



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

feixianxxx 2009-06-03

打赏
举报

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

koukoujiayi 2009-06-03

打赏
举报

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

usher_gml 2009-06-03

打赏
举报

全是高手...

cch1010 2009-06-03

打赏
举报

学习

--小F-- 2009-06-03

打赏
举报

进来仰望大侠

koukoujiayi 2009-06-02

打赏
举报

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

feixianxxx 2009-06-02

打赏
举报

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

结果不对么？

koukoujiayi 2009-06-02

打赏
举报

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

nalnait 2009-06-02

打赏
举报

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

koukoujiayi 2009-06-02

打赏
举报

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

hwhunter 2009-06-02

打赏
举报

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

feixianxxx 2009-06-02

打赏
举报

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

koukoujiayi 2009-06-02

打赏
举报

[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,最后两列还是为零!!
望指教!!谢谢!!

子陌红尘 2009-06-02

打赏
举报

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 2009-06-02

打赏
举报

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

koukoujiayi 2009-06-02

打赏
举报

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

lg3605119 2009-06-02

打赏
举报



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

子陌红尘 2009-06-02

打赏
举报

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

加载更多回复（2）

求一语句!!

相关推荐