mysql - 试图同时获得卡和现金成员(member)

Question

您好，我这样做是为了获取成员(member)的付款方式类型现金的详细信息

       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')"

和另一个类似的查询，用于使用支付方式类型卡获取成员(member)详细信息

                               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')"

但我想要成员(member)详细信息那些有付款方式的人是“现金和卡”(两种类型的成员(member))

有人会帮助解决这个问题吗...

我正在使用 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 名持卡成员(member)和 3 名现金成员(member)，分别显示 6 和 3，但当合并(卡和现金)时，它只会显示 4。但必须显示 (6+ 3) 我不知道怎么会这样请告诉任何解决方案。

Answer 1

假设唯一的支付类型是现金或卡，您可以省略 where 子句 membertomships.memberToMship_PayMethod='Card'

如果有其他支付方式，可以改成

membertomships.memberToMship_PayMethod in ('Card', 'Cash')