问题是我有数据和SAT表有3个字段,每个字段都可能包含值991。下面给出的列数不正确,会给出更多数据。如何获得正确的值。请告知
DATA
id
insurance1
insurance2
insurance3
SAT
id
insurance1
insurance2
insurance3
SELECT * FROM DATA v, SAT s where v.PRIMARY_INSURANCE_ID = '991'
or v.INSURANCE_ID = '991'
or v.INSURANCE_ID = '991'
and s.INSURANCE_ID = '991'
or s.INSURANCE_ID = '991'
or s.INSURANCE_ID = '991';
我举个例子
例1
DATA
id
insurance1 =991
insurance2 =56
insurance3 =22
SAT
id
insurance1=56
insurance2=23
insurance3=96
When query is executed. The count should be 1
例2
DATA
id
insurance1 =991
insurance2 =56
insurance3 =22
SAT
id
insurance1=991
insurance2=23
insurance3=96
When query is executed. The count should be 2
例3
DATA
id
insurance1 =991
insurance2 =56
insurance3 =991
SAT
id
insurance1=991
insurance2=23
insurance3=96
When query is executed. The count should be 3 or 2
最佳答案
你有两张桌子,你想在里面数到991。所以表不一定是相关的,连接也没有意义。简单地加上两个计数:
select
(select count(*) from data where 991 in (insurance1, insurance2, insurance3))
+
(select count(*) from sat where 991 in (insurance1, insurance2, insurance3));
顺便说一句:混合时要小心: