mysql - 同一查询但条件不同的 2 个计数

Question

这件事困扰了我一整天:

我想在 1 个表中进行 2 次计数。第一个计算总共有多少个商店，第二个应该计算价格低于 tsuppliers.lowestprice 的商店总数

所以基本上我想将第二个查询合并到第一个查询中:

SELECT tshops.shopID, tshops.OfficialName, tsuppliershopinfo.ContactName,   tsuppliershopinfo.ContactMail, Count(distinct tresults.pID) AS AantalVanpID
FROM (tsupplierproducts 
    INNER JOIN tresults ON tsupplierproducts.pID = tresults.pID)     
    INNER JOIN (tsuppliershopinfo INNER JOIN tshops ON tsuppliershopinfo.shopID = tshops.shopID) ON tresults.shopID = tsuppliershopinfo.shopID
WHERE (((tsupplierproducts.supplierID)=2)) AND tresults.starttime BETWEEN DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND DATE_ADD(CURDATE(),INTERVAL 1 DAY)
GROUP BY tshops.shopID, tshops.OfficialName, tsuppliershopinfo.ContactName, tsuppliershopinfo.ContactMail;

第二个(请注意，where 子句中只有一个额外条件):

SELECT tresults.shopID, Count(distinct tresults.pID) AS AantalVanpID
FROM tsupplierproducts INNER JOIN tresults ON tsupplierproducts.pID = tresults.pID
WHERE (((tsupplierproducts.supplierID)=2) AND ((tresults.Price)<tsupplierproducts.LowestPrice)) AND tresults.starttime BETWEEN DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND DATE_ADD(CURDATE(),INTERVAL 1 DAY)
GROUP BY tresults.shopID;

如何将第二个查询合并到第一个查询中？

谢谢!

Answer 1

SELECT 
    tshops.shopID, 
    tshops.OfficialName, 
    tsuppliershopinfo.ContactName,   
    tsuppliershopinfo.ContactMail, 
    Count(distinct tresults.pID) AS AantalVanpID,
    Count(distinct 
        case when tresults.Price < tsupplierproducts.LowestPrice then tresult.pID end
        ) as AantalVanpID_2
FROM
    tsupplierproducts 
    INNER JOIN tresults ON tsupplierproducts.pID = tresults.pID   
    INNER JOIN tsuppliershopinfo ON tresults.shopID = tsuppliershopinfo.shopID
    INNER JOIN tshops ON tsuppliershopinfo.shopID = tshops.shopID
WHERE 
    tsupplierproducts.supplierID = 2
    AND 
    tresults.starttime BETWEEN 
        DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND DATE_ADD(CURDATE(),INTERVAL 1 DAY)
GROUP BY 
    tshops.shopID, 
    tshops.OfficialName, 
    tsuppliershopinfo.ContactName, 
    tsuppliershopinfo.ContactMail