SQL에서 동일한 쿼리에 여러 뷰를 만든 다음 이 뷰를 결합하려면 어떻게 해야 합니까?

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

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

하위 쿼리에 "가입"해야 합니다.

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)

일부 다른 측정값을 공통 속성으로 "풀"해야 할 때는 참여하지 마십시오.사용하다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

id | t1_val | t2_val | t3_val-: | -----: | -----: | -----:1 | 30 | 10 | null2 | 30 | null | 303 |     49 |     50 |     495 | null | 49 | null4 | null | null | 106 | null | null | 20

db<>여기로 이동

언급URL : https://stackoverflow.com/questions/70659324/how-do-i-create-multiple-views-in-the-same-query-in-sql-and-then-join-them