mysql - 具有多个 where 条件的 SQL 多重选择

Question

我有简单的表格

ID   usr_id       card_amount     state     created
----------------------------------------------------------------
1    a1          10.000            2      2014-03-13 14:33:39
2    a2          30.000            2      2014-03-11 14:33:39
3    a3          50.000            1      2014-03-10 14:33:39
4    a4          20.000            2      2014-04-13 14:33:39
5    a5          40.000            2      2014-03-19 14:33:39
----------------------------------------------------------------

我有这个查询，但需要很长时间!帮助我解决问题，使其更快

SELECT 
    DATE_FORMAT(`created`, '%Y-%m-%d') AS `key_date`,       
    (
        SELECT 
            SUM(`card_amount`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` = 2 AND  `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `totalamount`,
    (
        SELECT 
            COUNT(`id`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` =2 AND `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `turncharge`,
    (
        SELECT 
            COUNT(DISTINCT `usr_id`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` =2 AND `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `pu`,
    (
        SELECT 
            SUM(`card_amount`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `totalamount7`,            
    (
        SELECT 
            COUNT(`id`) 
        FROM `payment` WHERE `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY)  
    ) AS `turncharge7`,             
    (
        SELECT
            COUNT(DISTINCT `usr_id`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `pu7`,
    (
        SELECT 
            SUM(`card_amount`) 
        FROM `payment` WHERE `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `totalamount30`,           
    (
        SELECT 
            COUNT(`id`) 
        FROM `payment` WHERE `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `turncharge30`,            
    (
        SELECT
            COUNT(DISTINCT `usr_id`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `pu30`
FROM `payment`          
WHERE MONTH(`created`)=$month AND YEAR(`created`)=$year         
GROUP BY `key_date`         
ORDER BY `key_date` DESC

我使用 LEFT JOIN 但它没有给出我需要的内容:c1.totalamount = c2 Totalmount7, c1.turncharge =c2.turncharge7 ........(不对)

SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `key_date`,c1.totalamount,c1.turncharge,c1.pu,c2.totalamount7,c2.turncharge7,c2.pu7
FROM `payment` 

LEFT JOIN (SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `date1`,SUM(`card_amount`) AS `totalamount`, COUNT(`id`) AS `turncharge`,COUNT(DISTINCT `usr_id`) AS `pu` 
FROM `payment` 
WHERE `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_FORMAT(`created`, '%Y-%m-%d')
AND `created` < DATE_SUB(DATE_FORMAT(`created`, '%Y-%m-%d'),INTERVAL -1 DAY)  GROUP BY date1) AS c1 ON date1 = DATE_FORMAT(`created`, '%Y-%m-%d')

LEFT JOIN (SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `date2`,SUM(`card_amount`) AS `totalamount7`, COUNT(`id`) AS `turnchargep7`,COUNT(DISTINCT `usr_id`) AS `pu7` 
FROM `payment` 
WHERE `card_amount`> 0 AND `state` = 2 AND  created >= DATE_SUB(DATE_FORMAT(`created`, '%Y-%m-%d'),INTERVAL 6 DAY)   
AND `created` < DATE_SUB(DATE_FORMAT(`created`, '%Y-%m-%d'),INTERVAL -1 DAY) GROUP BY date2) AS c2 ON date2 = DATE_FORMAT(`created`, '%Y-%m-%d')

        WHERE MONTH(`created`)=4 AND YEAR(`created`)=2014
        GROUP BY `key_date`
        ORDER BY `key_date` DESC 

谢谢姆拉登·普拉吉迪奇我使用 CASE 表达式，它运行得更快

SELECT date.key_date,
SUM(case when `card_amount`> 0 AND `state` = 2 AND  `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) then `card_amount` else 0 end) AS totalamount,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` =2 AND `created` >= date.`key_date` AND `created` < DATE_SUB(date.`key_date`,INTERVAL -1 DAY) then payment.`id` else 0 end)) AS `turncharge`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` =2 AND `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY)  then `usr_id` else 0 end) )AS `pu` ,

SUM(case when `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) then `card_amount` else 0 end) AS `totalamount7`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(date.`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(date.`key_date`,INTERVAL -1 DAY)  then payment.`id` else 0 end)) AS `turncharge7`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(date.`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(date.`key_date`,INTERVAL -1 DAY)  then `usr_id` else 0 end) )AS `pu7` ,


SUM(case when `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) then `card_amount` else 0 end) AS `totalamount30`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(date.`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(date.`key_date`,INTERVAL -1 DAY)  then payment.`id` else 0 end)) AS `turncharge30`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY)  then `usr_id` else 0 end) )AS `pu30` 

FROM `payment`,(SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `key_date` FROM `payment` 
WHERE MONTH(`created`)=3 AND YEAR(`created`)=2014
GROUP BY `key_date`) AS date

GROUP BY date.key_date
ORDER BY date.key_date DESC

Answer 1

使用 case 语句重写相关子查询。这将单次遍历您的表。本文如何展示:
Rewriting correlated sub-queries with CASE expressions