试图同时获得卡和现金会员

Question

您好，我已经这样做了，以获取付款方式类型现金的会员详细信息

       SELECT members.member_Id, members.member_Lastname, members.member_Firstname, members.member_PostCode, 
            members.member_Reference,  members.member_Dob,30*memberToMship_ChargePerPeriod/DateDiff(memberToMship_EndDate, memberToMship_StartDate) As monthly_amount, mshiptypes.mshipType_Name, mshipstatustypes.mshipStatusType_Name,
            membertomships.memberToMship_EndDate, IF(mshipOption_Period='year', TIMESTAMPDIFF (YEAR,memberToMship_StartDate, memberToMship_EndDate),
            TIMESTAMPDIFF (MONTH ,memberToMship_StartDate,memberToMship_EndDate ) ) *memberToMship_ChargePerPeriod As Total
          FROM members LEFT JOIN membertomships ON membertomships.member_Id = members.member_Id,
          LEFT JOIN mshipstatustypes ON mshipstatustypes.mshipStatusType_Id = membertomships.mshipStatusType_Id,
          LEFT JOIN mshipoptions ON mshipoptions.mshipOption_Id = membertomships.mshipOption_Id,
          LEFT JOIN mshiptypes ON mshiptypes.mshipType_Id = mshipoptions.mshipType_Id,
          WHERE membertomships.memberToMship_PayMethod='Cash'
          AND members.member_Active LIKE 'y%'
          AND (mshipstatustypes.mshipStatusType_Id='2' 
            OR mshipstatustypes.mshipStatusType_Id = '3' 
            OR mshipstatustypes.mshipStatusType_Id='6')"

，并执行另一个查询，以获取付款方式类型卡的会员详细信息

                               SELECT members.member_Id, members.member_Lastname, members.member_Firstname, members.member_PostCode,
          members.member_Reference,  members.member_Dob,
          30*memberToMship_ChargePerPeriod/DateDiff(memberToMship_EndDate, memberToMship_StartDate) As monthly_amount, 
          mshiptypes.mshipType_Name, mshipstatustypes.mshipStatusType_Name,
          membertomships.memberToMship_EndDate,
          IF(mshipOption_Period='year', TIMESTAMPDIFF (YEAR,memberToMship_StartDate, memberToMship_EndDate),
          TIMESTAMPDIFF (MONTH ,memberToMship_StartDate,memberToMship_EndDate ) ) *memberToMship_ChargePerPeriod As Total
        FROM members LEFT JOIN membertomships ON membertomships.member_Id = members.member_Id
        LEFT JOIN mshipstatustypes ON mshipstatustypes.mshipStatusType_Id = membertomships.mshipStatusType_Id
        LEFT JOIN mshipoptions ON mshipoptions.mshipOption_Id = membertomships.mshipOption_Id
        LEFT JOIN mshiptypes ON mshiptypes.mshipType_Id = mshipoptions.mshipType_Id
        WHERE membertomships.memberToMship_PayMethod='Card'
        AND members.member_Active LIKE 'y%'
        AND (mshipstatustypes.mshipStatusType_Id='2' OR  mshipstatustypes.mshipStatusType_Id = '3' 
            OR mshipstatustypes.mshipStatusType_Id='6')"

，但我希望具有付款方式的会员详细信息是“现金和卡”（两种类型的会员） ）

任何人都可以帮助解决此查询...

我正在使用 MySQL

非常感谢........

修改后的查询

                       SELECT members.member_Id, members.member_Lastname, members.member_Firstname, members.member_PostCode,
          members.member_Reference,  members.member_Dob,30*memberToMship_ChargePerPeriod/DateDiff(memberToMship_EndDate, memberToMship_StartDate) As monthly_amount, mshiptypes.mshipType_Name, mshipstatustypes.mshipStatusType_Name,
          membertomships.memberToMship_EndDate,
          IF(mshipOption_Period='year', TIMESTAMPDIFF (YEAR,memberToMship_StartDate, memberToMship_EndDate),
          TIMESTAMPDIFF (MONTH ,memberToMship_StartDate,memberToMship_EndDate ) ) *memberToMship_ChargePerPeriod As Total
        FROM members
         INNER JOIN membertomships ON membertomships.member_Id = members.member_Id
        INNER JOIN mshipstatustypes ON mshipstatustypes.mshipStatusType_Id = membertomships.mshipStatusType_Id
        INNER JOIN mshipoptions ON mshipoptions.mshipOption_Id = membertomships.mshipOption_Id
        INNER JOIN mshiptypes ON mshiptypes.mshipType_Id = mshipoptions.mshipType_Id
        WHERE membertomships.memberToMship_PayMethod IN ('Card','Card','Cheque')
        AND members.member_Active LIKE 'y%'
        AND ( mshipstatustypes.mshipStatusType_Id='2' OR  mshipstatustypes.mshipStatusType_Id = '3'
          OR mshipstatustypes.mshipStatusType_Id='6');

，但它没有显示正确的值..

嗨，但有一个问题，我有 6 名卡会员和 3 名现金会员它显示 6 和 3，但是当组合（卡和现金）时，它只会显示 4 .但必须显示（6+3）我不知道这是怎么回事哟请告诉任何解决方案。

Answer 1

假设支付方式只有现金或银行卡，则可以省略 where 子句 membertomships.memberToMship_PayMethod='Card'

如果还有其他支付方式，则可以将其更改为

membertomships.memberToMship_PayMethod （'卡'，'现金'）

Answer 2

使用 OR 或 IN

WHERE membertomships.memberToMship_PayMethod IN ('Card','Cash')

WHERE (membertomships.memberToMship_PayMethod ='Card' OR membertomships.memberToMship_PayMethod ='Cash')