问题是我有数据和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));

顺便说一句:混合时要小心:

10-06 12:51