表Tdate card id type
100101 1111 001 1
100101 1111 3
100101 1111 001 2
100101 1111 001 3
select *
from t
where (type=1 or type=2 or type=3)
and id='001'
要取这3种type的记录
当type=3时,有可能出现id是空的情况
这时候
利用card关联这个表里type=1里的id
select t2.id
from t t1,t t2
where t1.card=t2.card
and t2.id='001'
and t1.id is null
这样的sql要怎么写?
100101 1111 001 1
100101 1111 3
100101 1111 001 2
100101 1111 001 3
select *
from t
where (type=1 or type=2 or type=3)
and id='001'
要取这3种type的记录
当type=3时,有可能出现id是空的情况
这时候
利用card关联这个表里type=1里的id
select t2.id
from t t1,t t2
where t1.card=t2.card
and t2.id='001'
and t1.id is null
这样的sql要怎么写?
解决方案 »
- oracle自定义function如果异常返回值是什么?
- 如何用oracle存储过程把Blob字段写成图片
- 如何更改oarcle9i中的web服务的端口?
- 为什么我的Oracle 10g下的OracleDBConsole服务启动不了,只有在断开网络连接的情形下才能启动?
- 通过怎样的途径检测一记录的数据发生了变化
- ORA-01031: insufficient privileges问题
- 菜鸟请教高手
- Oracle的Sql语句有left()函数么
- 菜鸟问题: red hat 9下oracle9i安装即将结束时弹出一对话框, 不会添. 真是火大!!
- 急!DBMS_METADATA.GET_DDL问题·
- oracle enterprise manager console 找不到的问题(急)
- 大家好啊,谁能帮我看下下面的问题呢
WHERE TYPE=1 OR TYPE=2
UNOIN ALL
SELECT t1.date,t1.card,NVL(t1.id,t2.id) id,t1.type FROM T t1, T t2
WHERE t1.TYPE=3 AND t2.TYPE=2 AND t1.CARD=t2.CARD
select '100101' date1,'1111' card,'001' id,1 type from dual
union all
select '100101' date1,'1111' card,null id,3 type from dual
union all
select '100101' date1,'1111' card,'001' id,2 type from dual
union all
select '100101' date1,'1111' card,'001' id,3 type from dual
)
--select distinect t2.id from t t1,t t2 where t1.card=t2.card and t2.id='001'
select date1,card,case when type = 3 and id is null then
(select distinct t2.id from t t1,t t2 where t1.card=t2.card and t2.id='001')
else id end id,type
from t
inner join t t2
on t1.card=t2.card
and t2.id='001'
where (t1.type=1 or t1.type=2 or t1.type=3)
CARD,
NVL(ID,
(SELECT ID
FROM T
WHERE CARD = TT.CARD
AND ROWNUM < 2)) ID,
TYPE
FROM T TT;
CARD,
NVL(ID,
(SELECT ID
FROM T
WHERE CARD = TT.CARD
AND ROWNUM < 2)) ID,
TYPE
FROM T TT
WHERE TYPE IN (1, 2, 3);
WHERE TYPE=1 OR TYPE=2
UNOIN ALL
SELECT t1.date,t1.card,NVL(t1.id,t2.id) id,t1.type FROM T t1, T t2
WHERE t1.TYPE=3 AND t2.TYPE=2 AND t1.CARD=t2.CARD
拿1楼的举个例子
union all后面是当type=3的情况,那句要加上id='001'要怎么写呢?
当id不为空时,SELECT t1.date,t1.card,t1.id ,t1.type FROM T t1, T t2
where t1.TYPE=3 and t1.id='001'
当id为空时,SELECT t1.date,t1.card,t2.id,t1.type FROM T t1, T t2
where t1.TYPE=3 AND t2.TYPE=2 and t2.id='001'在where语句里的判断?
SELECT * FROM T
WHERE TYPE=1 OR TYPE=2
UNOIN ALL
SELECT t1.date,t1.card,NVL(t1.id,t2.id) id,t1.type FROM T t1, T t2
WHERE t1.TYPE=3 AND t2.TYPE=2 AND t1.CARD=t2.CARD拿1楼的举个例子
union all后面是当type=3的情况,那句要加上id='001'要怎么写呢?
当id不为空时,SELECT t1.date,t1.card,t1.type FROM T t1, T t2
where t1.TYPE=3 and t1.id='001'
当id为空时,SELECT t1.date,t1.card,t1.type FROM T t1, T t2
where t1.TYPE=3 AND t2.TYPE=2 and t2.id='001'001是做一个变量,查询的结果中是没有id这个字段就可以的,就是where后的语句要怎么判断?
WHERE TYPE=1 OR TYPE=2
UNOIN ALL
SELECT t1.date,t1.card,NVL(t1.id,t2.id) id,t1.type FROM T t1, T t2
WHERE (t1.TYPE=3 AND t2.TYPE=2 AND t1.CARD=t2.CARD) and (t1.id='001' or t2.id='001')
FROM t x
WHERE TYPE IN (1,2,3)
AND ID='001'
OR (ID IS NULL
AND EXISTS(
SELECT 1 FROM t
WHERE ID='001'
AND TYPE=1
AND card=x.card)
);
WHERE TYPE=1 OR TYPE=2 or (type=3 and id='001')
UNOIN ALL
SELECT t1.date,t1.card,t2.id id,t1.type FROM T t1, T t2
WHERE t1.TYPE=3 AND t2.TYPE=1 AND t1.CARD=t2.CARD and t2.id='001' and t1.id is null
这个会不会 比较慢,我这边在测试环境里测的时候很慢,
像2楼那样with弄些临时测试数据,倒是可以
SELECT DATE,card,TYPE FROM T
WHERE TYPE in (1,2,3)
and id='001'
UNOIN ALL
SELECT t1.date,t1.card,t1.type FROM T t1, T t2
WHERE t1.CARD=t2.CARD
AND t2.TYPE=1
AND t1.TYPE=3
and t2.id='001'
and t1.id is null还得再测测,不懂有没问题
SELECT DATE,card,TYPE
FROM t x
WHERE TYPE IN (1,2,3)
AND (ID='001'
OR ID IS NULL
AND EXISTS(
SELECT 1 FROM t
WHERE ID='001'
AND TYPE=1
AND card=x.card)
);慢的话看看执行计划
这时候,这时候才需要这些处理,而type=1或2都不需要这样再把T表拿出来折腾
像是在16楼写的那种意思
FROM t x
WHERE TYPE IN (1,2,3)
AND ID='001'
OR ID IS NULL
AND TYPE=3
AND EXISTS(
SELECT 1 FROM t
WHERE ID='001'
AND TYPE=1
AND card=x.card)
;
你前面的SELECT DATE,card,TYPE FROM T
WHERE TYPE in (1,2,3)
and id='001' 和我的是不一样的