UNION问题

[wobushilegend]

SELECT date_format(created_at,'%Y-%m-%d') AS 时间,count(0) 注册人数 FROM mt4_agm.mt4_marketing_center AS a RIGHT JOIN mt4_agm.mt4_users_info AS b ON a.account = b.email LEFT JOIN mt4_agm.mt4_option_relation AS c ON b.account = c.mt4_account
WHERE DATEDIFF(created_at,NOW())<=-1 AND DATEDIFF(created_at,NOW())>=-10
GROUP BY 时间

Union ALL

SELECT date_format(updated_at,'%Y-%m-%d') AS 时间,count(0) 认证人数 FROM mt4_agm.mt4_marketing_center AS c RIGHT JOIN mt4_agm.mt4_users_info AS d ON c.account = d.email LEFT JOIN mt4_agm.mt4_option_relation AS e ON d.account = e.mt4_account
WHERE DATEDIFF(updated_at,NOW())<=-1 AND DATEDIFF(updated_at,NOW())>=-10
GROUP BY 时间



第一个select查了时间和注册人数
第二个select查了时间和认证人数
想变成成种效果怎么改

[二月十六]

试试这样

SELECT  t1.时间 ,
        t1.注册人数 ,
        t2.认证人数
FROM    ( SELECT    date_format(created_at, '%Y-%m-%d') AS 时间 ,
                    COUNT(0) 注册人数
          FROM      mt4_agm.mt4_marketing_center AS a
                    RIGHT JOIN mt4_agm.mt4_users_info AS b ON a.account = b.email
                    LEFT JOIN mt4_agm.mt4_option_relation AS c ON b.account = c.mt4_account
          WHERE     DATEDIFF(created_at, NOW()) <= -1
                    AND DATEDIFF(created_at, NOW()) >= -10
          GROUP BY  时间
        ) t1
        JOIN ( SELECT   date_format(updated_at, '%Y-%m-%d') AS 时间 ,
                        COUNT(0) 认证人数
               FROM     mt4_agm.mt4_marketing_center AS c
                        RIGHT JOIN mt4_agm.mt4_users_info AS d ON c.account = d.email
                        LEFT JOIN mt4_agm.mt4_option_relation AS e ON d.account = e.mt4_account
               WHERE    DATEDIFF(updated_at, NOW()) <= -1
                        AND DATEDIFF(updated_at, NOW()) >= -10
               GROUP BY 时间
             ) t2 ON t1.时间 = t2.时间

[吉普赛的歌]

--#1版主基础上稍改了一下，因为如果哪天只有注册没有认证或者只有认证没有注册就会遗漏了
SELECT  CASE WHEN t1.时间 is not null then t1.时间 else t2.时间 end as [时间],
        ISNULL(t1.注册人数,0) AS 注册人数 ,
        ISNULL(t2.认证人数,0) AS 认证人数
FROM    ( SELECT    date_format(created_at, '%Y-%m-%d') AS 时间 ,
                    COUNT(0) 注册人数
          FROM      mt4_agm.mt4_marketing_center AS a
                    RIGHT JOIN mt4_agm.mt4_users_info AS b ON a.account = b.email
                    LEFT JOIN mt4_agm.mt4_option_relation AS c ON b.account = c.mt4_account
          WHERE     DATEDIFF(created_at, NOW()) <= -1
                    AND DATEDIFF(created_at, NOW()) >= -10
          GROUP BY  时间
        ) t1
        FULL JOIN ( SELECT   date_format(updated_at, '%Y-%m-%d') AS 时间 ,
                        COUNT(0) 认证人数
               FROM     mt4_agm.mt4_marketing_center AS c
                        RIGHT JOIN mt4_agm.mt4_users_info AS d ON c.account = d.email
                        LEFT JOIN mt4_agm.mt4_option_relation AS e ON d.account = e.mt4_account
               WHERE    DATEDIFF(updated_at, NOW()) <= -1
                        AND DATEDIFF(updated_at, NOW()) >= -10
               GROUP BY 时间
             ) t2 ON t1.时间 = t2.时间

[文盲老顾]

select 时间,sum(注册) as 注册人数,sum(认证) as 认证人数 from (
	SELECT date_format(updated_at,'%Y-%m-%d') AS 时间
	,(case when DATEDIFF(created_at,NOW())<=-1 AND DATEDIFF(created_at,NOW())>=-10 then 1 else 0 end) as 注册
	,(case when DATEDIFF(updated_at,NOW())<=-1 AND DATEDIFF(updated_at,NOW())>=-10 then 1 else 0 end) as 认证
	FROM mt4_agm.mt4_marketing_center AS c 
	RIGHT JOIN mt4_agm.mt4_users_info AS d ON c.account = d.email 
	LEFT JOIN mt4_agm.mt4_option_relation AS e ON d.account = e.mt4_account 
) a
group by 时间

如果运算量不大，或者数据不多，都不需要union，直接运算即可