请问这个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
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-08-12 10:49
社区公告
暂无公告