java - 如何连接同一个表中的两列

Question

SELECT CAST(`last_charged_date`  AS DATE) AS Date_time, COUNT(*) AS 

Charged_Count, SUM( last_charge_amt) AS 来自subscriber GROUP BY CAST的收入（截至last_charged_date 日期）

SELECT CAST( created_dateAS DATE) AS Date_time, COUNT(*) AS Registered_Count FROM subscriber GROUP BY CAST( created_dateAS DATE)

我想加入last_charged_date并created_date作为一列，需要根据日期将 Charged_Count、Revenue 和 Registered_Count 显示为单独的列

标签： javamysqlsqldatabase

Answer 1

您可以使用union all：

select date, 
       sum ( col = 'last_charged' ) as Charged_Count,
       sum (case when col = 'last_charged' then Revenue else 0 end ) as Revenue,
       sum ( col = 'created_date' ) as Registered_Count
from (select CAST(s.`last_charged_date`  AS DATE) as date, 
             s.`last_charge_amt` as Revenue, 'last_charged' as col 
      from `subscriber` s union all
      select CAST(s1.`created_date`  AS DATE) as created_date, 
             0, 'created_date' as col 
      from `subscriber` s1
    ) t
group by date;