返回更多值的MYSQL查询

Question

问题是我有3个字段的数据和SAT表，每个字段可能包含值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

Answer 1

你有两张桌子，你想数到991。因此，这些表不一定是相关的，连接也没有任何意义。只需将这两个计数相加：

select
  (select count(*) from data where 991 in (insurance1, insurance2, insurance3))
  +
  (select count(*) from sat where 991 in (insurance1, insurance2, insurance3));

顺便说一句:当混合和OR：-时要小心。

Answer 2

我建议把它重新定义为join

SELECT *
FROM DATA v JOIN
     SAT
     ON '991' in (v.PRIMARY_INSURANCE_ID, v.INSURANCE_ID) AND 
        '991' in (s.INSURANCE_ID)

您的查询只提到三个字段。可以扩展in列表，使其具有所需的所有列。根据您的表结构：

SELECT *
FROM DATA v JOIN
     SAT
     ON '991' in (v.INSURANCE1, v.INSURANCE2, v.INSURANCE3) AND 
        '991' in (s.INSURANCE1, s.INSURANCE2, s.INSURANCE3);

编辑：

我认为你根本不想要join：

select count(*), count(distinct id)
from (select id, insurance1 as insurance from data union all
      select id, insurance2 from data union all
      select id, insurance3 from data union all
      select id, insurance1 from sat union all
      select id, insurance2 from sat union all
      select id, insurance3 from sat
     ) i
where insurance = '991';

使用count(*)获取3，使用count(distinct id)获取2。注意:后者假定表之间的id匹配。