请教一句非常简单的SQL
请教个问题~~
现有两张表如下：
表A
----------------|
id key |
----------------|
1 你好 |
2 are |
________________|
表B
__________________________________________________|
submitTime doneTime msgContent |
__________________________________________________|
2001-10-2 1:30 2001-10-2 1:32 大家好 |
2002-5-18 13:01 2002-5-19 15:00 你好吗 |
2002-9-1 12:05 2002-9-1 12:07 How are you |
2004-1-1 23:59 2004-1-2 1:05 你好吗 |
2004-12-12 5:40 2004-12-12 5:45 good |
2004-12-12 18:34 2004-12-12 19:08 where are you|
__________________________________________________|
注意：以上两表的所有字段（包括submitTime和doneTime）均为VARCHAR2类型
我现在要查出表B中 submitTime 为2002-7-1 23:59 到2005-1-1 23:59 间，并且MsgContent中含有表A中key内容（比如：“你好吗” 包含了 “你好”）的记录总量是多少条，请问SQL该怎么写？
问题点数：0、回复次数：4Top
1 楼skystar99047（天星）回复于 2005-04-03 16:02:57 得分 0
select k,count(*) from(
select a.key k,b.* from 表A a,(select * from 表B where to_date(submitTime,'yyyy-mm-dd hh24:mi') between to_date('2003-7-1 23:59','yyyy-mm-dd hh24:mi') and to_date('2005-1-1 23:59','yyyy-mm-dd hh24:mi') ) b where instr(b.msgContent,a.key)>0
) group by k;
没经过调试Top
2 楼heflyun（飞云）回复于 2005-04-04 14:36:30 得分 0
select count(*) from
(select * from A
where A.key in (select msgconent from B where to_char(submitTime,'yyyy.mm.dd)>'2002.07.01'
and to_char(submitTime,'yyyy.mm.dd')<'2005.01.01');Top
3 楼jjj801206（雪飞惊鸿）回复于 2005-04-04 15:37:37 得分 0
select count(*) from
(select * from A
where A.key in (select msgconent from B where to_char(submitTime,'yyyy.mm.dd)>'2002.07.01'
and to_char(submitTime,'yyyy.mm.dd')<='2005.01.01');
Top
4 楼czrain（蝴蝶剑）回复于 2005-04-04 16:16:54 得分 0
select count(*) from
(select b.* from a,b
where to_date(b.submitTime,'yyyy-mm-dd hh24:mi') between to_date('2002-7-1 23:59','yyyy- mm-dd hh24:mi') and to_date('2005-1-1 23:59','yyyy-mm-dd hh24:mi')
and instr(b.msgContent,a.key)>0
)Top
相关问题
关键词
得分解答快速导航
相关链接
广告也精彩
反馈
