请问这个sql语句要怎么写

hmilyzhen 2015-08-12 10:49:12

表结构：
id sj cbvalue code

1 2015-5-1 15:30 100 001
2 2015-5-1 15:31 105 002
3 2015-5-2 15:30 99 001
4 2015-5-2 15:30 99 002
5 2015-6-1 15:30 80 001
6 2015-6-2 15:30 77 001

我想获取按照code和年月分组取每个code每个月份的第一条记录值和每个月份的最后一条记录值
即
code min(sj) cbvalue1 max(sj) cbvalue2 所属年月
001 2015-5-1 15:30 100 2015-5-2 15:30 99 201505
001 2015-6-1 15:30 80 2015-6-2 15:30 77 201506
002 2015-5-1 15:31 105 2015-5-2 15:30 99 201505

能用一条sql写出来吗？

...全文

111 点赞收藏 5

写回复

5 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

Tiger_Zhao 2015-08-13

没看明白。
有需要专门建个月份表作为辅助啊，给日历之类建辅助表以便查询是常用的手段。

回复赞

hmilyzhen 2015-08-13

能有办法把每个月份的时间点和值作为下个月份的起始时间点起始值？这样就不会出现统计遗漏的问题了。。。

回复赞

Tiger_Zhao 2015-08-12

每一条记录的年月都要算出来才能过滤，始终是全遍历。
大数据当然慢了。
如果你把YM也做为字段，并且在(code,YM,sj)上建索引，就可以用下面的方法了。

/* 测试数据

WITH table1(id,sj,cbvalue,code,YM)AS(

    SELECT 1,'2015-05-01 15:30',100,'001','201505' UNION ALL

    SELECT 2,'2015-05-01 15:31',105,'002','201505' UNION ALL

    SELECT 3,'2015-05-02 15:30',99,'001','201505' UNION ALL

    SELECT 4,'2015-05-02 15:30',99,'001','201505' UNION ALL

    SELECT 5,'2015-06-01 15:30',80,'001','201506' UNION ALL

    SELECT 6,'2015-06-01 15:30',80,'001','201506'

)*/

        SELECT a.code,

               ba.sj [min(sj)],

               ba.cbvalue cbvalue1,

               bd.sj [max(sj)],

               bd.cbvalue cbvalue2,

               a.YM

          FROM (

                SELECT DISTINCT code,ym

                  FROM table1

               ) a

   CROSS APPLY (

                    SELECT TOP 1 sj,cbvalue

                      FROM table1

                     WHERE table1.code = a.code

                       AND table1.ym = a.ym

                  ORDER BY sj

               ) ba

   CROSS APPLY (

                    SELECT TOP 1 sj,cbvalue

                      FROM table1

                     WHERE table1.code = a.code

                       AND table1.ym = a.ym

                  ORDER BY sj DESC

               ) bd

回复赞

hmilyzhen 2015-08-12

谢谢不过貌似大数据量的时候要很长时间。。

引用 1 楼 Tiger_Zhao 的回复:

WITH /* 测试数据
table1(id,sj,cbvalue,code)AS(
    SELECT 1,CONVERT(datetime,'2015-05-01 15:30'),100,'001' UNION ALL
    SELECT 2,'2015-05-01 15:31',105,'002' UNION ALL
    SELECT 3,'2015-05-02 15:30',99,'001' UNION ALL
    SELECT 4,'2015-05-02 15:30',99,'001' UNION ALL
    SELECT 5,'2015-06-01 15:30',80,'001' UNION ALL
    SELECT 6,'2015-06-01 15:30',80,'001'
), */
a AS (
    SELECT sj,cbvalue,code,
           CONVERT(varchar(6),sj,112) YM
      FROM table1
)
,b AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY code,YM ORDER BY sj) rnA,
           ROW_NUMBER() OVER(PARTITION BY code,YM ORDER BY sj DESC) rnD
      FROM a
)
SELECT ba.code,
       ba.sj [min(sj)],
       ba.cbvalue cbvalue1,
       bd.sj [max(sj)],
       bd.cbvalue cbvalue2,
       ba.YM
  FROM b ba
  JOIN b bd
    ON ba.code = bd.code
   AND ba.YM = bd.YM
 WHERE ba.rnA = 1
   AND bd.rnD = 1

code min(sj)                    cbvalue1 max(sj)                    cbvalue2 YM
---- ----------------------- ----------- ----------------------- ----------- ------
001  2015-05-01 15:30:00.000         100 2015-05-02 15:30:00.000          99 201505
001  2015-06-01 15:30:00.000          80 2015-06-01 15:30:00.000          80 201506
002  2015-05-01 15:31:00.000         105 2015-05-01 15:31:00.000         105 201505

回复赞

Tiger_Zhao 2015-08-12

WITH /* 测试数据

table1(id,sj,cbvalue,code)AS(

    SELECT 1,CONVERT(datetime,'2015-05-01 15:30'),100,'001' UNION ALL

    SELECT 2,'2015-05-01 15:31',105,'002' UNION ALL

    SELECT 3,'2015-05-02 15:30',99,'001' UNION ALL

    SELECT 4,'2015-05-02 15:30',99,'001' UNION ALL

    SELECT 5,'2015-06-01 15:30',80,'001' UNION ALL

    SELECT 6,'2015-06-01 15:30',80,'001'

), */

a AS (

    SELECT sj,cbvalue,code,

           CONVERT(varchar(6),sj,112) YM

      FROM table1

)

,b AS (

    SELECT *,

           ROW_NUMBER() OVER(PARTITION BY code,YM ORDER BY sj) rnA,

           ROW_NUMBER() OVER(PARTITION BY code,YM ORDER BY sj DESC) rnD

      FROM a

)

SELECT ba.code,

       ba.sj [min(sj)],

       ba.cbvalue cbvalue1,

       bd.sj [max(sj)],

       bd.cbvalue cbvalue2,

       ba.YM

  FROM b ba

  JOIN b bd

    ON ba.code = bd.code

   AND ba.YM = bd.YM

 WHERE ba.rnA = 1

   AND bd.rnD = 1

code min(sj)                    cbvalue1 max(sj)                    cbvalue2 YM

---- ----------------------- ----------- ----------------------- ----------- ------

001  2015-05-01 15:30:00.000         100 2015-05-02 15:30:00.000          99 201505

001  2015-06-01 15:30:00.000          80 2015-06-01 15:30:00.000          80 201506

002  2015-05-01 15:31:00.000         105 2015-05-01 15:31:00.000         105 201505

回复赞

dleyna-connector-dbus-0.3.0-2.el8.x86_64.rpm 官方离线安装包，亲测可用

c++编写模糊控制生成dll文件_WinCC调用dll完成控制_A_fuzzy_dll_Model.rar 项目可运行！通过C++编译模糊控制算法生成动态链接库DLL文件，WinCC调用第三方DLL文件，完成控制！属于基于WinCC平台下模糊控制算法二次开发！可以通过C++开发多个功能的DLL文件，基于WinCC平台通过多个DLL协调完成一个完整的创效项目！这样就省去了繁琐的界面编程！

guice-parent-4.1-11.module_el8.0.0+30+832da3a1.noarch(1).rpm 官方离线安装包，亲测可用

libXNVCtrl-352.21-9.el8.ppc64le.rpm 官方离线安装包，亲测可用

evemu-libs-2.7.0-8.el8.x86_64.rpm 官方离线安装包，亲测可用

openstack-tripleo-common-container-base-11.5.0-1.el8.noarch.rpm 官方离线安装包，亲测可用

exempi-devel-2.4.5-2.el8.ppc64le.rpm 官方离线安装包，亲测可用

libnetfilter_cthelper-1.0.0-15.el8.ppc64le.rpm 官方离线安装包，亲测可用

fence-agents-all-4.2.1-75.el8.x86_64.rpm 官方离线安装包，亲测可用

python3-sahara-plugin-mapr-tests-unit-2.0.0-1.el8.noarch.rpm 官方离线安装包，亲测可用

Consolas.rar Consolas是windows 对 CN的升级，与CN相比，Consolas更窄，能够在较少的空间显示更多的内容。同时Consolas也是VS系列默认字体。