假设有张表
字段 qh kj
值 120101 12
120102 34
120103 14
120104 15
120105 12
120106 23
120107 14
120108 12
120109 23
120110 12
120201 23
120202 12
120203 34
120204 12
120205 89查询 所有kj值为12的后一条记录
12后 值34 为2次
值23 为3次
值89 为1次求如何实现该查询,谢谢
字段 qh kj
值 120101 12
120102 34
120103 14
120104 15
120105 12
120106 23
120107 14
120108 12
120109 23
120110 12
120201 23
120202 12
120203 34
120204 12
120205 89查询 所有kj值为12的后一条记录
12后 值34 为2次
值23 为3次
值89 为1次求如何实现该查询,谢谢
go
create table [TB] (qh int,kj int)
insert into [TB]
select 120101,12 union all
select 120102,34 union all
select 120103,14 union all
select 120104,15 union all
select 120105,12 union all
select 120106,23 union all
select 120107,14 union all
select 120108,12 union all
select 120109,23 union all
select 120110,12 union all
select 120201,23 union all
select 120202,12 union all
select 120203,34 union all
select 120204,12 union all
select 120205,89select * from [TB]SELECT kj,COUNT(1) AS 次数
FROM TB WHERE qh IN(
SELECT qh+1
FROM dbo.TB
WHERE kj = 12)
GROUP BY kj/*
kj 次数
23 2
34 2
89 1*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-20 15:41:32
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([qh] int,[kj] int)
insert [tb]
select 120101,12 union all
select 120102,34 union all
select 120103,14 union all
select 120104,15 union all
select 120105,12 union all
select 120106,23 union all
select 120107,14 union all
select 120108,12 union all
select 120109,23 union all
select 120110,12 union all
select 120201,23 union all
select 120202,12 union all
select 120203,34 union all
select 120204,12 union all
select 120205,89
--------------开始查询--------------------------
;with f as
(select px=ROW_NUMBER()over(order by getdate()),* from tb)
select kj,COUNT(1) as 次数 from f where px in(select px +1 from f where kj=12) group by kj----------------结果----------------------------
/* kj 次数
----------- -----------
23 3
34 2
89 1(3 行受影响)
*/
我用的SQL2000
qh非持续加1