table1
id content
001 abc def
002 abc def hij abc
003 hij def
004 bbb
table2
id val
1 abc
2 def
3 hij
比如要查询的值是1,2意味可以查询出table1表中content列里所有包含abc或者def的记录,并且返回包含的个数,按包含个数排列
例如查询1,2
那么结果是 id content times
002 abc def hij abc 3
001 abc def 2
003 def 1在线等,多谢
id content
001 abc def
002 abc def hij abc
003 hij def
004 bbb
table2
id val
1 abc
2 def
3 hij
比如要查询的值是1,2意味可以查询出table1表中content列里所有包含abc或者def的记录,并且返回包含的个数,按包含个数排列
例如查询1,2
那么结果是 id content times
002 abc def hij abc 3
001 abc def 2
003 def 1在线等,多谢
解决方案 »
- Linux 环境下登录oracle失败,提示ORA-12541: TNS: 无监听程序
- 【急】百分求sql匹配类似这样的字段值:"7||6||12||16||30"
- 又是求SQL语句问题,请高手帮看看.
- 创建控制文件Create Controlfile错误?
- 请帮我看一下,这样的批量更新为什么就不行呢?
- 启动OracleOracleTNSListener服务说无法找到路径怎么办?
- 请说出数据连接池的工作机制是什么?
- 请教AIX上Oracle9i内存泄漏问题,急!
- oracle8i中的USING 问题
- 在oracle9i的哪个工具里可以对表进行手工输入数据?
- SQL问题求教
- sqlloader如何同时导入多个数据文件
SELECT '001' ID,'abc def' CONTENT FROM dual
UNION ALL SELECT '002','abc def hij abc' FROM dual
UNION ALL SELECT '003','hij def' FROM dual
UNION ALL SELECT '004','bbb' FROM dual
),
table2 AS(
SELECT 1 ID,'abc' val FROM dual
UNION ALL SELECT 2,'def' FROM dual
UNION ALL SELECT 3,'hij' FROM dual)
SELECT a.ID,a.CONTENT,
SUM((LENGTH(replace(a.content,' ',' '))+2-LENGTH(REPLACE(' '||replace(a.content,' ',' ')||' ',' '||b.val||' ')))/(LENGTH(b.val)+2)) times
FROM table1 a,table2 b
WHERE ' '||a.content||' ' LIKE '% '||b.val||' %'
AND b.id IN (1,2)
GROUP BY a.id,a.content
SQL> SELECT * FROM table1;
ID CONTENT
---------- --------------------
001 abc def
002 abc def hij abc
003 hij def
004 bbb
SQL> SELECT * FROM table2;
ID VAL
---------- --------------------
1 abc
3 hij
SQL>
SQL> WITH t1 AS (SELECT a.id,a.content,b.val,(length(' '||a.content)-length(REPLACE(' '||a.content,' '||b.val,''))) /length(' '||b.val) cnt
2 FROM table1 a,table2 b WHERE b.id IN (1,2) AND instr(a.content,b.val)>0)
3 SELECT ID,content,SUM(cnt) times
4 FROM t1
5 GROUP BY ID,content
6 ORDER BY times DESC;
ID CONTENT TIMES
---------- -------------------- ----------
002 abc def hij abc 3
001 abc def 2
003 hij def 1
SQL>
第一张表的content各个值不一定是由空格隔开的,也就是说
有可能是
id content
001 abcd def
002 abc-def hij abc
003 hij def
004 bbb这样,001和002也算查询满足包含有abc和def
SQL> WITH t1 AS (SELECT a.id,a.content,b.val,(length(a.content)-length(REPLACE(a.content,b.val,''))) /length(b.val) cnt
2 FROM table1 a,table2 b WHERE b.id IN (1,2) AND instr(a.content,b.val)>0)
3 SELECT ID,content,SUM(cnt) times
4 FROM t1
5 GROUP BY ID,content
6 ORDER BY times DESC;
ID CONTENT TIMES
---------- -------------------- ----------
002 abc-def hij abc 3
001 abcd def 2
003 hij def 1
SQL>
WITH table1 AS(
SELECT '001' ID,'abc %$def' CONTENT FROM dual
UNION ALL SELECT '002','/abc- def hij abc' FROM dual
UNION ALL SELECT '003','hij*def' FROM dual
UNION ALL SELECT '004','bbb' FROM dual
),
table2 AS(
SELECT 1 ID,'abc' val FROM dual
UNION ALL SELECT 2,'def' FROM dual
UNION ALL SELECT 3,'hij' FROM dual)
--test data above
SELECT a.ID,a.CONTENT,
SUM((LENGTH(regexp_replace(a.content,'[[:punct:]]+',' '))+2-LENGTH(REPLACE(' '||regexp_replace(a.content,'[[:punct:]]+',' ')||' ',' '||b.val||' ')))/(LENGTH(b.val)+2)) times
FROM table1 a,table2 b
WHERE ' '||regexp_replace(a.content,'[[:punct:]]+',' ')||' ' LIKE '% '||b.val||' %'
AND b.id IN (1,2)
GROUP BY a.id,a.content
ORDER BY times DESC;
REGEXP_REPLACE(CONTENT,'[[:punct:]]',' ')
SELECT A.ID,
A.CONTENT,
SUM((LENGTH(REGEXP_REPLACE(A.CONTENT,
'([[:punct:]]|[[:space:]])+',
' ')) + 2 -
LENGTH(REPLACE(' ' || REGEXP_REPLACE(A.CONTENT,
'([[:punct:]]|[[:space:]])+',
' ') || ' ',
' ' || B.VAL || ' '))) / (LENGTH(B.VAL) + 2)) TIMES
FROM TABLE1 A, TABLE2 B
WHERE ' ' || REGEXP_REPLACE(A.CONTENT, '([[:punct:]]|[[:space:]])+', ' ') || ' ' LIKE
'% ' || B.VAL || ' %'
AND B.ID IN (1, 2)
GROUP BY A.ID, A.CONTENT
ORDER BY TIMES DESC;
问题是我的第一张表中,除了id和content之外,还有很多字段是需要查询的,
如果都group,那不是所有字段需要都写在t1里头?
有没有其他的方案呢?