Topic: SQL 问题紧急求救,万份感谢 @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

The system does not look like a good design. If I were you, I will change the architecture first to form a more proper one. -sailor(Sailor); 2006-10-12 (#3259452@0)

Thx Sailor. I'm working on an ongoing project, Yes I agree it is not a good dsign, but changing data schema will affect the exsiting system. anay ideas? -waiting(OK); 2006-10-12 (#3259484@0)
I didn't see anything wrong, except that usually ID should be the primary key (unique). -newkid(newkid); 2006-10-13 (#3260340@0)

SELECT * FROM TABLE WHERE ID||TYPE IN ('101A','102B','115C','106C') -bluebluesky(bluebluesky); 2006-10-12 (#3259493@0)

实不相瞒, 我写过很多比较复杂的PL/SQL, 真没用过也没听说过这个操作. 可能象sailor说的, 我们的DB design比较好吧^_^. 如果我真遇到LZ说的这种情况, 我会老老实实在程序里面就把数据处理成跟table column一模一样的... -poohbear(毛毛熊); 2006-10-13 (#3261039@0)

thanks, I tried this but it doesn't work to put || in where clause -waiting(OK); 2006-10-12 (#3259501@0)

select Name,ID,Type from ( SELECT Name,ID,Type, ID||TYPE x FROM TABLE ) b WHERE b.x IN ('101A','102B','115C','106C') -bluebluesky(bluebluesky); 2006-10-12 (#3259516@0)
are you sure about that || cannot be used in where clause? got a chance to try it today, works fine. check implicitly data type conversion, if necessary add to_char(). -bluebluesky(bluebluesky); 2006-10-13 (#3259922@0)
听说||在where不work，吓到我了，那俺们的系统岂不是有些地方不work！于是乎开toad小试了一下，||和concat都木有问题，to_char都不用写，自动了。还加了个index在 id||type 上，好使着呢。楼主用的啥版本？ -heeltoe(不過三); 2006-10-13 (#3260034@0)

老兄，真是PL/SQL的话，此处应该是在一个游标之中,或者 SELECT ... BULK COLLECT INTO 数组。没有单独写这样一个SQL的。 -newkid(newkid); 2006-10-13 (#3260319@0)

呵呵，我本来是要跟楼主的（他说你的SQL不WORK）。在PL/SQL方面我确实不是新手了。 -newkid(newkid); 2006-10-13 (#3260635@0)

concat( to_char(id, 999), type) 不行么？ -canadiantire(轮胎-pax et lux); 2006-10-13 (#3259704@0)
Try to use the index. If it is a big table, id || type or concate(id,type) will take more time since no index is using.My suggestion is to split the 101A to 2 parts:id:101 Type:A first and then construction the sql statement with id = ? and type=?. -hk_tnt(HK_TNT); 2006-10-13 {129} (#3259776@0)

Good point. Parse the string'101A' to ID'101' and Type'A' -guanshui88(guanshui88); 2006-10-13 (#3259800@0)
good point，但是你的知识需要更新了。CREATE INDEX IDX_ID_TYPE ON TEST_TABLE (ID || TYPE) -heeltoe(不過三); 2006-10-13 (#3259834@0)

yeap, now function index is available. like CREATE INDEX IDX_ID_TYPE ON TEST_TABLE (concat(ID,TYPE)) -bluebluesky(bluebluesky); 2006-10-13 (#3259905@0)
完全没有必要用函数索引。 SELECT ... FROM ...WHERE (ID,TYPE) IN ((101,'A'),(102,'B'),(115,'C'),(106,'C')) -newkid(newkid); 2006-10-13 (#3260333@0)

没人说必须用函数索引，我只是说可以这么做。你那么干还得把‘101A’拆成（101，‘A'），也不省事 -heeltoe(不過三); 2006-10-13 (#3260481@0)

既然已经有了唯一索引 ON (ID,TYPE), 那么自然要利用它。再建立另一个函数索引有系统开销（存储开销，维护开销）。如果你有另一个查询只用到ID的，那么这个唯一索引还能派上用场，函数索引就完全没用了。 -newkid(newkid); 2006-10-13 (#3260551@0)
此外，把101A拆开在逻辑上比较正确。设想你有另一行 ID=10, TYPE='1A', 那么合并后也是101A,而这一行是不该被选中的。 -newkid(newkid); 2006-10-13 (#3260556@0)

看来你始终没有搞清楚他的要求。他现在有的查询条件，就是‘101A'，你可以说这不合理，但是这是客观现实。你要拆分它，反倒不知道该拆分成(10, '1A')还是(101,'A')了。你给拆错了，本来能查出来的反倒查不出来。 -heeltoe(不過三); 2006-10-13 (#3260577@0)

只是就事论事，没兴趣judge你的理解能力。其实干过一两个项目的new grad都有可能比客户更清楚他们的需求的 -heeltoe(不過三); 2006-10-13 (#3261455@0)

SELECT name, ID, Type FROM (SELECT name, ID, Type, (ID + Type) as MyField FROM TABLE) as A WHERE MyField in ('101A','102B','115C','106C') -wonton_soup(Wonton Soup); 2006-10-13 (#3259828@0)
一句话。create table T1 (id char(3), type char(1));
insert into t1 values ('101', 'A'),('102', 'B') ;

with V(id, type) as
(select substr(col, 1,3) as ID, substr(col, 4,1) as TYPE from (values '101A','102B','115C','106C') as temp(col))
select T.* from
T1 T, V
where T.id=V.id and T.type=V.type;
ID TYPE
--- ----
101 A
102 B

2 record(s) selected. -886xyz(cqcq); 2006-10-13 {376} (#3260098@0)

@Florida