22,300
社区成员




/* 测试数据
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
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