问一个比较有挑战性的查询语句

在表中含如下列，它是列车车次，包括数据如k8，t5等等，但也有不规则的车次如：K60/1/0/1，28030/29等等。K60/1/0/1表示车次k60和k61，28030/29表示28030和28029。及用斜杠后的几位字符替换首斜杠前的末尾几位字符
请问如果我要查k61或者28029应该如何写查询语句？

24 个解决方案

#1

没有直接能解决的办法，建议进行数据清洗工作。

如果只是想要应付一下的话，编写自己的函数来解决。

#2

to:LDH202(玲海) .
数据没有办法修改，我只能进行读取，怎么写函数解决啊

#3

效率很低

#4

如果自己写函数效率很低吗？（注：该表为全国性数据。有56万多条记录）

#5

各位老大，帮个忙啊

#6

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_CompSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_CompSTR]
GO

CREATE FUNCTION f_CompSTR(
@s1 varchar(8000),   --包含车次的字符串
@s2 varchar(100)     --要查询的值
)RETURNS varchar(100)
AS
BEGIN
DECLARE @h varchar(100),@s varchar(100),@l int
SELECT @h=LEFT(@s1,CHARINDEX('/',@s1)-1)
,@l=len(@h)+1
,@s1=STUFF(@s1,1,CHARINDEX('/',@s1),'')
IF @h=@s2 RETURN(1)
WHILE CHARINDEX('/',@s1)>0
BEGIN
SELECT @s=LEFT(@s1,CHARINDEX('/',@s1)-1)
,@s1=STUFF(@s1,1,CHARINDEX('/',@s1),'')
IF len(@s)<@l
SET @s=STUFF(@h,@l-len(@s),100,@s)
if @s=@s2 RETURN(1)

END
IF len(@s1)<@l
SET @s1=STUFF(@h,@l-len(@s1),100,@s1)
return(case when @s2=@s1 then 1 else 0 end)
END
GO

#7

--查询时的调用(效率低是因为每次查询都得分拆字符串)
--查 k61
select * from 表 where dbo.f_compstr(车次,'k61')=1

#8

邹老大说的是对的，

还是老观点：强烈建议数据清洗（当然也可以另表存储冗余数据）

用函数只能是对问题不对事

#9

另外说一下：56万多条记录意味着每次的Select操作都要调用该函数56万多次

#10

好象不行。车次传的是车次的列名？

#11

是车次的列名啊

#12

select * from 表 where dbo.f_compstr(车次,'k61')=1

怎么把车次列cc中的值带进"车次"？
select * from 表 where dbo.f_compstr(cc???,'k61')=1

#13

我的表中包含车次的列名为cc

#14

--晕,车次就是包含车次的列名为cc
select * from 表 where dbo.f_compstr(cc,'k61')=1

#15

SELECT *
FROM lksk
WHERE (dbo.f_CompSTR(cc, 'k61') = 1)

说向substring函数传递了无效的length参数

#16

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_CompSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_CompSTR]
GO

CREATE FUNCTION f_CompSTR(
@s1 varchar(8000),   --包含车次的字符串
@s2 varchar(100)     --要查询的值
)RETURNS varchar(100)
AS
BEGIN
DECLARE @h varchar(100),@s varchar(100),@l int
SELECT @h=LEFT(@s1,CHARINDEX('/',@s1+'/')-1)
,@l=len(@h)+1
,@s1=STUFF(@s1,1,CHARINDEX('/',@s1+'/'),'')
IF @h=@s2 RETURN(1)
WHILE CHARINDEX('/',@s1)>0
BEGIN
SELECT @s=LEFT(@s1,CHARINDEX('/',@s1)-1)
,@s1=STUFF(@s1,1,CHARINDEX('/',@s1),'')
IF len(@s)<@l
SET @s=STUFF(@h,@l-len(@s),100,@s)
if @s=@s2 RETURN(1)

END
IF len(@s1)<@l
SET @s1=STUFF(@h,@l-len(@s1),100,@s1)
return(case when @s2=@s1 then 1 else 0 end)
END
GO

--测试
select * from(
select cc='k8' union all 
select cc='t5' union all 
select cc='K60/1/0/1' union all 
select cc='28030/29' union all 
select cc='k61' 
)a where (dbo.f_CompSTR(cc, 'k61') = 1)

/*--结果
cc        
--------- 
K60/1/0/1
k61

（所影响的行数为 2 行）
--*/

#17

哦，老大，你的这个函数好象有点问题，参数含“/”的执行正常，不含“/”的就报错误。
我的数据里面有如“k60/1”的数据，也有如"t5"这样的不含“/”的参数。

#18

问题解决，谢谢皱老大和玲海。想不到第一次来老大就这么热情的帮忙，非常感谢。
我测试了一下，速度是比较慢。反映时间是半分钟。但这个也是没有办法的事情。都是工作人员数据输入不标准造成的。不过这个不是问题。我可以建一个新的车次字典（56万条数据车次是有很多重复的）另外我想可能也同我毒龙750的机器有关系。如果放在大型服务器上相信速度应该要稍微好一点。

#19

你不能这样想，同志！！！

你要想想，这是单客户情况，服务器上如果有10000个用户来查询，会怎么样？

要知道售票点是疯狂多的！！

#20

新建的字典车次数据小于8000。反应时间小于2s。

#21

欢迎发表意见，下午结贴哈。再次对两位表示感谢。

#22

是的，建立字典表、倒排表，都能提高不少的性能。

当然如果你一定没有建表的权限，还可以对函数进行修改以增加速度，

就是修改查询语句：

select * from(
select cc='k8' union all
select cc='t5' union all
select cc='K60/1/0/1' union all
select cc='28030/29' union all
select cc='k61'
)a where (left(cc, 1) = 'k') and (dbo.f_CompSTR(cc, 'k61') = 1) 

就是先对字冠进行过滤

当你在cc上面建立了索引的话，这种操作能先过滤掉较多的数据

#23

"参数含“/”的执行正常，不含“/”的就报错误"

这个错误已经解决了,用我最后一次写的函数就行了

#24

收获不小