How to find intersection of two tables - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

How to find intersection of two tables

How to find intersection of two tables# Database - 数据库

l*n2008-10-08 07:10

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*d2008-10-08 07:10

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*g2008-10-08 07:10

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*e2008-10-08 07:10

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)