请问这个sql语句要怎么写

[hmilyzhen]

表结构：
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写出来吗？

[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

• 打赏
• 举报

回复 赞

[hmilyzhen]

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

引用 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]

每一条记录的年月都要算出来才能过滤，始终是全遍历。
大数据当然慢了。
如果你把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]

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

• 打赏
• 举报

回复 赞

[Tiger_Zhao]

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

• 打赏
• 举报

回复 赞