--@days 你需要的天数 declare @days int set @days=5 ;with cte as( select top 20 *, (SKJE-FHJE) as result from 你的表 where FHJE>SKJE ) select * from cte where datediff(dd,[DATE],getdate())<@days
哥们 你的问题问的也太模糊了,还打错了字段名 猜测着帮你想了下,要是有点启示,意思一下吧 select top 20 isnull(SKJE,0)-isnull(FHJE,0) from View_XSHTTZH where DHJE>SKJE and 时间字段>convert(varchar(10),dateadd(d,-5,getdate()),120) --得到5天内数据,其它时间段自己改参数。
if object_id('Tempdb..#t') is not null drop table #t create table #t( [KHH] int identity(1,1) not null, [DATE] datetime null, [FHJE] int null, [SKJE] int null ) Insert Into #t select '2014-04-01',1,2 union all select '2014-04-05',3,2 union all select '2014-04-09',3,1 union all select '2014-04-12',4,2 union all select '2014-04-15',6,2 union all select '2014-04-20',7,2 ;with cte as( select *,SKJE-FHJE as result from #t where FHJE>SKJE ) select [KHH],[DATE],[FHJE],[SKJE],[result], (case when datediff(dd,[DATE],getdate())<5 then result else null end ) as '小于5天', (case when datediff(dd,[DATE],getdate()) between 5 and 15 then result else null end ) as '5到15天', (case when datediff(dd,[DATE],getdate())>15 then result else null end ) as '15天以前' from cte ------------------------- --结果(6 行受影响) KHH DATE FHJE SKJE result 小于5天 5到15天 15天以前 ----------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- 2 2014-04-05 00:00:00.000 3 2 -1 NULL NULL -1 3 2014-04-09 00:00:00.000 3 1 -2 NULL -2 NULL 4 2014-04-12 00:00:00.000 4 2 -2 NULL -2 NULL 5 2014-04-15 00:00:00.000 6 2 -4 NULL -4 NULL 6 2014-04-20 00:00:00.000 7 2 -5 -5 NULL NULL(5 行受影响)
好复杂呀,我写的结果有问题,请指导下哪写错了 select top 20 a.khh sum(case when isnull(a.SKJE,0)<isnull(a.FHJE,0) then (isnull(a.FHJE,0)-isnull(a.SKJE,0)) else 0 end) as yszk,b.yszk1, c.yszk2,d.ywysk3 from View_XSHTTZH a left join (select khh,sum(case when isnull(SKJE,0)<isnull(FHJE,0) then (isnull(FHJE,0)-isnull(SKJE,0)) else 0 end) as yszk3 from View_XSHTTZH where date<GETDATE()-15 group by khh) d on a.khh=d.khh left join (select khh,sum(case when isnull(SKJE,0)<isnull(FHJE,0) then (isnull(FHJE,0)-isnull(SKJE,0)) else 0 end) as yszk2 from View_XSHTTZH where date>GETDATE()-15 and date<=GETDATE()-5 group by khh) c on d.khh=c.khh left join (select khh,sum(case when isnull(SKJE,0)<isnull(FHJE,0) then (isnull(FHJE,0)-isnull(SKJE,0)) else 0 end) as yszk1 from View_XSHTTZH where date>GETDATE()-5 and date<=GETDATE() group by khh) b on d.khh=b.khh group by a.khh,b.yszk1,c.yszk2,d.yszk3 order by yszk desc
5天内---是以当前系统日期作比较? 以下举个例子,自己调试一下结果是否正确DECLARE @Dt DATETIME SET @Dt = CONVERT(VARCHAR(10), GETDATE() + 1, 120) SELECT a.khh ,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 5 THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0) ELSE 0 END) AS [5天内] ,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 10 THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0) ELSE 0 END) AS [10天内] ,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 15 THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0) ELSE 0 END) AS [15天内] ,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) >= 15 THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0) ELSE 0 END) AS [15天之前] FROM View_XSHTTZH AS a WHERE ISNULL(a.SKJE, 0) < ISNULL(a.FHJE, 0) AND a.khh IN ( SELECT TOP 20 khh FROM View_XSHTTZH GROUP BY khh ORDER BY SUM(CASE WHEN ISNULL(SKJE, 0) < ISNULL(FHJE, 0) THEN ISNULL(FHJE, 0) - ISNULL(SKJE, 0) ELSE 0 END) DESC ) GROUP BY a.khh
你的问题问的也太模糊了,还打错了字段名
猜测着帮你想了下,要是有点启示,意思一下吧
select top 20 isnull(SKJE,0)-isnull(FHJE,0)
from View_XSHTTZH
where DHJE>SKJE
and 时间字段>convert(varchar(10),dateadd(d,-5,getdate()),120) --得到5天内数据,其它时间段自己改参数。
标题:KHH YSZK YSZK1(5日内) YSZK2(5-15日内) YSZK3 (15天前的)结果: A 10 2 4 4
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
[KHH] int identity(1,1) not null,
[DATE] datetime null,
[FHJE] int null,
[SKJE] int null
)
Insert Into #t
select '2014-04-01',1,2 union all
select '2014-04-05',3,2 union all
select '2014-04-09',3,1 union all
select '2014-04-12',4,2 union all
select '2014-04-15',6,2 union all
select '2014-04-20',7,2
;with cte as(
select *,SKJE-FHJE as result from #t
where FHJE>SKJE
)
select [KHH],[DATE],[FHJE],[SKJE],[result],
(case when datediff(dd,[DATE],getdate())<5 then result else null end ) as '小于5天',
(case when datediff(dd,[DATE],getdate()) between 5 and 15 then result else null end ) as '5到15天',
(case when datediff(dd,[DATE],getdate())>15 then result else null end ) as '15天以前'
from cte
-------------------------
--结果(6 行受影响)
KHH DATE FHJE SKJE result 小于5天 5到15天 15天以前
----------- ----------------------- ----------- ----------- ----------- ----------- ----------- -----------
2 2014-04-05 00:00:00.000 3 2 -1 NULL NULL -1
3 2014-04-09 00:00:00.000 3 1 -2 NULL -2 NULL
4 2014-04-12 00:00:00.000 4 2 -2 NULL -2 NULL
5 2014-04-15 00:00:00.000 6 2 -4 NULL -4 NULL
6 2014-04-20 00:00:00.000 7 2 -5 -5 NULL NULL(5 行受影响)
select top 20 a.khh
sum(case when isnull(a.SKJE,0)<isnull(a.FHJE,0) then (isnull(a.FHJE,0)-isnull(a.SKJE,0)) else 0 end) as yszk,b.yszk1,
c.yszk2,d.ywysk3
from View_XSHTTZH a
left join (select khh,sum(case when isnull(SKJE,0)<isnull(FHJE,0) then (isnull(FHJE,0)-isnull(SKJE,0)) else 0 end) as yszk3 from View_XSHTTZH where date<GETDATE()-15 group by khh) d
on a.khh=d.khh
left join (select khh,sum(case when isnull(SKJE,0)<isnull(FHJE,0) then (isnull(FHJE,0)-isnull(SKJE,0)) else 0 end) as yszk2 from View_XSHTTZH where date>GETDATE()-15 and date<=GETDATE()-5 group by khh) c
on d.khh=c.khh
left join (select khh,sum(case when isnull(SKJE,0)<isnull(FHJE,0) then (isnull(FHJE,0)-isnull(SKJE,0)) else 0 end) as yszk1 from View_XSHTTZH where date>GETDATE()-5 and date<=GETDATE() group by khh) b
on d.khh=b.khh
group by a.khh,b.yszk1,c.yszk2,d.yszk3
order by yszk desc
字体名: KHH YSZK YSZK1(5日内) YSZK2(5-15日内) YSZK3 (15天前的)
返回的结果: A 10 2 4 4
以下举个例子,自己调试一下结果是否正确DECLARE @Dt DATETIME
SET @Dt = CONVERT(VARCHAR(10), GETDATE() + 1, 120)
SELECT a.khh
,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 5
THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0)
ELSE 0
END) AS [5天内]
,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 10
THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0)
ELSE 0
END) AS [10天内]
,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) < 15
THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0)
ELSE 0
END) AS [15天内]
,SUM(CASE WHEN DATEDIFF(dd, a.[DATE], @Dt) >= 15
THEN ISNULL(a.SKJE, 0) - ISNULL(a.FHJE, 0)
ELSE 0
END) AS [15天之前]
FROM View_XSHTTZH AS a
WHERE ISNULL(a.SKJE, 0) < ISNULL(a.FHJE, 0)
AND a.khh IN (
SELECT TOP 20
khh
FROM View_XSHTTZH
GROUP BY khh
ORDER BY SUM(CASE WHEN ISNULL(SKJE, 0) < ISNULL(FHJE, 0)
THEN ISNULL(FHJE, 0) - ISNULL(SKJE, 0)
ELSE 0
END) DESC )
GROUP BY a.khh