id datetime weizbianh name
1 2012-12-12 00:00:01 1 a
2 2012-12-12 00:00:05 1 b
3 2012-12-12 00:00:10 1 a
4 2012-12-12 00:00:51 1 a
5 2012-12-12 00:00:52 1 b
6 2012-12-12 00:00:53 1 a
7 2012-12-12 00:01:01 1 a
8 2012-12-12 00:01:10 1 a
9 2012-12-12 00:02:01 1 a想得到结果:
查询出a被b打断了几次!即返回值为: 3
查询a 连续出现的最小记录
即:
id datetime weizbianh name
1 2012-12-12 00:00:01 1 a
3 2012-12-12 00:00:10 1 a
6 2012-12-12 00:00:53 1 a
1 2012-12-12 00:00:01 1 a
2 2012-12-12 00:00:05 1 b
3 2012-12-12 00:00:10 1 a
4 2012-12-12 00:00:51 1 a
5 2012-12-12 00:00:52 1 b
6 2012-12-12 00:00:53 1 a
7 2012-12-12 00:01:01 1 a
8 2012-12-12 00:01:10 1 a
9 2012-12-12 00:02:01 1 a想得到结果:
查询出a被b打断了几次!即返回值为: 3
查询a 连续出现的最小记录
即:
id datetime weizbianh name
1 2012-12-12 00:00:01 1 a
3 2012-12-12 00:00:10 1 a
6 2012-12-12 00:00:53 1 a
解决方案 »
- 关数据库的论文以何命题?
- 菜鸟问题,如何删除SQL2005中的数据后保留硬盘目录下的文件
- 我想请问两个网站用同一个数据库,用户同时添加数据会不会出错?
- 【请问怎么提高数据库性能啊?】
- sql 2000 中带条件的动态分页问题
- 跪求一个求最大值的高效SQL语句,请各位大侠帮忙,谢谢!
- 请高手帮帮忙,存储过程怎么写?
- Delphi 批量更新有问题 ApplyUpdates (少用) ,各位高手请看下代码!
- 请教:安装 server的时候为什么他默认的时候总是以sa身份,为什么系统不能让我自己分配命名
- 想做个邮件收发程序 用什么办法来保存邮件?数据库吗?
- 问下SQL 查询的执行顺序
- 执行sql语句时发生死锁,如何才能让这条sql语句再次执行?
select distinct * from
(select top 1 * from 表名 where name ='a'
union all
select * from 表名 where id-1=(select id from 表名 where name ='b'))a
GO
IF OBJECT_ID('TB_1') IS NOT NULL
DROP TABLE TB_1
CREATE TABLE TB_1
(
ID INT IDENTITY(1,1),
name VARCHAR(10)
)
INSERT INTO TB_1(name)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a'
GOSELECT
*
FROM
TB_1 A
WHERE
(
EXISTS(SELECT 1 FROM TB_1 B WHERE B.ID+1 = A.ID AND B.name <> A.name)
OR ID = (SELECT MIN(ID) FROM TB_1)
)
AND name <> 'b' IF OBJECT_ID('TB_1') IS NOT NULL
DROP TABLE TB_1
GO
程序报错:
子查询返回的值多于一个。当子查询跟随在=、!=、<、>、之后,或子查询用表达式时,这种情况是不允许的
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-25 11:45:15
-- Version:-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) -- Feb 10 2012 19:13:17 -- Copyright (c) Microsoft Corporation-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]
(
[id] int,
[datetime] datetime,
[weizbianh] int,
[name] varchar(1)
)
insert [test]
select 1,'2012-12-12 00:00:01',1,'a' union all
select 2,'2012-12-12 00:00:05',1,'b' union all
select 3,'2012-12-12 00:00:10',1,'a' union all
select 4,'2012-12-12 00:00:51',1,'a' union all
select 5,'2012-12-12 00:00:52',1,'b' union all
select 6,'2012-12-12 00:00:53',1,'a' union all
select 7,'2012-12-12 00:01:01',1,'a' union all
select 8,'2012-12-12 00:01:10',1,'a' union all
select 9,'2012-12-12 00:02:01',1,'a'
go
select * from test a
where not exists(select 1 from test b where a.id=b.id+1 and a.name=b.name)
and a.name='a'
/*
id datetime weizbianh name
1 2012-12-12 00:00:01.000 1 a
3 2012-12-12 00:00:10.000 1 a
6 2012-12-12 00:00:53.000 1 a
*/