Oracle SQL 合并两个查询

2024-05-10 • 问答

我有这个查询：

with rws as (
    select c.ID as P_ID,c.FIRST_NAME || ' ' || c.LAST_NAME as PATIENT,to_char(s.SESSION_DATE,'MON-YYYY') as SESSION_MONTH,'DD-MM-YYYY') as SESSION_DATE,row_number () over (
            partition by CLIENTS_ID
            order by c.ID asc
        ) rn
    from   SESSIONS s inner join CLIENTS c
    on c.ID = s.CLIENTS_ID
    where c.actIVE = 2
)
  select * from rws
  where  rn <= 1
  order  by P_ID asc;

结果如下：

我也有这个查询：

select c.ID as P_ID,count(s.Id) as Qty,sum(s.AMOUNT) as PAID,sum(s.PRICE-s.AMOUNT) as Dif
from SESSIONS s inner join CLIENTS c
on c.ID = s.CLIENTS_ID
where c.actIVE =2
and s.STATUS = 5
group by c.ID,c.FIRST_NAME || ' ' || c.LAST_NAME
order by P_ID;

结果如下：

两个查询的患者 ID 相同。是否可以将这两个查询合二为一并得到这个结果？

感谢@Sam Ware，这是正确的查询：

with rws as (
    select p.ID as P_ID,p.FIRST_NAME || ' ' || p.LAST_NAME as PATIENT,row_number () over (
            partition by CLIENTS_ID
            order by p.ID asc
        ) rn
    from   SESSIONS s inner join CLIENTS p
    on p.ID = s.CLIENTS_ID
    where p.actIVE = 2
),transactions as (
    select p.ID as P_ID,sum(s.PRICE-s.AMOUNT) as Dif
    from SESSIONS s inner join CLIENTS p
        on p.ID = s.CLIENTS_ID
    where p.actIVE =2
        and s.STATUS = 5
    group by p.ID
    order by P_ID
)
  select r.*,t.Qty,t.PAID,t.Dif
  from rws r
  inner join transactions t
    on r.p_id = t.p_id
  where r.rn <= 1
  order by r.P_ID asc;

with rws as ( select c.ID as P_ID,c.FIRST_NAME || ' ' || c.LAST_NAME as PATIENT,to_char(s.SESSION_DATE,'MON-YYYY') as SESSION_MONTH,'DD-MM-YYYY') as SESSION_DATE,row_number () over ( partition by CLIENTS_ID order by c.ID asc ) rn from SESSIONS s inner join CLIENTS c on c.ID = s.CLIENTS_ID where c.ACTIVE = 2 ),paid_stats as ( select c.ID as P_ID,count(s.Id) as Qty,sum(s.AMOUNT) as PAID,sum(s.PRICE-s.AMOUNT) as Dif from SESSIONS s inner join CLIENTS c on c.ID = s.CLIENTS_ID where c.ACTIVE =2 and s.STATUS = 5 group by c.ID,c.FIRST_NAME || ' ' || c.LAST_NAME order by P_ID ),select rws.*,paid_stats.Qty,paid_stats.PAID,paid_stats.Dif from rws join paid_stats on rws.p_id = paid.p_id where rn <= 1 order by P_ID asc;

with rws as ( select c.ID as P_ID,ROW_NUMBER() OVER (PARTITION BY CLIENTS_ID ORDER BY c.ID ASC) AS rn,COUNT(CASE s.STATUS WHEN 5 THEN s.ID) OVER (PARTITION BY c.ID) AS Qty,SUM(CASE s.STATUS WHEN 5 THEN s.AMOUNT) OVER (PARTITION BY c.ID) AS Paid,SUM(CASE s.STATUS WHEN 5 THEN s.PRICE - s.AMOUNT) OVER (PARTITION BY c.ID) AS DIF from SESSIONS s inner join CLIENTS c on c.ID = s.CLIENTS_ID where c.ACTIVE = 2 ) SELECT * FROM rws WHERE rn = 1 ORDER BY P_ID asc;

Oracle SQL 合并两个查询

ABCEFGOPQ 回答：Oracle SQL 合并两个查询

大家都在问