A表
--------
ID
NAME
yearChar
monthB表
-----
ID
AID怎么样实现如下SQL文
因为ORACLE里面OR是不能用外关连的
SELECT
B.ID
A.NAME
FROM
A
,B
WHERE
A.ID(+) = B.AID
AND (
A.yearChar(+) < '2010'
OR
(
A.yearChar(+) = '2010'
AND
A.month(+) <= '03'
)
--------
ID
NAME
yearChar
monthB表
-----
ID
AID怎么样实现如下SQL文
因为ORACLE里面OR是不能用外关连的
SELECT
B.ID
A.NAME
FROM
A
,B
WHERE
A.ID(+) = B.AID
AND (
A.yearChar(+) < '2010'
OR
(
A.yearChar(+) = '2010'
AND
A.month(+) <= '03'
)
类似这样不行么?
With t As(
select '001' Id,'11' Name,'2010' yearchar,'03' Month From dual
Union All select '001','22','2009','03' From dual
Union All select '001','33','2010','08' From dual
Union All select '002','44','2008','03' From dual
Union All select '002','55','2011','03' From dual
),t1 As (
Select 1 Id,'001' aid From dual
Union All Select 2,'002' From Dual
)
Select t1.id , t.Name From t ,t1
Where t.Id(+) = t1.aid
And yearchar||Month <= '201003'
SELECT
B.ID
A.NAME
FROM B
LEFT JOIN A
ON A.ID = B.ID
WHERE A.yearChar <= '2010' AND A.month <'03'
这和我提的问题差异太大了吧?
WHERE
(
A.yearChar(+) < '2010'
OR
(
A.yearChar(+) = '2010'
AND
A.month(+) <= '03'
)
这不能简单的把yearChar 进行合并吧
是 oracle 中有OR就不能有外关连了
With t As(
select '001' Id,'11' Name,'2010' yearchar,'03' Month From dual
Union All select '001','22','2009','03' From dual
Union All select '001','33','2010','08' From dual
Union All select '002','44','2008','03' From dual
Union All select '002','55','2011','03' From dual
),t1 As (
Select 1 Id,'001' aid From dual
Union All Select 2,'002' From Dual
)
Select t1.id , t.Name From t ,t1
Where t.Id(+) = t1.aid
AND
(
t.yearChar(+) < '2010'
OR
(
t.yearChar(+) = '2010'
AND
t.month(+) <= '03'
)
这样执行就应该有错了吧简单的加如 WHERE A.yearChar <= '2010' AND A.month <'03'
是没有问题的
With t As(
select '001' Id,'11' Name,'2010' yearchar,'03' Month From dual
Union All select '001','22','2009','03' From dual
Union All select '001','33','2010','08' From dual
Union All select '002','44','2008','03' From dual
Union All select '002','55','2011','03' From dual
),t1 As (
Select 1 Id,'001' aid From dual
Union All Select 2,'002' From Dual
)
Select t1.id , t.Name From t Right join
t1
On t.Id = t1.aid
AND
(
t.yearChar < '2010'
OR
(
t.yearChar = '2010'
AND
t.Month <= '03'
))
为什么会可以把我的t.yearChar 后面的(+)可以省略呢??