34,590
社区成员
发帖
与我相关
我的任务
分享
-- sql2000+环境
-- 我想问
select 1 where 'cd%' like 'c%' --是得到记录的查询
select 1 where 'c%' like 'cd%' --是得不到记录的查询。
--我的实际应用是一个表的字段类似与c 另一个表的字段类似于cd ,我想通过cd 查询到 所有c 的记录
--【举例】
--创建表
select * into t1 from
(select 'c' as cid,'1234' as cvalue union
select 'c1' as cid,'1234' as cvalue union
select 'c2' as cid,'1234' as cvalue union
select 'd' as cid,'1234' as cvalue union
select 'd1' as cid,'1234' as cvalue union
select 'd2' as cid,'1234' as cvalue union
select 'e' as cid,'1234' as cvalue ) a
select * into t2 from
(select 'c101' as cid,'1234' as cvalue union
select 'c102' as cid,'1234' as cvalue)b
-- 需要的查询结果是:所有t1的cid+上任意字符=t2的cid 的t1记录 [应当就是t1中cid为 c,c1 的行]
--这个语句是不能执行的
select * from t1 where (select cid from t2) like cid+'%'
-- 子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
select * from t1 WHERE
EXISTS(SELECT 1 FROM t2 WHERE t2.cid LIKE t1.cid+'%')