SELECT * FROM (
SELECT TAKEY,TACONTENT FROM TA表
UNION ALL
SELECT TBKEY,TBCONTENT FROM TB表
UNION ALL
SELECT TCKEY,TCCONTENT FROM TB表
) T
WHERE charindex(ltrim(T.TCKEY)+' '+ltrim(T.TACONTENT),'TK')>0
SELECT TAKEY,TACONTENT FROM TA表
UNION ALL
SELECT TBKEY,TBCONTENT FROM TB表
UNION ALL
SELECT TCKEY,TCCONTENT FROM TB表
) T
WHERE charindex(ltrim(T.TCKEY)+' '+ltrim(T.TACONTENT),'TK')>0
--try
SELECT * FROM (
SELECT TAKEY,TACONTENT FROM TA表
UNION ALL
SELECT TBKEY,TBCONTENT FROM TB表
UNION ALL
SELECT TCKEY,TCCONTENT FROM TB表
) T
WHERE charindex('TK',ltrim(T.TCKEY)+' '+ltrim(T.TACONTENT))>0 --刚才写反了..
TAKEY最后字母T,TACONTENT第一字母K 应该是不符合要求的吧。
语句应改为如下:
SELECT * FROM (
SELECT TAKEY,TACONTENT FROM TA表
UNION ALL
SELECT TBKEY,TBCONTENT FROM TB表
UNION ALL
SELECT TCKEY,TCCONTENT FROM TB表
) T
WHERE charindex('TK',ltrim(T.TCKEY))>0 or charindex('TK',ltrim(T.TACONTENT))>0
TB表 字段是TBID,TBKEY,TBCONTENT
TC表 字段是TCID,TCKEY,TCCONTENT 查询的关键词是TK,要求查询在三个表中的TAKEY,TACONTENT,TBKEY,TBCONTENT,TCKEY,TCCONTENT,是否符合, 一次性查出记录,可以实现吗? ---
举点例子看看
select * from TA
where charindex(TAKEY, 'TK') > 0
union
select * from TA
where charindex(TACONTENT, 'TK') > 0
union
select * from TB
where charindex(TBKEY, 'TK') > 0
union
select * from TB
where charindex(TBCONTENT, 'TK') > 0
union
select * from TC
where charindex(TCKEY, 'TK') > 0
union
select * from TC
where charindex(TCCONTENT, 'TK') > 0
SELECT * FROM (
SELECT * FROM TA
UNION ALL
SELECT * FROM TB
UNION ALL
SELECT * FROM Tc
) a
WHERE charindex('TK',ltrim(TCKEY)+ltrim(TACONTENT))>0
declare @TB table(TBID int, TBKEY nvarchar(10),TBCONTENT nvarchar(10))
declare @TC table(TCID int, TCKEY nvarchar(10),TCCONTENT nvarchar(10))INSERT INTO @TA
SELECT 1,'T','K' UNION ALL
SELECT 2,'TT','AK' UNION ALL
SELECT 3,'TK','TK' UNION ALL
SELECT 4,'KT','TK' INSERT INTO @TB
SELECT 5,'TR','K' UNION ALL
SELECT 6,'TTS','AK' UNION ALL
SELECT 7,'TKU','TK' UNION ALL
SELECT 8,'KTP','TK' INSERT INTO @TC
SELECT 9,'TRT','K' UNION ALL -- 这一行没有选出来
SELECT 10,'TTSTK','AK' UNION ALL
SELECT 11,'TKUS','TSK' UNION ALL
SELECT 12,'KTP','TRK'
SELECT * FROM (
SELECT TAID,TAKEY,TACONTENT FROM @TA
UNION ALL
SELECT TBID,TBKEY,TBCONTENT FROM @TB
UNION ALL
SELECT TCID,TCKEY,TCCONTENT FROM @TC
) T
WHERE charindex('TK',ltrim(T.TAKEY)+' '+ltrim(T.TACONTENT))>0
/*
TAID TAKEY TACONTENT
----------- ---------- ----------
3 TK TK
4 KT TK
7 TKU TK
8 KTP TK
10 TTSTK AK
11 TKUS TSK
*/
SELECT * FROM (
SELECT TAKEY,TACONTENT FROM TA
UNION ALL
SELECT TBKEY,TBCONTENT FROM TB
UNION ALL
SELECT TCKEY,TCCONTENT FROM Tc
) a
WHERE charindex('TK',ltrim(TAKEY)+ltrim(TACONTENT))>0
SELECT TAKEY,TACONTENT FROM TA表
UNION ALL
SELECT TBKEY,TBCONTENT FROM TB表
UNION ALL
SELECT TCKEY,TCCONTENT FROM TB表
) T
WHERE charindex(ltrim(T.TCKEY)+' '+ltrim(T.TACONTENT),'TK')>0
SELECT TAID,TAKEY,TACONTENT FROM @TA
UNION ALL
SELECT TBID,TBKEY,TBCONTENT FROM @TB
UNION ALL
SELECT TCID,TCKEY,TCCONTENT FROM @TC
) T
WHERE charindex('TK',ltrim(T.TAKEY)+' '+ltrim(T.TACONTENT))>0 支持
WHERE charindex('TK',ltrim(T.TAKEY)+' '+ltrim(T.TACONTENT))>0TBKEY,TBCONTENT 怎么不考虑?
union all所有的表,如果几个表的字段不一样,则默认以第一个表的字段为最后字段。
会的兄弟帮帮写一下 :)
Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' 被搜索的字符串。
SearchChar = "P" ' 要查找字符串 "P"。
' 从第四个字符开始,以文本比较的方式找起。返回值为 6(小写 p)。
' 小写 p 和大写 P 在文本比较下是一样的。
MyPos = Instr(4, SearchString, SearchChar, 1)
' 从第4个字符开使,按照原文比较的方式找起。返回值为 9(大写 P)。
' 小写 p 和大写 P 在二进制比较下是不一样的。
MyPos = Instr(1, SearchString, SearchChar, 0)
' 缺省的比对方式为二进制比较(最后一个参数可省略)。
MyPos = Instr(SearchString, SearchChar) ' 返回 9。
MyPos = Instr(1, SearchString, "W") ' 返回 0。
SELECT * FROM (
SELECT TAKEY,TACONTENT FROM TA表
UNION ALL
SELECT TBKEY,TBCONTENT FROM TB表
UNION ALL
SELECT TCKEY,TCCONTENT FROM TB表
) T
WHERE Instr(1, T.TCKEY, "tk")>0
这样可以吗?怎么出现无效字符 ?
instr帮助如下:
函数:InStr([start, ]string1, string2[, compare])
返回值
如果 InStr 返回
string1 的长度为零 0
string1 为 Null Null
string2 的长度为零 start
string2 为 Null Null
找不到 string2 0
在 string1 中找到了 string2 找到匹配项的位置
start > string2 0
SELECT TAKEY,TACONTENT FROM TA表
UNION ALL
SELECT TBKEY,TBCONTENT FROM TB表
UNION ALL
SELECT TCKEY,TCCONTENT FROM TB表
) T
WHERE T.TAKEY LIKE "*tk*"试一试,看是否可以。