Works for DB2/NT. Does not work for DB2/OS390. -looi500(looi) @卡尔加里华人论坛:卡尔加里枫下论坛 The Rolia Forum of Calgary

工作学习 / 专业技术讨论 / 又一个SQL问题, 请教大侠们: -poohbear(毛毛熊); 2006-11-7 {294} (#3308272@0)
select * from table1 where column1 = @col

如果没搜到符合的记录, 把@col最右边的字符去掉再搜; 如果没搜到, 再去一个字符, 直到剩下一个字母. @col有可能含1-7个字母. DB是SQL Server.

我现在想到的是写 len(@col) 个select, 然后coalesce, 还没试能不能行. 即使行的话, 也够庞大的. 请问大侠们有好的建议?

需求不清楚，如果中间有结果的，是否还需要搜下去？ -luoboyang(萝卜秧); 2006-11-7 (#3308280@0)

no. "如果"... -poohbear(毛毛熊); 2006-11-7 (#3308281@0)

也许这个performance不太好。。。 -luoboyang(萝卜秧); 2006-11-7 {448} (#3308295@0)
declare @number int

select @number=count(*) from table1 where column1 = @col
if @number = 0
then
select @number = count(*) from table1 where column1=left(1,@col)
if @number=0
then
select @number = count(*) from table1 where column1=left(2,@col)
..................
.................
else
select * from table1 where column1 =left(1,@col)
else
select * from table1 where column1 = @col

这个应该是可行的, 如果coalesce不行的话(不好意思, 我常常是QUERY写出来才发现不行), 我就用它了. 谢谢! -poohbear(毛毛熊); 2006-11-7 (#3308309@0)

你写SQL语句的思路有问题，每次你都想用一个复杂query解决你的问题，但是大多数时候如果你把整个处理过程放到stored procedure里面会更简单。你应该换个思路来想想。 -hard20(hard20); 2006-11-7 {106} (#3308285@0)
在sp里面，你可以用临时表，你可以用cursor,你还可以用if或者while。你问的这些高级问题在sp里面就都不是问题了。

stored procedure = compiled statement, 我只要有了statement, 我当然可以选择compile成sp -- 我会根据程序要求决定的. 你说的临时表，cursor,if或者while都跟comile与否不沾边... -poohbear(毛毛熊); 2006-11-7 (#3308292@0)

can you use 'like'? -super__7(中奖大家有份); 2006-11-7 (#3308296@0)

当然, 只要有合适的logic... -poohbear(毛毛熊); 2006-11-7 (#3308298@0)

select top 1 * from table where (col1 like '%xxxxxx' or col1 like '%xxxxx' or col1 like '%xxxx' ...) order by ... -super__7(中奖大家有份); 2006-11-7 (#3308319@0)

需要加 distinct -luoboyang(萝卜秧); 2006-11-7 (#3308332@0)

加上distinct就能解决我在下面的贴里说的问题(AAA vs. ABC)吗? -poohbear(毛毛熊); 2006-11-7 (#3308353@0)

用top是个好主意, 但是这个的logic我还不能十分确定(不好意思) -- 比如, 我想搜ABC, 表中也有ABC, 这个query会不回返回AAA... -poohbear(毛毛熊); 2006-11-7 (#3308340@0)

看来你还不太了解like 的用法，如果 like %abc 是不会出来aaa的，如果like %A%是有可能的，你仔细读一下like的用法。 -luoboyang(萝卜秧); 2006-11-7 (#3308374@0)

我想是我没有清楚表达出我的疑问, 关键不是LIKE, 是%代表"0-N", 把它放前面是肯定不行的... -poohbear(毛毛熊); 2006-11-7 (#3308384@0)

%ABC 是不会出来AAA的，你再想想。 -luoboyang(萝卜秧); 2006-11-7 (#3308397@0)

top1...%ABC...or...%A... order by会出来AAA... -poohbear(毛毛熊); 2006-11-7 (#3308398@0)

哦，你是要去掉最右边的。。。 -luoboyang(萝卜秧); 2006-11-7 (#3308403@0)

yep :-( -poohbear(毛毛熊); 2006-11-7 (#3308409@0)

这倒也不对, 因为我没想到在左边加%, 所以下意识地觉得这个%应该在右边 :-) -poohbear(毛毛熊); 2006-11-7 (#3308407@0)

还有, 如果我搜ABC, 表中要是有ABC和ABCD的话, 我只要ABC... 所以我觉得用"%"不行, 也许pattern可能会性... -poohbear(毛毛熊); 2006-11-7 (#3308371@0)
想了一下，觉得你这个不符合他的需求，因为他说了，一旦一个有结果了，后面就不要选了。你这样是全选了。即使用了distinct也不对。 -luoboyang(萝卜秧); 2006-11-7 (#3308382@0)

that's why there is a order by, what go to order by is significant. may use database specific func. -super__7(中奖大家有份); 2006-11-7 (#3308394@0)

我只可以去掉右边的字符, 确保左边匹配. 所以%是不能加在左边的... -poohbear(毛毛熊); 2006-11-7 (#3308396@0)

就按你的最直接的思路写七个SELECT，在存储过程里做，最坏情况下全执行到。勉强写成一个大SQL的话，效率也不比七个好。在左边加%用不上索引。 -newkid(newkid); 2006-11-7 (#3308569@0)

既然你这么说, 我就这么做了 ^_^ BTW, 我认为左边加%不能实现... Thanks! -poohbear(毛毛熊); 2006-11-7 (#3308586@0)

Here exec usp_Search 'ABCDEFGH' -hard20(hard20); 2006-11-7 {407} (#3308587@0)
CREATE PROCEDURE usp_Search
( @col VARCHAR(10) )
AS
BEGIN
DECLARE @i INT
DECLARE @sql varchar(500)

SET @i = 0

WHILE @i < LEN(RTRIM(@col))
BEGIN
--等于用这个
select * from LS1 where COL1 = (LEFT(@col,(LEN(RTRIM(@col))- @i)) )
--LIKE 用这个
--select * from LS1 where COL1 LIKE (LEFT(@col,(LEN(RTRIM(@col))- @i)) + '%')
IF @@ROWCOUNT > 0
BREAK
ELSE
SET @i = @i + 1
END

END

多谢! 您前面提到SP, 这个在begin...end之间的部分写成statement也可以, SP的好处是执行快, 坏处是不在程序里面, debug和deploy麻烦. -poohbear(毛毛熊); 2006-11-7 {273} (#3308648@0)
我们开发和生产用不同SERVER, 虽然做个SP不麻烦, 但是倒底还是增加了出错的可能. 所以我宁愿在程序里面create SP, 用完就DROP. 现在这个SQL, 因为不是批量执行, 我觉得没有做成SP的必要.

while确实能使code看起来简洁不少, 虽然performance上跟IF, COALESCE是一样的....明天我都试一试...

从维护，扩展角度来讲，应该尽量使用SP，避免在程序里面直接写入sql 语句。 -luoboyang(萝卜秧); 2006-11-7 (#3308675@0)

看怎么用了. 我个人的SQL和SP都是用XML存取的, 没有任何区别. 我们的新系统是OR MAPPING, 旧系统则归程序的主人, 唯一分不开的是SP, 我才不想跟别人搀和... -poohbear(毛毛熊); 2006-11-7 (#3308695@0)

权限不一样。权限所属不一样。 -luoboyang(萝卜秧); 2006-11-7 (#3308699@0)

可能你们那里有DBA和严格的管理, 我们所有WEB APP都是ADMIN权限(安全不时问题) , 底线是互不影响...看过有些framework的结构, 有一层SP, 可惜啊, 我们的旧系统诞生的时候还不兴那个, 新系统其实已经花钱造好了, 头们不肯migrate... -poohbear(毛毛熊); 2006-11-7 (#3308717@0)

等他们migrate了, 估计我就不用来这里问SQL问题了 -poohbear(毛毛熊); 2006-11-7 (#3308752@0)

如果你对SP的理解是这样的话，那我就没话说了。。。 -hard20(hard20); 2006-11-7 (#3308785@0)

我知道自己的理解肯定有限... 也许过N年后, 会觉得自己很可笑... -poohbear(毛毛熊); 2006-11-8 (#3308907@0)

我看他这个SP很好啊。SP的作用就是把数据存取的逻辑封装到后台，让客户端专心做界面和用户交互的事。岂有用过即扔的道理。 -newkid(newkid); 2006-11-7 (#3308825@0)

"把数据存取的逻辑封装到后台，让客户端专心做界面和用户交互的事" -- 难道除了SP就做不到吗? 如果程序用100个QUERY, 我们当然可以写100个SP, 但是怎么能保证这些SP没有BUG哪? -poohbear(毛毛熊); 2006-11-8 {399} (#3308866@0)
你如果做了改动, 有如何能保证新的SP和新的程序同时DEPLOY哪?

从系统角度说, SP占的系统资源..

从编译角度说, 一个程序, 如果只用少数几次的话, 为什么要compile它哪?

从软件工程说, 一个SP很难extend ...

从实际工作上看, .NET程序员基本都是自己写DB程序, 如果几个人共同做一个PROJECT里面不同的部分, 他们必须共用一个DB帐户, 这种情况下SP势必是混在一起的, 出错的可能极大...

This is my understanding....

你这些观点几乎每条都和我相反，假如你是我老板我就没饭吃了。 -newkid(newkid); 2006-11-8 (#3309179@0)

别担心。。我们公司就是像lz 这样把sql 嵌在.net code behind里的坚决不招。 -feihong(feihong); 2006-11-8 (#3309423@0)
^_^, 每个人都有自己的观点, 大人物的观点肯定影响大一些... 各个公司情况不同, 我们干活的人只好适应了... -poohbear(毛毛熊); 2006-11-8 (#3310019@0)

SELECT * FROM table1 WHERE column1 = LEFT(@col , (SELECT MAX(LEN(column1)) FROM table1 WHERE @col like column1 + '%')) -bdbs(不多不少); 2006-11-8 {136} (#3308858@0)
SELECT *
FROM table1
WHERE column1 =
LEFT(@col,
(
SELECT MAX(LEN(column1))
FROM table1
WHERE @col LIFE column1 + '%'
)

)

这个query适用于任何长度的@col，没有限制，包括NULL。@col的长度不影响performance。测试过没问题。如果题目改作“把@col最［左］边的字符去掉再搜” ，上面query只要作很小改动即可。 -bdbs(不多不少); 2006-11-8 (#3308864@0)

what if length(@col) <length(column1), does it still work? -interview(intervieweree); 2006-11-8 (#3308890@0)

it works even @col is null. :) -bdbs(不多不少); 2006-11-8 (#3308894@0)

这个确实很简洁, 在逻辑上跟 if / while / coalesce不同, 不过我现在还没太看懂... 明天慢慢研究... 至于null怎么处理, 我现在其实还没有idea, 得问business analyst -- 应该是个简单的case.. 多谢!. -poohbear(毛毛熊); 2006-11-8 (#3308871@0)

最后一行是LIKE吧? 如果是的话, 我怀疑... i'll try to run it tomorrow.... -poohbear(毛毛熊); 2006-11-8 (#3308882@0)

是LIKE。看上面Subject里的那个就行了。里面的我只是想format一下看着容易些，不好意思反而打错了。:) 怀疑就不要怀疑了，这么简单的query，错不了的。呵呵 -bdbs(不多不少); 2006-11-8 (#3308888@0)

我好笨啊....还是不觉得它简单.... -poohbear(毛毛熊); 2006-11-8 (#3308895@0)

假如@col = 'abcdefg'，那么sub query就得到所有满足跟@col前面N几个字符相等的最长的一个，假如有'a', 'abcd','abc'，就得到4。LEFT(@col,4) 就是'abcd'。后面就不用解释了吧。 -bdbs(不多不少); 2006-11-8 (#3308883@0)

如果@col = 'ab', column1={'abcd', 'efgh',...}哪? --- 我想不过来了! 明天我要1运行今天大家讨论的query/sp, 2 照萝卜秧说的, 弄清楚LIKE.... -poohbear(毛毛熊); 2006-11-8 (#3308892@0)

'ab' LIKE 'abcd%' is false, 'ab' LIKE 'efgh' is false. then the final query looks like SELECT * FROM tables WHERE column = '', no output. 看来你是需要弄清楚LIKE。；） -bdbs(不多不少); 2006-11-8 (#3308897@0)

you misunderstand him, in this case, he needs abcd to be returned. -interview(intervieweree); 2006-11-8 (#3308903@0)

左看右看没看出来我哪里misunderstand了。如果@col = 'ab', column1={'abcd', 'efgh',...} -bdbs(不多不少); 2006-11-8 {715} (#3308914@0)
select * from table1 where column1 = @col

如果没搜到符合的记录, 把@col最右边的字符去掉再搜; 如果没搜到, 再去一个字符, 直到剩下一个字母. @col有可能含1-7个字母. DB是SQL Server.

我现在想到的是写 len(@col) 个select, 然后coalesce, 还没试能不能行. 即使行的话, 也够庞大的. 请问大侠们有好的建议?
===============================================================================
1st try: @col = 'ab'
select * from table1 where column1 = 'ab'
return nothing

2nd try: 把@col最右边的字符去掉再搜, @col='a'
select * from table1 where column1 = 'a'
still return nothing

3rd try: 把@col最右边的字符去掉再搜, @col=''
select * from table1 where column1 = ''
still return nothing

什么地方理解错了？

In this case, you are right. but, i'm sure this is not his intention.:) -interview(intervieweree); 2006-11-8 (#3308923@0)

#3308916 -bdbs(不多不少); 2006-11-8 (#3308925@0)

简单，如果你坚持不写sp,那就写个simple sql,动态pass参数，run it recursively.... if table is well indexed, should be very quick -interview(intervieweree); 2006-11-8 (#3308900@0)

我没有坚持不写SP, 在TSQL中, 任何SP的BODY都是个statement block.... 我只是觉得只要先把这个statement block弄清楚才是主要的, 至于前面加不加create .. as.. 以后再说吧... -poohbear(毛毛熊); 2006-11-8 (#3308911@0)

那就照我的写吧 -interview(intervieweree); 2006-11-8 {159} (#3308917@0)
getdata(p_string)
{
SQL..... "select * from a where b=" + p_string;
if not find
getdata(p_string-1)
else
return;
}
}
easy...and clear , right?

这还不是个IF? 至于写这么麻烦嘛... -- 不过我已经看不清楚了, 该去睡了... pleasant dreams to all! -poohbear(毛毛熊); 2006-11-8 (#3308926@0)

不用写n个if...., 逻逻辑上也清楚，...唯一的缺点是，如果你的@col有2G长的话，system crashes. -interview(intervieweree); 2006-11-8 (#3308928@0)

hard code & recursive 是程序中最忌讳的，应该尽量避免。 -bdbs(不多不少); 2006-11-8 (#3309515@0)

my understanding of his question is -interview(intervieweree); 2006-11-8 {286} (#3308896@0)
column1: abcdefg
@col='abcdi

if column1<>'abcdi'
else column1<>'abcd'
else column1<>'abc'
....
in this case, inner query gets 0, that does not satisfy his criteria....

SELECT MAX(LEN(column1)) FROM table1 WHERE @col like column1 + '%'))

correct me if i'm wrong

LZ的题目是where column1 = @col ，不是where column1［like］ @col 。你的例子column1: abcdefg， @col='abcdi' 应该是没有东西返回的。inner query gets 0就对了嘛。 -bdbs(不多不少); 2006-11-8 (#3308908@0)

我认为，楼主的意思是，如果column1='abcdefg',@col='abcdi', 他应该得到一个输出：abcdefg. -interview(intervieweree); 2006-11-8 (#3308910@0)

等号是他误用了吧。。。。，他这个肯定是个模糊查询的意思。。。。 -interview(intervieweree); 2006-11-8 (#3308913@0)

哪有你这样篡改LZ的题目再说别人看错了的？@#$%^&*()$%^&#@#!@#$ -bdbs(不多不少); 2006-11-8 (#3308916@0)
这种情况下输出为NULL -poohbear(毛毛熊); 2006-11-8 (#3308918@0)
有点那个意思... -poohbear(毛毛熊); 2006-11-8 (#3308933@0)

很聪明的方法, 把%用在COLUMN1上. 通常%只用在常量上. 但这个QUERY不会用到COLUMN1上的INDEX　if there is one． -looi500(looi); 2006-11-8 (#3308934@0)

这么聪明的方法恐怕不行... declare @a char(5) set @a = 'a' print @a + '%' 结果是a %. 用这个去匹配什么也得不到... -poohbear(毛毛熊); 2006-11-8 (#3310481@0)

declare @a VARchar(5) 就可以了。 -bdbs(不多不少); 2006-11-9 (#3311117@0)

想法很好,但效率不高.还是用SP最好.最坏情况是查7次索引的时间.查一次的话,可用or predicate全部找到,再找最接近的值,比如说pad column1后再减@col,比最小值. -886xyz(cqcq); 2006-11-8 (#3308961@0)

还是分开查的好，虽然有“最坏”情况，但也有“最好（一次命中）”的嘛。写在OR里面对SQL引擎而言也是分开找再UNION的。 -newkid(newkid); 2006-11-8 (#3309394@0)
这还效率不高，怎么才高呢？难道用7个query或者用loop效率更高？ -bdbs(不多不少); 2006-11-8 (#3309492@0)

你先建一个1米粒的表,再建一个索引,按7种可能填具体数据,比一比,告诉我们 -886xyz(cqcq); 2006-11-8 (#3309544@0)

这活还是烦劳您自己去做吧。结果大家都是很愿意知道的。 -bdbs(不多不少); 2006-11-8 (#3309563@0)

按你的想法要多慢有多慢, 可以按1比N来估计, N等于索引的叶数.不服自检去. -886xyz(cqcq); 2006-11-8 (#3309611@0)

你这个子查询里面的LIKE应该是全表扫描的，我不相信SQL SERVER的优化器能生成什么好的计划。 -newkid(newkid); 2006-11-8 (#3309633@0)

LIKE当然比=慢一些，但是这里只LIKE一遍，而且还只是取MAX(LEN())，PERFORMANCE总体来讲比用LOOP显然要强。LOOP的问题是随着@col长度变化performance受影响。 -bdbs(不多不少); 2006-11-8 (#3309647@0)

“只LIKE一遍”，可是1M行就是1M次的LIKE操作啊！至于MAX我倒不相信有什么问题，因为子查询的结果不会很多。COLUMN的宽度，数据的分布在设计的时候就是已知的，总开销在控制之内。表行数的增长可就很难说了，那是没底的。 -newkid(newkid); 2006-11-8 (#3309760@0)

如果column1 字段上有index的话，哪怕7次查询也会比全表扫描快。因为你的查询总是在做全表扫描。不信可以看execution plan. 如果没有的话，那你的查询比7次查询快7倍。 -hard20(hard20); 2006-11-8 (#3310140@0)

我总算看明白了, 个人直觉performance比N个select会差一些, 要是一般程序就是O(N^2) vs. O(N). 创意是like的用法, 我从来没这么用过, 不知道这么写行不行... 根据我的经验, 严重怀疑... 明天试试, 要是行的话, 有长一见识... -poohbear(毛毛熊); 2006-11-8 (#3310307@0)

won't work. Sql server doesn't allow @col to be on the left side of operator... -poohbear(毛毛熊); 2006-11-9 (#3310678@0)

这是你猜的吧？我从SQL 7开始就这么用了。LIKE和=之类的其它比较符没什么两样，没有规定必须哪边是变量哪边是常量的。 -bdbs(不多不少); 2006-11-9 (#3310735@0)

在我们公司的sql server上运行的... -poohbear(毛毛熊); 2006-11-9 (#3311734@0)

不是你有问题就是你公司的ＳＱＬ　ＳＥＲＶＥＲ有问题。#3311117　你不会用的是ＣＨＡＲ吧。把你的ＣＯＤＥ　ＣＯＰＹ＆ＰＡＳＴＥ贴过来我看看。 -bdbs(不多不少); 2006-11-9 (#3311740@0)

常量表达式字段名这种写法的确不标准，但却不存在语法错误，因为优化器处理的时候自动转化成标准格式。 -hard20(hard20); 2006-11-9 (#3311871@0)

什么地方说过LIKE的这种用法不标准了？你们这些人呀，得了便宜还卖乖。学了我这秘籍还说这说那的。早知道俺就不费那脑筋了。；） -bdbs(不多不少); 2006-11-10 (#3312334@0)

我忽然意识到这个requirement可能不对(不合logic), 我现在有99.9%的把握他们是想要: select * from table1 where column1 like @col + '%' ---这对IF/WHILE/coalesce影响不大, 但是对bdbs, interview的高深逻辑有极大影响... -poohbear(毛毛熊); 2006-11-8 (#3308930@0)

我倒是很理解这个需求，即试图寻找最精确的匹配。以我个人开发过的项目而言，有一个电话计费系统，就是试图从电话号码中找出最精确的区号。 -newkid(newkid); 2006-11-8 (#3309180@0)

不好意思,错了, 昨天深更半夜被那二位大侠转晕了, 是要精确匹配... 查了DB的数据, query string的长度是column的最大长度... -poohbear(毛毛熊); 2006-11-8 (#3309361@0)

多花点银子,买各个数据库的Text Search,比自己开发省事多了 -886xyz(cqcq); 2006-11-8 (#3309404@0)
是我被你们转晕了。一会儿说题目对了，一会儿说题目错了。我都不敢随便understand你的题目了。精确匹配是什么意思？你把题目再说一遍吧。 -bdbs(不多不少); 2006-11-8 {95} (#3309488@0)
如果所谓精确匹配就是你原题中where column1 = @col 的condition，我的query绝对正确。你试过了没有？

同晕, 同晕.重复一边题目: 用@col (len = 5)去query column1(char 5). column1里面的数据可能是1-5个char. 我想得到最精确的匹配. 举例:@col = 'ABCDE', 就要检查column1里面有没有ABCDE, ABCD, ABC, AB, A. 遇到第一个匹配就停止... -poohbear(毛毛熊); 2006-11-8 (#3309996@0)

OK，从这个例子解释来看，我那个QUERY完全正确。CASE可以CLOSE了。几种options的Performance你可以自己比较。希望知道具体应用的结果。：） -bdbs(不多不少); 2006-11-8 (#3310226@0)

我明天都试试...答案多多益善...对您的答案我仍有怀疑...#3310307 我一定会把最后的结果贴上来 -- runtime的东西现在很难估计到... -poohbear(毛毛熊); 2006-11-8 (#3310256@0)

刚在自己家的旧机器上(Pentium 120)创建了一个430080行的表， -bdbs(不多不少); 2006-11-9 {183} (#3310528@0)
无index，执行结果<4sec。加上index，执行结果<3sec。时间包括完成输出21054行结果的时间。·(用同一个QUERY反复INSERT生成的表，所以重复数据很多)。@col输入7位长，完成匹配的column1是3位长的。

#3310678 -poohbear(毛毛熊); 2006-11-9 (#3310680@0)
你的测试数据是连续产生的，没有碎片；输出行数过多（索引命中率低），在这种情况下FULL TABLE SCAN 更有优势。 -newkid(newkid); 2006-11-9 (#3310810@0)

数据本身不是连续的，是从一个现有的表里提出来的。只是行数不多，只有100多行，所以才重复复制了一些。 -bdbs(不多不少); 2006-11-9 (#3310841@0)

哦，我不是说数据本身连续，我的意思是它们被连续存储。这样扫描起来很快。 -newkid(newkid); 2006-11-9 (#3310870@0)

多谢大家发表意见, 我今天没来得及试任何approach, 只是可以肯定coalesce能达到目的 -- 这是个decent的IF而已, while也没有本质区别...performance上三者是一样的... 继续研究interview和bdbs的abstract solution...再解释一下原题#3309996 -poohbear(毛毛熊); 2006-11-8 (#3310023@0)
比速度,我的查询最快,但是只能取第一行. -886xyz(cqcq); 2006-11-8 {301} (#3310080@0)
with v(i1,i2) as (select * from (values (1,'ABCDE'), (2, 'ABCD'),(3,'ABC'), (4, 'AB'),(5,'A')) as T order by 1 asc)
select t.* from v, t where v.i2=t.i2 order by v.i1 fetch first 1 row only;

在SQL Server里没有fetch first 1 row only, 可用top 1 .'ABCD' 可用left()替代.
但是只能取第一行.

我只要第一行就够了... 太好了! 明天试试. 多谢! -poohbear(毛毛熊); 2006-11-8 (#3310109@0)
未必最快啊老兄……你为了拿到最精确匹配，最后作了个排序order by v.i1，也就是说在内部先把所有子串都各查一遍。 -newkid(newkid); 2006-11-8 (#3310206@0)

order by v.i1 只是为了保险, 在没选NLJN时保证正确性.优化器应该可以把它优化掉(如果数据太少不一定选NLJN) -886xyz(cqcq); 2006-11-8 (#3310313@0)

HARD CODE的东西，没有具体应用性。 -bdbs(不多不少); 2006-11-8 (#3310241@0)

如果column1 字段上有index的话，哪怕7次查询也会比全表扫描快。因为你的子查询总是在做全表扫描。不信可以看execution plan. 如果没有的话，那你的查询比7次查询快7倍 -hard20(hard20); 2006-11-8 (#3310289@0)

这个我觉得还是 len(@col) 个selection, performance 应该跟if / while是一样的, 但是可以把code写得比较精炼整齐...弄得跟数列似的, 佩服ING... -poohbear(毛毛熊); 2006-11-8 (#3310284@0)

综合一下, 一个QUERY用INDEX. 缺点是要求@col最长是7. Statement较长. -looi500(looi); 2006-11-8 {759} (#3310441@0)
Tested in MySQL:

Select * FROM table1
WHERE column1 = (
Select MAX(column1) from
(SELECT * FROM table1
WHERE LENGTH('abcdefg') > 6 AND column1 = 'abcdefg'
UNION
SELECT * FROM table1
WHERE LENGTH('abcdefg') > 5 AND column1 = LEFT('abcdefg', 6)
UNION
SELECT * FROM table1
WHERE LENGTH('abcdefg') > 4 AND column1 = LEFT('abcdefg', 5)
UNION
SELECT * FROM table1
WHERE LENGTH('abcdefg') > 3 AND column1 = LEFT('abcdefg', 4)
UNION
SELECT * FROM table1
WHERE LENGTH('abcdefg') > 2 AND column1 = LEFT('abcdefg', 3)
UNION
SELECT * FROM table1
WHERE LENGTH('abcdefg') > 1 AND column1 = LEFT('abcdefg', 2)
UNION
SELECT * FROM table1
WHERE LENGTH('abcdefg') > 0 AND column1 = LEFT('abcdefg', 1)) as T);

多谢! 不过, 我个人感觉这个的performance不如if/while/coalesce, 因为它永远是worst case... -poohbear(毛毛熊); 2006-11-8 (#3310459@0)

performance不会有问题. 它决对会用INDEX如果有的话. 并且只是一个DATABASE CALL. 缺点在上面说过了 -looi500(looi); 2006-11-8 (#3310479@0)

多谢, 我明天试试执行计划...有时候想当然了... -poohbear(毛毛熊); 2006-11-8 (#3310486@0)

再仔细想一想, 为了能handle @col长度变化的情况(虽然这不在现在的要求里面), 必须用到while, 象hard20大侠的SP中那样#3308587 .... -poohbear(毛毛熊); 2006-11-8 (#3310474@0)

莫办法,咋整呢,只好用递归了 -886xyz(cqcq); 2006-11-9 {283} (#3310529@0)
with v(i1,i2) as (select * from (values (1,'ABCDE')) as T union all select i1+1, substr('ABCDE', 1, length('ABCDE')-i1) from v where i1<length('ABCDE'))
select t.* from (select i1,i2 from v order by i1) as v2(i1,i2), t where v2.i2=t.i2 order by v2.i1 fetch first 1 row only;

WOW! 递归使用 WITH 子句是SQL SERVER还是DB2的功能？刚刚试了一把,ORACLE不行。 -newkid(newkid); 2006-11-9 (#3310805@0)

DB2 和 SQL SERVER 递归语法相似。 -886xyz(cqcq); 2006-11-9 (#3311557@0)

ORACLE 岂能落后！看我的递归结果集！ -newkid(newkid); 2006-11-9 {196} (#3311838@0)
select level,substr(:p_str,1,level) from dual connect by level <= length(:p_str);

当带入变量 'ABCDE'时，输出：

1 A
2 AB
3 ABC
4 ABCD
5 ABCDE

虽然是题外话，却比楼主的题目有趣，呵呵。

长见识, 虽然俺看不懂.... 严重说明, 我可不时没事找乐给大家出题做着玩的啊... 我问的可都是实际的工作问题.... -poohbear(毛毛熊); 2006-11-9 (#3311856@0)
语句简洁，就是有些不习惯ORACLE 的伪字段，随处可见。可以排序吧？SQL Server居然不许排序。 -886xyz(cqcq); 2006-11-9 (#3311879@0)

可以排序的。这 LEVEL 伪字段是ORACLE 特有的 CONNECT BY (自连接查询，用来遍历树特别高效)中表示连接层数的。DUAL是一个只有一行的伪表，估计你们都知道了。 -newkid(newkid); 2006-11-9 (#3311915@0)

9i和9i之后才有的。sys_connect_by_path 也是9i之后的函数。。。bdbs的那个题用这个方法也就是一个query的事。+ -hard20(hard20); 2006-11-9 (#3311968@0)

Works for DB2/NT. Does not work for DB2/OS390. -looi500(looi); 2006-11-10 {1211} (#3312980@0)
本文发表在 rolia.net 枫下论坛Database server = DB2/NT 8.2.0
SQL authorization ID =
Local database alias =

db2 => with temptbl (num,str) as (select * from (values (1,'abcdefg')) as T unio
n all select num+1, substr('abcdefg', 1, length('abcdefg')-num) from temptbl wh
ere num<length('abcdefg') ) select * from temptbl

NUM STR
----------- -------
SQL0347W The recursive common table expression "TEMPTBL" may contain
an infinite loop. SQLSTATE=01605

1 abcdefg
2 abcdef
3 abcde
4 abcd
5 abc
6 ab
7 a

7 record(s) selected with 1 warning messages printed.

db2 =>
====================================================
Database server = DB2 OS/390 7.1.2
SQL authorization ID =
Local database alias =

db2 => with temptbl (num,str) as (select * from (values (1,'abcdefg')) as T unio
n all select num+1, substr('abcdefg', 1, length('abcdefg')-num) from temptbl wh
ere num<length('abcdefg') ) select * from temptbl
SQL0104N An unexpected token "(" was found following "". Expected tokens may
include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ". SQLSTATE=42601
db2 =>更多精彩文章及讨论，请光临枫下论坛 rolia.net

DB2 UDB Version 8 for z/OS 以上支持递归。 -886xyz(cqcq); 2006-11-10 (#3313026@0)

Hi, poohbear, how're you? I like your name and I'm your brother. :) Have you solved this question? -brother-pooh(Pooh哥哥); 2006-11-10 (#3313044@0)

兄弟! 多谢楼上朋友们, 我的问题解决了 --- 这个select只是我实际问题中的一小部分, 考虑到整体, 也考虑到我自己的水平和习惯, 我通过一个while循环, 找出第一个精确匹配, 然后把@col改写成了可以精确匹配的子字符号串... -poohbear(毛毛熊); 2006-11-10 {212} (#3313594@0)
有几点考虑和收获:

1. 递归看来不是各DB通用的, 我也看不懂...

2. 为了不限定@col长度, 必须用循环, TSQL只有WHILE可用...

3. 不能直接在while里面做select, 否则会造成结果中包含不确定书目的table, 给程序带来麻烦...

@Calgary

Works for DB2/NT. Does not work for DB2/OS390.

Replies, comments and Discussions:

More Topics