表结构及数据如下:
id 车牌号 地点 经过时间
1 京A8888 北一路 2009-11-11 09:00:00
2 京A9999 北一路 2009-11-11 09:01:00
3 京A7777 北一路 2009-11-11 09:02:00
4 京A8888 北二路 2009-11-11 09:05:00
5 京A1234 北二路 2009-11-11 09:05:00 现需要找出既经过"北一路" 又经过 "北二路"的记录, sql该怎么写呢?
路过的兄弟姐妹给个思路,谢谢先.
解决方案 »
- 导出工具
- 用UTL_FILE导出来的数据某列有多行,如何变成一行?
- Oracle 处理速度控制
- 怎么让存储过程和定时器挂钩
- 怎么用SQL语句判断字符串????
- 请问怎样用语句查询出一个中的两条重复记录?急....
- 创建数据库出错,总说我创建的数据库名与参数DB_NAME不匹配
- 在一个jdbc连接对象执行一次插入操作不提交,然后立即select max(id) from ....,能保证结果是刚才插入的那条么?
- Oracle Developer Form Build 如何连接 Oracle 9i ! ! !
- 动态删除汇总和为0的列,sql(或者存储过程)如何写
- 一打开cmd就弹出"imp.exe遇到一个问题,需要关闭"
- 求解:ftp:connect 未知错误号
没测试过..
试试看。
from(select 车牌号,count(distinct (case 地点 when '北一路' then 1 '北二路' then 2 end))c from table1
group by 车牌号)
where c=2
from(select 车牌号,count(distinct (case 地点 when '北一路' then 1 when '北二路' then 2 end))c from table1
group by 车牌号)
where c=2
单纯从以上理解,可以如下
select a.*
from test a
where exists (select 1 from test b where b.车牌号='北一路' and b.车牌号=a.车牌号)
and exists (select 2 from test c where c.车牌号='北二路' and c.车牌号=a.车牌号)
估计你应该还有其他要求,只是思路,呵呵。
select *
from(select t.*,
count(distinct (case 地点 when '北一路' then 1 '北二路' then 2 end))
over(partition by 车牌号)c from table1 t
)
where c=2
select a.*
from test a
where exists (select 1 from test b where b.地点='北一路' and b.车牌号=a.车牌号)
and exists (select 2 from test c where c.地点='北二路' and c.车牌号=a.车牌号)1楼的不错,
select a.*
from test a
where exists (select 1 from test b where b.地点='北一路' and b.车牌号=a.车牌号)
and a.地点='北二路'
2 select '1' id,'京A8888' card,'北一路' local,'2009-11-11 09:00:00' time from dual
3 union all select '2','京A9999','北一路','2009-11-11 09:01:00' from dual
4 union all select '3','京A7777','北一路','2009-11-11 09:02:00' from dual
5 union all select '4','京A8888','北二路','2009-11-11 09:05:00' from dual
6 union all select '5','京A1234','北二路','2009-11-11 09:05:00' from dual
7 )
8 select card from tt where local ='北一路'
9 Intersect
10 select card from tt where local ='北二路';CARD
-------
京A8888
2 select '1' id,'京A8888' card,'北一路' local,'2009-11-11 09:00:00' time from dual
3 union all select '2','京A9999','北一路','2009-11-11 09:01:00' from dual
4 union all select '3','京A7777','北一路','2009-11-11 09:02:00' from dual
5 union all select '4','京A8888','北二路','2009-11-11 09:05:00' from dual
6 union all select '5','京A1234','北二路','2009-11-11 09:05:00' from dual
7 )
8 select * from tt where card in(
9 select card from tt where local ='北一路'
10 Intersect
11 select card from tt where local ='北二路');ID CARD LOCAL TIME
-- ------- ------ -------------------
1 京A8888 北一路 2009-11-11 09:00:00
4 京A8888 北二路 2009-11-11 09:05:00
谢谢adebayor
用hou1104的方法数据不对lengyunfei006,adebayor 的方法调试中.......