l******n
发帖数: 9344 | 1 Two tables A and B. A has Date and State. B also has Date and State.
A has 20 records and B has 50.
For all records, how can we find out the number of records in A that has
Date and State intersection with B.
for example, if A has
2/1/2008-3/1/2008, CA
B has
2/12-2/14/2008, TX,CA, OR
Then this record is what we want. I want to count these records.
Any way?
thanks in advance | c*****d
发帖数: 6045 | 2 呵呵,欢迎longtian版主光临数据库版
先介绍几个deal给我们吧 ;-)
估计这个就是个人使用
所以规范化,效率也就不用太严格了
tab_A (start_date, end_date, state)
tab_B (start_date, end_date, state1, state2, state3)
select * from tab_A a, tab_B b
where a.start_date < b.start_date
and a.end_date > b.end_date
and a.state in (b.state1, b.state2, b.state3)
【在 l******n 的大作中提到】
: Two tables A and B. A has Date and State. B also has Date and State.
: A has 20 records and B has 50.
: For all records, how can we find out the number of records in A that has
: Date and State intersection with B.
: for example, if A has
: 2/1/2008-3/1/2008, CA
: B has
: 2/12-2/14/2008, TX,CA, OR
: Then this record is what we want. I want to count these records.
: Any way?
| B*****g
发帖数: 34098 | 3 你也搞deal？
我觉得sql应该是
select * from tab_A a, tab_B b
where a.start_date > b.end_date
and a.end_date > b.start_date
and a.state in (b.state1, b.state2, b.state3)
【在 c*****d 的大作中提到】
: 呵呵,欢迎longtian版主光临数据库版
: 先介绍几个deal给我们吧 ;-)
: 估计这个就是个人使用
: 所以规范化,效率也就不用太严格了
: tab_A (start_date, end_date, state)
: tab_B (start_date, end_date, state1, state2, state3)
: select * from tab_A a, tab_B b
: where a.start_date < b.start_date
: and a.end_date > b.end_date
: and a.state in (b.state1, b.state2, b.state3)
| b*****e
发帖数: 364 | 4 Yes. Intersection.
【在 B*****g 的大作中提到】
: 你也搞deal？
: 我觉得sql应该是
: select * from tab_A a, tab_B b
: where a.start_date > b.end_date
: and a.end_date > b.start_date
: and a.state in (b.state1, b.state2, b.state3)
|
|
