butween 的奇怪问题

[mingxuan3000]

select A.MANNO, A.Sdate, a.edate, B.Name, b.code, b.sdate, b.edate
from tsyain A

left join mszk B on A.Ads = b.code
and ('2006/01/01' between to_cha(b.sdate,'yyyy/MM/dd') and to_char(b.edate,'yyyy/MM/dd'))
and A.Corp = b.corp

where a.corp = 001
and ('2006/01/01' between a.sdate and a.edate)
and A.MANNO = '2999999999'


left join mszk B 中的 between 不起作用，
between 能这样反着用吗 ('2006/01/01' between a.sdate and a.edate)？
单句查询的时候可以，但在 left join中不起作用了

[AFIC]

between 就是一个>= 一个<=,
还不对肯定是你条件写的有问题。

[mingxuan3000]

select
A.MANNO,
A.Sdate,
a.edate,
B.Name,
b.code,
b.sdate,
b.edate
from
tsyain A
left join mszk B on A.Ads = b.code and '2006/01/01' >=b.sdate and b.edate >='2006/01/01' and A.Corp = b.corp
where
a.corp = 001 and
('2006/01/01' between a.sdate and a.edate) and
A.MANNO = '2999999999'

我改成>= <=是对的 用between 就不对

[goodbee]

应该不会有这种问题吧，是不是写反了？

[hugh_9]

'2006/01/01' between a.sdate and a.edate
是什么意思呢？
何不a.sdate='2006/01/01' and a.edate ='2006/01/01'？

between 是一个>= 一个<=,应该没问题的。

[icedut]

select count(*) from
b_accounts a left join b_account_brands b on a.account_no=b.account_no
and brand_id>='E'
and area_id<='E'


select count(*) from
b_accounts a left join b_account_brands b on a.account_no=b.account_no
and ('E' between area_id and brand_id)

--我做的测试，两个结果都一样，怎么会不一样呢
left 与between 没有关系阿

=============
left join mszk B on A.Ads = b.code
and ('2006/01/01' between to_cha(b.sdate,'yyyy/MM/dd') and to_char(b.edate,'yyyy/MM/dd'))
and A.Corp = b.corp

where a.corp = 001
and ('2006/01/01' between a.sdate and a.edate)
and A.MANNO = '2999999999'

--where 后面的才会过滤结果
where 前面只要写两个表的连接关系就好
你是a left join b ，会把 a中数据都显示出来（如果不加where）

[icedut]

left join mszk B on A.Ads = b.code
and ('2006/01/01' between to_cha(b.sdate,'yyyy/MM/dd') and to_char(b.edate,'yyyy/MM/dd'))
and A.Corp = b.corp

变成
left join mszk B on A.Ads = b.code and A.Corp = b.corp

日起的判断放在where后面

[多壮志]

现在才注意到这个现象.值得注意!

[icedut]

改成>= <= 在on后面也行的

--
我觉得这是不可能的
select count(*) from
b_accounts a left join b_account_brands b on a.account_no=b.account_no
and brand_id>='E'
and area_id<='E'


select count(*) from
b_accounts a left join b_account_brands b on a.account_no=b.account_no
and ('E' between area_id and brand_id)

--我做的测试，两个结果都一样，怎么会不一样呢
left 与between 没有关系阿

[mingxuan3000]

where 后面是可以的
为什么on后面不行 主要是找原理
改成>= <= 在on后面也行的

[icedut]

lz你这样测试吧
where后面你不加条件
只在left join 后面加你那两个不同的测试方法
我认为结果应该是一样的

[mingxuan3000]

我们这边研究了一天，
left join 后面用>= <= 可以，between不行，所以感到很奇怪，谢谢楼上的各位，在看看大家有什么高见，测试时用日期

[AFIC]

别用left join用(+)试试呢？

[icedut]

测试时用日期
--
你用的是字符串的比较阿

[icedut]

select a.*,to_char(a.bill_date,'yyyy-mm-dd'),to_char(a.opr_date,'yyyy-mm-dd')
from t_store_send_bills a left join t_store_send_bill_Details b
on a.bill_no=b.bill_no
and '2006-10-01' >= to_char(a.bill_date,'yyyy-mm-dd')
and to_char(a.opr_date,'yyyy-mm-dd')>='2006-10-01'
--order by a.bill_no

select a.*,to_char(a.bill_date,'yyyy-mm-dd'),to_char(a.opr_date,'yyyy-mm-dd')
from t_store_send_bills a left join t_store_send_bill_Details b
on a.bill_no=b.bill_no
and ('2006-10-01' between to_char(a.bill_date,'yyyy-mm-dd')
and to_char(a.opr_date,'yyyy-mm-dd'))

--两个结果记录数一样