如何在SQL的同一查詢中創建多個視圖，然后加入它們？

這是我正在使用的查詢。我需要加入三個視圖來計算每月的總收入。我應該如何進行？

With Txn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE), '%y-%m') as Month, Sum(netPrice/100) as TransactionRevenue from transactions 
group by Month)

With Leaves as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval -1 MONTH), '%y-%m') as Month, sum(amount/100) as LeaveRevenue from driverPaymentTransactions
group by Month)

With Sxn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE ), '%y-%m') as Month, sum(amount/100) as SubscribedRevenue from subscribedDriversDailyRevenues
group by MONTH) 

Select * from Txn t
join Leaves l on t.Month = l.month
join Sxn s on t.month = s.month

uj5u.com熱心網友回復：

With Txn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE), '%y-%m') as Month, Sum(netPrice/100) as TransactionRevenue from transactions 
group by Month),
Leaves as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval -1 MONTH), '%y-%m') as Month, sum(amount/100) as LeaveRevenue from driverPaymentTransactions
group by Month),
Sxn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE ), '%y-%m') as Month, sum(amount/100) as SubscribedRevenue from subscribedDriversDailyRevenues
group by MONTH) 
Select * from Txn t
join Leaves l on t.Month = l.month
join Sxn s on t.month = s.month

uj5u.com熱心網友回復：

您需要“加入”子查詢

CREATE VIEw myview 
AS (With Txn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE), '%y-%m') as Month, Sum(netPrice/100) as TransactionRevenue from transactions 
group by Month)
, Leaves as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval -1 MONTH), '%y-%m') as Month, sum(amount/100) as LeaveRevenue from driverPaymentTransactions
group by Month)
, Sxn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE ), '%y-%m') as Month, sum(amount/100) as SubscribedRevenue from subscribedDriversDailyRevenues
group by MONTH) 

Select * from Txn t
join Leaves l on t.Month = l.month
join Sxn s on t.month = s.month)

uj5u.com熱心網友回復：

當您需要將一些不同的度量“拉”到公共屬性時，請不要加入。使用union all，您不需要關心最完整的組值來源：

create table t1 as
select 1 as id, 10 as val union all
select 1, 20 union all
select 2, 30 union all
select 3, 49

create table t2 as
select 1 as id, 10 as val union all
select 3, 20 union all
select 3, 30 union all
select 5, 49

create table t3 as
select 4 as id, 10 as val union all
select 6, 20 union all
select 2, 30 union all
select 3, 49

with u as (
  select
    id
    , val as t1_val
    , cast(null as decimal) as t2_val
    , cast(null as decimal) as t3_val
  from t1

  union all

  select
    id
    , null as t1_val
    , val as t2_val
    , null as t3_val
  from t2

  union all

  select
    id
    , null as t1_val
    , null as t2_val
    , val as t3_val
  from t3
)
select
  id
  , sum(t1_val) as t1_val
  , sum(t2_val) as t2_val
  , sum(t3_val) as t3_val
from u
group by id

編號 | t1_val | t2_val | t3_val
-: | -----: | -----: | -----：
 1 | 30 | 10 |   空
 2 | 30 |   空| 30
 3 | 49 | 50 | 49
 5 |   空| 49 |   空
 4 |   空|   空| 10
 6 |   空|   空| 20

db<>在這里擺弄

uj5u.com熱心網友回復：

有一些例子：https : //www.sqlshack.com/sql-multiple-joins-for-beginners-with-examples/ 檢查它們

標籤：

上一篇：我如何找到最小值\最大值Numpy陣列

下一篇：獲取分箱時間間隔的最大值