declare @s table (id int,val int)
insert into @s
select 0,234 union all
select 1,123 union all
select 2,34 union all
select 3,45 union all
select 4,45 union all
select 5,56 union all
select 6,645 union all
select 7,456
SET STATISTICS TIME ON
SELECT a.id as gid,
CASE a.id
WHEN a.id>2 THEN AVG(b.val)
ELSE NULL
END AS ma3
FROM @s a,@s b WHERE b.id<=a.id AND b.id>a.id-3
GROUP BY a.id
SET STATISTICS TIME OFF
GOa.id>2这里报错
insert into @s
select 0,234 union all
select 1,123 union all
select 2,34 union all
select 3,45 union all
select 4,45 union all
select 5,56 union all
select 6,645 union all
select 7,456
SET STATISTICS TIME ON
SELECT a.id as gid,
CASE a.id
WHEN a.id>2 THEN AVG(b.val)
ELSE NULL
END AS ma3
FROM @s a,@s b WHERE b.id<=a.id AND b.id>a.id-3
GROUP BY a.id
SET STATISTICS TIME OFF
GOa.id>2这里报错
SELECT a.id as gid,
(CASE WHEN a.id>2 THEN AVG(b.val)
ELSE NULL
END) AS ma3
FROM @s a,@s b WHERE b.id<=a.id AND b.id>a.id-3
WHEN a.id>2 THEN AVG(b.val)
ELSE NULL
END AS ma3
--這種情況只允許相等值
CASE X
WHEN ... THEN ...
ELSE
END--這種情況可以有比較值
CASE
WHEN X>1 THEN ...
WHEN ... THEN ...
ELSE ...
END
SELECT a.id as gid,
CASE WHEN a.id>2 THEN AVG(b.val)
ELSE NULL
END AS ma3
FROM @s a,@s b WHERE b.id<=a.id AND b.id>a.id-3
GROUP BY a.id
SET STATISTICS TIME OFF
GO