USE [huguang]
GO
/****** 对象: Table [dbo].[test103] 脚本日期: 06/16/2009 15:44:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test103](
[month_id] [int] NULL,
[displayvalue] [decimal](18, 2) NULL
) ON [PRIMARY]
select * from test103
go
insert into test103
select 200001,22.22
union all
select 199901,22.22
union all
select 200002,22.02
union all
select 199902,22.02
union all
select 200003,22.03
union all
select 199903,22.03
union all
select 200004,22.04
union all
select 199904,22.04
union all
select 200005,22.05
go
select * from test103
gomonth_id displayvalue
200001 22.22
199901 22.22
200002 22.02
199902 22.02
200003 22.03
199903 22.03
200004 22.04
199904 22.04
200005 22.05select c.month_id as [c.month_id],p.displayvalue as [p.displayvalue],c.displayvalue as [c.displayvalue],(c.displayvalue/p.displayvalue-1)*100 as [增长%]
from test103 c,test103 p where p.month_id=199901 and c.month_id=200001c.month_id p.displayvalue c.displayvalue [增长%]
200001 22.22 22.22 0.0000000000000000改成如下的:
select c.month_id as [c.month_id],p.displayvalue as [p.displayvalue],c.displayvalue as [c.displayvalue],(c.displayvalue/p.displayvalue-1)*100 as [增长%]
from test103 c,test103 p where p.month_id=199905 and c.month_id=200005
不显示值,如何改成是显示的
c.month_id p.displayvalue c.displayvalue [增长%]
200005 - 22.05 -
GO
/****** 对象: Table [dbo].[test103] 脚本日期: 06/16/2009 15:44:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test103](
[month_id] [int] NULL,
[displayvalue] [decimal](18, 2) NULL
) ON [PRIMARY]
select * from test103
go
insert into test103
select 200001,22.22
union all
select 199901,22.22
union all
select 200002,22.02
union all
select 199902,22.02
union all
select 200003,22.03
union all
select 199903,22.03
union all
select 200004,22.04
union all
select 199904,22.04
union all
select 200005,22.05
go
select * from test103
gomonth_id displayvalue
200001 22.22
199901 22.22
200002 22.02
199902 22.02
200003 22.03
199903 22.03
200004 22.04
199904 22.04
200005 22.05select c.month_id as [c.month_id],p.displayvalue as [p.displayvalue],c.displayvalue as [c.displayvalue],(c.displayvalue/p.displayvalue-1)*100 as [增长%]
from test103 c,test103 p where p.month_id=199901 and c.month_id=200001c.month_id p.displayvalue c.displayvalue [增长%]
200001 22.22 22.22 0.0000000000000000改成如下的:
select c.month_id as [c.month_id],p.displayvalue as [p.displayvalue],c.displayvalue as [c.displayvalue],(c.displayvalue/p.displayvalue-1)*100 as [增长%]
from test103 c,test103 p where p.month_id=199905 and c.month_id=200005
不显示值,如何改成是显示的
c.month_id p.displayvalue c.displayvalue [增长%]
200005 - 22.05 -
select c.month_id as [c.month_id],ISNULL(p.displayvalue,0) as [p.displayvalue],c.displayvalue as [c.displayvalue],CASE WHEN p.displayvalue=0 THEN 0 ELSE (c.displayvalue/p.displayvalue-1)*100 END as [增长%]
from test103 c,test103 p where p.month_id=199905 and c.month_id=200005
CASE WHEN p.displayvalue=0 and c.displayvalue<>0 THEN -c.displayvalue ELSE (c.displayvalue/p.displayvalue-1)*100
when p.displayvalue<>0 and c.displayvalue=0 then 0 else (c.displayvalue/p.displayvalue-1)*100
when p.displayvalue=0 and c.displayvalue=0 then 0 else (c.displayvalue/p.displayvalue-1)*100
END as [增长%]
from test103 c,test103 p where p.month_id=199905 and c.month_id=200005
set @a1=199905,@a2=200005
select c.month_id as [c.month_id],ISNULL(p.displayvalue,0) as [p.displayvalue],ISNULL(c.displayvalue,0) as [c.displayvalue],
CASE WHEN p.displayvalue=0 and c.displayvalue<>0 THEN -c.displayvalue ELSE (c.displayvalue/p.displayvalue-1)*100
when p.displayvalue<>0 and c.displayvalue=0 then 0 else (c.displayvalue/p.displayvalue-1)*100
when p.displayvalue=0 and c.displayvalue=0 then 0 else (c.displayvalue/p.displayvalue-1)*100
END as [增长%]
from test103 c,test103 p where p.month_id=@a1 and c.month_id=@a2
insert into test103(month_id,displayvalue) values(200006,22.06)
go
insert into test103(month_id) values(199906)
===>
200006 22.06
199906 NULLisnull 是对有年份的,并且他的值是null时起做用.现在是表中没有199905这条记录.isnull找不到记录,还如何去判断它的值呀
你上面的
200006和199906年就可以用isnull判断了,它首先能找到19996然后一个displayvalue的值为null就能将0赋给它了.
现在的总是是表中没有199905这条记录.
[month_id] [int] NULL,
[displayvalue] [decimal](18, 2) NULL
) ON [PRIMARY] go
insert into test103
select 200001,22.22
union all
select 199901,22.22
union all
select 200002,22.02
union all
select 199902,22.02
union all
select 200003,22.03
union all
select 199903,22.03
union all
select 200004,22.04
union all
select 199904,22.04
union all
select 200005,22.05 --DROP TABLE TEST103
select * from test103 WHERE month_id=200005 SELECT [c.month_id],[P.displayvalue],[C.displayvalue],CASE WHEN [P.displayvalue]=0 THEN 0 ELSE ([C.displayvalue]/[P.displayvalue]-1)*100 END as [增长%]
FROM
(select c.month_id as [c.month_id],
CASE WHEN NOT EXISTS(SELECT displayvalue FROM TEST103 WHERE month_id=199905) THEN 0 ELSE displayvalue END AS [P.displayvalue],
(SELECT displayvalue FROM TEST103 WHERE month_id=200005 )AS [C.displayvalue]
from test103 C WHERE C.month_id=200005)AS T
/*
c.month_id P.displayvalue C.displayvalue 增长%
----------- -------------------- -------------------- ----------------------------------------
200005 .00 22.05 .0000000000000000(所影响的行数为 1 行)
*/