我自己的做法是将用户输入分解后查4次,即依此查c,d,b,a,再判断结果的id是否连续,可行,但感觉不爽,希望能有高明点的方法,期待中...
解决方案 »
- 如何将具有相同的属性的记录汇总成一行?
- 请教各位一个比较菜的存储过程设计问题
- 我的SQL数据库无缘无故出现好多代码!请问高手么 怎么批量删除!高手进 急需高分给!
- 小弟新手请教一个函数的写法,谢谢了!
- SQL server分组求和
- 统计问题?。。。。。。着急啊。。。
- sql server 2005代理问题——已超过挂起的 SQLServerAgent 通知的最大数目,将忽略该通知
- 我Select视图into Table 为什么报错
- 奇怪,有关visual foxpro 简单的问题,能帮忙吗?
- 如何实现把sql server数据库的某个表导出成excel文件?
- 触发器,盼回复!
- 关于CTE的问题
-- Author:happyflystone
-- Version:V1.001
-- Date:2009-07-11 17:33:10
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int,fld1 nvarchar(1))
Go
Insert into ta
select 1,'a' union all
select 2,'c' union all
select 3,'d' union all
select 4,'b' union all
select 5,'a' union all
select 6,'d' union all
select 7,'c' union all
select 8,'a'
Go
--Start
declare @s varchar(5),@t varchar(500)
set @s = 'cdba'Select @t = isnull(@t,'')+ fld1 from ta order by idselect charindex(@s,@t)
--Result:
/*-----------
2(所影响的行数为 1 行)
*/
--End
INSERT TBTEST
SELECT 1 , 'a' UNION
SELECT 2 , 'c' UNION
SELECT 3 , 'd' UNION
SELECT 4 , 'b' UNION
SELECT 5 , 'a' UNION
SELECT 6 , 'd' UNION
SELECT 7 , 'c' UNION
SELECT 8 , 'a 'SELECT ID FROM TBTEST T
WHERE fld1='C'
AND EXISTS(SELECT 1 FROM TBTEST WHERE fld1='D' AND ID=T.ID+1)
AND EXISTS(SELECT 1 FROM TBTEST WHERE fld1='B' AND ID=T.ID+2)
AND EXISTS(SELECT 1 FROM TBTEST WHERE fld1='A' AND ID=T.ID+3)
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-11 17:33:09
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,fld1 VARCHAR(1))
INSERT INTO @T
SELECT 1,'a' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 3,'d' UNION ALL
SELECT 4,'b' UNION ALL
SELECT 5,'a' UNION ALL
SELECT 6,'d' UNION ALL
SELECT 7,'c' UNION ALL
SELECT 8,'a'--SQL查询如下:DECLARE @str VARCHAR(10);
SET @str = 'cdba';--分解字符串SELECT *
FROM @T AS A
WHERE EXISTS(SELECT * FROM @T WHERE fld1 = SUBSTRING(@str,1,1))
AND EXISTS(SELECT * FROM @T
WHERE fld1 = SUBSTRING(@str,2,1) AND id = A.id+1)
AND EXISTS(SELECT * FROM @T
WHERE fld1 = SUBSTRING(@str,3,1) AND id = A.id+2)
AND EXISTS(SELECT * FROM @T
WHERE fld1 = SUBSTRING(@str,4,1) AND id = A.id+3)/*
id fld1
----------- ----
2 c(1 row(s) affected)*/
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int ,fld char(1))
go
insert into tb
select
1 , 'a' union all select
2 , 'c' union all select
3 , 'd' union all select
4 , 'b' union all select
5 , 'a' union all select
6 ,'d' union all select
7 , 'c' union all select
8 , 'a'
go
declare @i int ,@n int,@s varchar(100)
set @s=''
set @i=(select COUNT(*) from tb )
set @n=1
while (@n<=@i)
begin
select @s=@s+fld from tb where id=@n
set @n=@n+1
end
declare @s1 varchar(10)
set @s1='cdba'
select id
from tb
where id=CHARINDEX(@s1,@s)
/*------------
id
-----------
2
-------*/