表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要怎么写?
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' 和我的是不一样的