环境:SQL2000原记录如下
ID Flute
1 B
2 B ---1
3 C ---2
4 A
5 A ---3
6 B ---4
7 C ---5
. .
. .
. .
要求查询字段Flute的变化次数, 结果为5次
请教SQL语句如何写?
ID Flute
1 B
2 B ---1
3 C ---2
4 A
5 A ---3
6 B ---4
7 C ---5
. .
. .
. .
要求查询字段Flute的变化次数, 结果为5次
请教SQL语句如何写?
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Flute] varchar(1))
insert [tb]
select 1,'B' union all
select 2,'B' union all
select 3,'C' union all
select 4,'A' union all
select 5,'A' union all
select 6,'B' union all
select 7,'C'
---查询---
select
count(1) as cnt
from
[tb] t
where
not exists(select 1 from tb where Flute=t.Flute and id=t.id+1)---结果---
cnt
-----------
5(所影响的行数为 1 行)
DECLARE @TB TABLE([ID] INT, [Flute] VARCHAR(1))
INSERT @TB
SELECT 1, 'B' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'A' UNION ALL
SELECT 5, 'A' UNION ALL
SELECT 6, 'B' UNION ALL
SELECT 7, 'C'SELECT SUM(NUM) AS NUM
FROM (
SELECT *,NUM=CASE WHEN Flute=(SELECT Flute FROM @TB WHERE ID=TA.ID+1) THEN 0 ELSE 1 END
FROM @TB AS TA
) AS TB
/*
NUM
-----------
5
*/
DECLARE @TB TABLE([ID] INT, [Flute] VARCHAR(1))
INSERT @TB
SELECT 1, 'B' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'A' UNION ALL
SELECT 5, 'A' UNION ALL
SELECT 6, 'B' UNION ALL
SELECT 7, 'C';with cte as
(
select *, type = 1 from @TB where ID = 1
union all
select a.*,type = case when a.Flute = b.Flute then b.type else b.type+1 end from @TB a,cte b where a.id = b.id+1
)
select ID,Flute,ChangeNum = case when (select max(ID) from cte where a.type = type) = a.ID then cast(type as varchar(10)) else '' end
from cte a/*
1 B
2 B 1
3 C 2
4 A
5 A 3
6 B 4
7 C 5
*/
把Flute跟上一个值比较
DECLARE @TB TABLE([ID] INT, [Flute] VARCHAR(1))
INSERT @TB
SELECT 1, 'B' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'A' UNION ALL
SELECT 5, 'A' UNION ALL
SELECT 6, 'B' UNION ALL
SELECT 7, 'C'select count(*) from (select Flute, isnull((select Flute from @tb where id+1=a.id),'') Flute1 from @tb a) b where Flute<>Flute1-----------
5(1 行受影响)