怎么计算一个列的数值变化?比如某一个列的数值变化是
1 1 2 3 4 2 2希望返回的结果是 变化次数是4次其中2的出现次数是2次
1 1 2 3 4 2 2希望返回的结果是 变化次数是4次其中2的出现次数是2次
解决方案 »
- 文革类型转换的菜菜问题
- 【Mysql如何查询当前使用的数据库名】
- 急求有关日期运算的查询语句
- 两个结构相同的表,怎样查询出某个姓名(xm)的人?
- 可以在产生SQL指令码同时也产生每条记录的insert into语句吗?
- 关于SQL分割的问题,请高人指点?
- 企业管理器可以连接为什么查询分析器不可以连接?
- left join 的疑惑
- 存储过程传递参数问题
- 高分求70-228 Microsoft SQL Server 2000 Administration 认证原版最新教材,必给分,星期一下午结贴!!!!
- SQL回收站
- SQL Server XML Xquery 如何获取RDL 文件中dataset 对应的参数。
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-14 16:37:39
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([col] int)
insert [huang]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 2 union all
select 2
--------------开始查询--------------------------select COUNT(DISTINCT col)[变化次数]
from [huang]
----------------结果----------------------------
/*
变化次数
-----------
4*/
if object_id('[test]') is not null drop table [test]
go
create table [test]([col] int)
insert [test]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 2 union all
select 2
go
;with t
as
(
select *,
ROW_NUMBER() over(order by getdate()) rownum
from test
)select SUM(v) as '值变化次数'
from
(
select *,
(select COUNT(*)
from t t2 where t1.rownum+1 = t2.rownum and t1.col <> t2.col) as v
from t t1
)tt
/*
值变化次数
4
*/
insert @a select 1,NULL
union all select 8,NULL
union all select 1,NULL
union all select 2,NULL
union all select 3,NULL
union all select 4,NULL
union all select 2,NULL
union all select 2,NULL
union all select 2,NULL
union all select 2,NULL
union all select 3,NULLDECLARE @i INT,@j INT
SELECT @i=-1,@j=-1
UPDATE @a SET @i= CASE WHEN a=@j THEN @i ELSE @i+1 END, @j=a ,b=@i
SELECT MAX(b) FROM @a
--result
/*-----------
7
(所影响的行数为 1 行)
*/
if object_id('[t]') is not null drop table [t]
go
create table [t]([col] varchar)
insert [t]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 2 union all
select 2go
select col,COUNT (col )as [变化次数] from [t] group by col
union all
select '合计:', count(*)as [次数] from [t] --col 变化次数
--1 2
--2 3
--3 1
--4 1
--合计: 7