count计算的时候排除null
if判断为真的时候,显示第一个结果,
isnull,当null的时候返回1,非null返回0
SELECT
count( IF ( iway2 = cway2 AND iway2 = 1, 1, NULL ) ) result
FROM
(
SELECT
bi.interincome_way iway,
c.id AS id,
c.interincome_way cway,
c.`status`,
ISNULL( bi.interincome_way ) AS iway2,
ISNULL( c.interincome_way ) AS cway2
FROM
jmall_product_base_info bi
LEFT JOIN jmall_product_specification_combine c ON c.product_id = bi.id
WHERE
c.`status` = 1
AND c.is_delete = 0
AND bi.brand_merchant_id = ?
AND bi.merchant_no = ?
AND bi.merchant_combo_no = ?
) detail
上面的count太复杂,性能不好,优化
SELECT
count( id ) result
FROM
(
SELECT
bi.interincome_way iway,
c.id AS id,
c.interincome_way cway,
c.`status`,
ISNULL( bi.interincome_way ) AS iway2,
ISNULL( c.interincome_way ) AS cway2
FROM
jmall_product_base_info bi
LEFT JOIN jmall_product_specification_combine c ON c.product_id = bi.id
WHERE
c.`status` = 1
AND c.is_delete = 0
) detail
WHERE
iway2 = 1
AND cway2 = 1