代码之家 › 专栏 › 技术社区 › ryvantage

从两个表合并数据的sql查询

oracle sql

ryvantage · 技术社区 · 6 年前

我正试着(为甲骨文)写一个查询。我有三张桌子:

sale
====================================
sale_id | part_id | amt | cost | qty

quote
==============================
quote_id | part_id | amt | qty

part
================
part_id | number

我需要编写一个查询,该查询按零件号对编号进行分组,类似于此(非功能性)查询:

select p.number 

sum(s.amt * s.qty) as sales_amt_total,
sum(s.qty) as sales_qty_total,
count(s.sale_id) as sales_count,
sum(s.qty * s.cost) as cost_total,

sum(q.amt * q.qty) as quotes_amt_total,
sum(q.qty) as quotes_qty_total,
count(q.quote_id) as quotes_count

from parts p
inner join quotes q on q.part_id = p.part_id
inner join sales s on s.part_id = p.part_id

group by p.number

having sales_amt_total < ?
and sales_amt_total > ?
and sales_qty_total < ?
and sales_qty_total > ?
and sales_count < ?
and sales_count > ?
and cost_total < ?
and cost_total > ?

and quotes_amt_total < ?
and quotes_amt_total > ?
and quotes_qty_total < ?
and quotes_qty_total > ?
and quotes_count < ?
and quotes_count > ?

;

产生这样的结果:

number | sales_amt_total | sales_qty_total | sales_count | cost_total | ...
---------------------------------------------------------------------------
    P1 |         9999999 |         9999999 |     9999999 |    9999999 | ...
    P2 |         9999999 |         9999999 |     9999999 |    9999999 | ...
    P3 |         9999999 |         9999999 |     9999999 |    9999999 | ...

然而, inner join quotes 和 inner join sales 创建膨胀的数字,因为它每次都在计算每个匹配项。

我可以把这分成两个问题:

select p.number 

sum(s.amt * s.qty) as sales_amt_total,
sum(s.qty) as sales_qty_total,
count(s.sale_id) as sales_count,
sum(s.qty * s.cost) as cost_total,

from parts p
inner join sales s on s.part_id = p.part_id

having sales_amt_total < ?
and sales_amt_total > ?
and sales_qty_total < ?
and sales_qty_total > ?
and sales_count < ?
and sales_count > ?
and cost_total < ?
and cost_total > ?
;

select p.number 

sum(q.amt * q.qty) as quotes_amt_total,
sum(q.qty) as quotes_qty_total,
count(q.quote_id) as quotes_count

from parts p
inner join quotes q on q.part_id = p.part_id

group by p.number

having quotes_amt_total < ?
and quotes_amt_total > ?
and quotes_qty_total < ?
and quotes_qty_total > ?
and quotes_count < ?
and quotes_count > ?

;

但我失去了应用 having 各表的条款。

如何创建此查询?

1 回复 | 直到 6 年前

Bob Jarvis - Ð¡Ð»Ð°Ð²Ð° Ð£ÐºÑÐ°ÑÐ½Ñ 6 年前

大致如下:

WITH ctePART_QUOTES AS
       (SELECT qq.PART_ID,
               COUNT(*) AS QUOTES_COUNT
               SUM(qq.AMT * qq.QTY) AS QUOTES_AMT_TOTAL,
               SUM(qq.QTY) as QUOTES_QTY_TOTAL
          FROM QUOTES qq
          GROUP BY qq.PART_ID),
     ctePART_SALES AS
       (SELECT ss.PART_ID,
               COUNT(*) AS SALES_COUNT,
               SUM(ss.AMT * ss.QTY) AS SALES_AMT_TOTAL,
               SUM(ss.QTY) AS SALES_QTY_TOTAL,
               COUNT(*) AS SALES_COUNT,
               SUM(ss.QTY * ss.COST) AS COST_TOTAL
          FROM SALES ss
          GROUP BY ss.PART_ID)
SELECT p.NUMBER,
       s.SALES_AMT_TOTAL,
       s.SALES_QTY_TOTAL,
       s.SALES_COUNT,
       s.COST_TOTAL,
       q.QUOTES_AMT_TOTAL,
       q.QUOTES_QTY_TOTAL,
       q.QUOTES_COUNT
  FROM PARTS p
  LEFT OUTER JOIN ctePART_QUOTES q
    ON q.PART_ID = p.PART_ID
  LEFT OUTER JOIN ctePART_SALES s
    ON s.PART_ID = p.PART_ID

添加所有 HAVING 东西,你应该走了。

祝你好运。