求教SQL按多条件统计某个字段的值

2014-04-22 09:32:44 621 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

比如有一张表，字段有四个，KHH，DATE，FHJE，SKJE，现需统计数据要求如下，找出所有数据中前20个DHJE大于SKJE，值为SKJE-FHJE，对找出的20个数据统计5天内的SKJE-FHJE，10天内的SKJE-FHJE，15天之前的SKJE-FHJE。
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
from View_XSHTTZH a
group by a.khh
后面的就不会写了
请大神们帮忙

...全文

告别jom Error 2：深入理解QtCreator、CMake与MSVC工具链的协作机制本文深入解析QtCreator构建过程中常见的jom Error 2问题，揭示CMake与MSVC工具链的协作机制。从CMake生成器的工作原理到MSVC工具链的组件协作，帮助开发者系统化诊断构建错误，提升开发效率。阅读详情

ilovedoll

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

+ 关注 + 加入社区

切换为时间正序

中国风 2014-05-03

打赏
举报

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

ilovedoll 2014-04-25

打赏
举报

咋就没人给我处理下呢？一张表，字段有四个，KHH，DATE，FHJE，SKJE，现需统计数据要求如下，找出所有数据中前20个FHJE大于SKJE，值为SKJE-FHJE，对找出的20个数据统计5天内的SKJE-FHJE，5-15天内的SKJE-FHJE，15天之前的SKJE-FHJE。得到的结果如下：字体名： KHH YSZK YSZK1（5日内） YSZK2（5-15日内） YSZK3 （15天前的）返回的结果： A 10 2 4 4

ilovedoll 2014-04-22

打赏
举报

好复杂呀，我写的结果有问题，请指导下哪写错了 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

IEEE_China 2014-04-22

打赏
举报


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 行受影响)

ilovedoll 2014-04-22

打赏
举报

重新写过问题：比如有一张表，字段有四个，KHH，DATE，FHJE，SKJE，现需统计数据要求如下，找出所有数据中前20个HJE大于SKJE，值为SKJE-FHJE，对找出的20个数据统计5天内的SKJE-FHJE，5-15天内的SKJE-FHJE，15天之前的SKJE-FHJE。

ilovedoll 2014-04-22

打赏
举报

不好意思，打错了一个字，DHJE应该是FHJE

ilovedoll 2014-04-22

打赏
举报

你这是只求一条而已，我是求一条SQL语句，包含所有记录呀，当求出20条数据时，后面的就会接着来的，得到的结果应该是标题：KHH YSZK YSZK1（5日内） YSZK2（5-15日内） YSZK3 （15天前的）结果： A 10 2 4 4

ymymym16888 2014-04-22

打赏
举报

哥们你的问题问的也太模糊了，还打错了字段名猜测着帮你想了下，要是有点启示，意思一下吧 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天内数据，其它时间段自己改参数。

IEEE_China 2014-04-22

打赏
举报


--@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

求教SQL按多条件统计某个字段的值

相关推荐