顺序查找指定科目(A,B,C - 必须从A开始，然后找到最近的B，然后找到最近的C结束)， 并相加Score (A*1 + B*2 + C*1), Time取A 的对应值。
Name, Subject, Score, Time
Zhao, A, 50, 10:00:00
Zhao, B, 50, 10:00:01
Zhao, C, 100, 10:00:02
Qian, X, 60, 10:00:02 --忽略，必须从A开始
Qian, Y, 50, 10:00:03 --忽略，必须从A开始
Qian, A, 10, 10:00:04
Qian, B, 10, 10:00:04
Sun, C, 55, 10:00:04 --忽略，必须从A开始
Qian, C, 10, 10:00:04
Zhao, Z, 10, 10:00:05 --忽略，必须从A开始
Zhao, A, 50, 10:00:06
Zhao, B, 50, 10:00:07
Zhao, B, 100, 10:00:07 -- 忽略，取前一个B （离A最近的）
Sun, C, 55, 10:00:07
Zhao, C, 100, 10:00:08
Zhao, C, 60, 10:00:09 -- 忽略，取前一个C （离A最近的）
结果应该是：
Name, Scores, Time
Zhao, 250, 10:00:00
Qian, 40, 10:00:04
Zhao, 250, 10:00:06
X, Y, Z 可能是任意值。
------解决思路----------------------
WITH /* 测试数据
table1(name,subject,score,time) AS (
SELECT 'Zhao','A',50,'10:00:00' UNION ALL
SELECT 'Zhao','B',50,'10:00:01' UNION ALL
SELECT 'Zhao','C',100,'10:00:02' UNION ALL
SELECT 'Qian','X',60,'10:00:02' UNION ALL
SELECT 'Qian','Y',50,'10:00:03' UNION ALL
SELECT 'Qian','A',10,'10:00:04' UNION ALL
SELECT 'Qian','B',10,'10:00:04' UNION ALL
SELECT 'Sun','C',55,'10:00:04' UNION ALL
SELECT 'Qian','C',10,'10:00:04' UNION ALL
SELECT 'Zhao','Z',10,'10:00:05' UNION ALL
SELECT 'Zhao','A',50,'10:00:06' UNION ALL
SELECT 'Zhao','B',50,'10:00:07' UNION ALL
SELECT 'Zhao','B',100,'10:00:07' UNION ALL
SELECT 'Sun','C',55,'10:00:07' UNION ALL
SELECT 'Zhao','C',100,'10:00:08' UNION ALL
SELECT 'Zhao','C',60,'10:00:09'
), */
t1 AS ( -- 仅选 ABC
SELECT name,subject,score,time,
ROW_NUMBER() OVER(PARTITION BY name ORDER BY time, subject) rn1 -- 加subject以对应time相同
FROM table1
WHERE subject IN ('A','B','C')
)
,t2 AS ( -- 仅选顺序的 ABC
SELECT t.name,t.subject,t.score,t.time,
ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY t.rn1) rn2
FROM t1 t
LEFT JOIN t1 p
ON p.name = t.name
AND p.rn1 + 1 = t.rn1
WHERE (t.subject = 'A')
OR (t.subject = 'B' and p.subject = 'A')
OR (t.subject = 'C' and p.subject = 'B')
)
,t3 AS ( -- 把BC的时间换成A的时间
SELECT name,subject,score,time
FROM t2
WHERE subject = 'A'
UNION ALL
SELECT b.name,b.subject,b.score,a.time
FROM t2 a
JOIN t2 b
ON a.name = b.name
AND a.rn2 + 1 = b.rn2
WHERE b.subject = 'B'
UNION ALL
SELECT c.name,c.subject,c.score,a.time
FROM t2 a
JOIN t2 c
ON a.name = c.name
AND a.rn2 + 2 = c.rn2
WHERE c.subject = 'C'
)
SELECT name,
SUM(score) AS score,
time AS time
FROM t3
GROUP BY name, time
name score time
---- ----------- --------
Zhao 200 10:00:00
Qian 30 10:00:04
Zhao 200 10:00:06
预期结果250？怎么算的？
------解决思路----------------------
借用 楼上的测试数据
WITH
table1(name,subject,score,time) AS (
SELECT 'Zhao','A',50,'10:00:00' UNION ALL
SELECT 'Zhao','B',50,'10:00:01' UNION ALL
SELECT 'Zhao','C',100,'10:00:02' UNION ALL
SELECT 'Qian','X',60,'10:00:02' UNION ALL
SELECT 'Qian','Y',50,'10:00:03' UNION ALL
SELECT 'Qian','A',10,'10:00:04' UNION ALL
SELECT 'Qian','B',10,'10:00:04' UNION ALL
SELECT 'Sun','C',55,'10:00:04' UNION ALL
SELECT 'Qian','C',10,'10:00:04' UNION ALL
SELECT 'Zhao','Z',10,'10:00:05' UNION ALL
SELECT 'Zhao','A',50,'10:00:06' UNION ALL
SELECT 'Zhao','B',50,'10:00:07' UNION ALL
SELECT 'Zhao','B',100,'10:00:07' UNION ALL
SELECT 'Sun','C',55,'10:00:07' UNION ALL
SELECT 'Zhao','C',100,'10:00:08' UNION ALL
SELECT 'Zhao','C',60,'10:00:09'
)
--开始查询
, t1 as (
select ROW_NUMBER() over (order by getdate()) as num1,
* from table1
),t2 as(
select ROW_NUMBER() over( partition by name,subject order by num1 )as num2,
* from t1
),t3 as (
select a.* from t2 a
left join(select * from t2 where subject='A') b
on 1=1
where a.num1=(select MIN(num1) from t2
where num1 >b.num1 and subject='B' and name=b.name)
or a.num1=(select MIN(num1) from t2
where num1 >b.num1 and subject='C' and name=b.name)
union all
select * from t2 where subject='A'
)
select name ,SUM(score)as Scores,MIN(time)as Time
from t3 group by num2,name order by num2
--查询结果
/*
name Scores Time
---- ----------- --------
Qian 30 10:00:04
Zhao 200 10:00:00
Zhao 200 10:00:06
(3 行受影响)
*/