下边给出的数据,想实现 按PROID分组后再按ID倒排序,取每组第一条记录和第二条记录的值。
是想得到第一条记录中的FLOWID减第二条记录中的FLOWID的值是否大于1,
求SQL语句的写法。能实现取出按PROID分组,FLOWID没有按照ID的顺序的数据这个功能的语句。
ID PROID FLOWID
1 1 1
2 1 2
3 2 1
4 3 1
5 1 4
6 3 3
7 2 2
8 1 3
9 2 3
10 3 4
11 1 2
12 3 2
是想得到第一条记录中的FLOWID减第二条记录中的FLOWID的值是否大于1,
求SQL语句的写法。能实现取出按PROID分组,FLOWID没有按照ID的顺序的数据这个功能的语句。
ID PROID FLOWID
1 1 1
2 1 2
3 2 1
4 3 1
5 1 4
6 3 3
7 2 2
8 1 3
9 2 3
10 3 4
11 1 2
12 3 2
解决方案 »
- SQL Server 2008 目前为止最稳定的版本是哪个?能放心用于程序中么?
- 关于集合查询的问题
- 如何实现全文检索
- 求一个sql的写法。
- ======超级查询(存储过程)==================
- 存储过程的简单问题,大家快来帮忙呀?
- 表中有个字段是”年月”,我想对年月在指定日期间的数据进行update处理,应该怎么写这个条件??
- 查出表里某一列的纪录值有2个或以上相同的值的所有列~~这sql语句怎么写?
- 请教一个有关SQLSERVER2000 Analysis Service的问题
- 大伙看看,如何实现这个功能呢?
- 求insert update 和 delete 三个操作语句的所有用法
- sql执行效率的问题?
select t.* from tb t where id in (select top 2 id from tb where PROID = t.PROID order by id )是前面两条的话
(
select * ,flag=row_number() over(partition by proid ORDER BY id desc) from tb
) t
where flag<3
insert into tb values(1 , 1 ,1)
insert into tb values(2 , 1 ,2)
insert into tb values(3 , 2 ,1)
insert into tb values(4 , 3 ,1)
insert into tb values(5 , 1 ,4)
insert into tb values(6 , 3 ,3)
insert into tb values(7 , 2 ,2)
insert into tb values(8 , 1 ,3)
insert into tb values(9 , 2 ,3)
insert into tb values(10, 3 ,4)
insert into tb values(11, 1 ,2)
insert into tb values(12, 3 ,2)
goselect t.* from tb t where id in (select top 2 id from tb where PROID = t.PROID order by id desc) order by t.PROID , t.id descdrop table tb/*
ID PROID FLOWID
----------- ----------- -----------
11 1 2
8 1 3
9 2 3
7 2 2
12 3 2
10 3 4(所影响的行数为 6 行)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-13 17:06:39
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[PROID] int,[FLOWID] int)
insert [tb]
select 1,1,1 union all
select 2,1,2 union all
select 3,2,1 union all
select 4,3,1 union all
select 5,1,4 union all
select 6,3,3 union all
select 7,2,2 union all
select 8,1,3 union all
select 9,2,3 union all
select 10,3,4 union all
select 11,1,2 union all
select 12,3,2
--------------开始查询--------------------------
SELECT * FROM TB AS T WHERE (SELECT COUNT(1) FROM tb WHERE PROID = t.PROID AND ID>T.ID)<2
----------------结果----------------------------
/* ID PROID FLOWID
----------- ----------- -----------
7 2 2
8 1 3
9 2 3
10 3 4
11 1 2
12 3 2(6 行受影响)
*/
(
select * ,flag=row_number() over(partition by proid ORDER BY id ) from #abc
) t
where flag<>flowid
insert into tb values(1 , 1 ,1)
insert into tb values(2 , 1 ,2)
insert into tb values(3 , 2 ,1)
insert into tb values(4 , 3 ,1)
insert into tb values(5 , 1 ,4)
insert into tb values(6 , 3 ,3)
insert into tb values(7 , 2 ,2)
insert into tb values(8 , 1 ,3)
insert into tb values(9 , 2 ,3)
insert into tb values(10, 3 ,4)
insert into tb values(11, 1 ,2)
insert into tb values(12, 3 ,2)
goselect m.* , case when m.FLOWID - n.FLOWID > 1 then '数据异常' when m.FLOWID - n.FLOWID = 1 then '数据正常' else '' end from
(select t.* , px = (select count(1) from tb where PROID = t.PROID and id > t.id) + 1 from tb t) m,
(select t.* , px = (select count(1) from tb where PROID = t.PROID and id > t.id) + 1 from tb t) n
where m.PROID = n.PROID and m.px = 1 and n.px = 2
drop table tb/*
ID PROID FLOWID px
----------- ----------- ----------- ----------- --------
9 2 3 1 数据正常
11 1 2 1
12 3 2 1 (所影响的行数为 3 行)
*/
也就没法判断了。
这样FLOWID直接跳过1是个2的话,也属于异常范围,应该怎么判断呢?
insert into tb values(1 , 1 ,1)
insert into tb values(2 , 1 ,2)
insert into tb values(3 , 2 ,1)
insert into tb values(4 , 3 ,1)
insert into tb values(5 , 1 ,4)
insert into tb values(6 , 3 ,3)
insert into tb values(7 , 2 ,2)
insert into tb values(8 , 1 ,3)
insert into tb values(9 , 2 ,3)
insert into tb values(10, 3 ,4)
insert into tb values(11, 1 ,2)
insert into tb values(12, 3 ,2)
insert into tb values(13, 4 ,1) -- 增加一条PROID=4的记录.
goselect m.* , case when m.FLOWID - isnull(n.FLOWID,0) > 1 then '数据异常' when m.FLOWID - isnull(n.FLOWID,0) = 1 then '数据正常' else '' end from
(select t.* , px = (select count(1) from tb where PROID = t.PROID and id > t.id) + 1 from tb t) m left join
(select t.* , px = (select count(1) from tb where PROID = t.PROID and id > t.id) + 1 from tb t) n
on m.PROID = n.PROID and n.px = 2 where m.px = 1
order by m.PROIDdrop table tb/*
ID PROID FLOWID px
----------- ----------- ----------- ----------- --------
11 1 2 1
9 2 3 1 数据正常
12 3 2 1
13 4 1 1 数据正常(所影响的行数为 4 行)
*/