Oracle SQL连接三个表并按最高值进行排序

Question

我有以下三张表：

CREATE TABLE CUSTOMERS
(customerID     INT     PRIMARY KEY,
customerZip     VARCHAR(15) NOT NULL);

CREATE TABLE VEHICLES
(vehicleVIN     VARCHAR(25) PRIMARY KEY,
vehicleModel        VARCHAR(15) NOT NULL);

CREATE TABLE SALES
(saleID         INT     PRIMARY KEY,
customerID      INT,
vehicleVIN      VARCHAR(25),
CONSTRAINT SALES_FK1 FOREIGN KEY (customerID) REFERENCES CUSTOMERS(customerID),
CONSTRAINT SALES_FK2 FOREIGN KEY (vehicleVIN) REFERENCES VEHICLES(vehicleVIN));

我正在尝试开发一个查询来连接这三个表，并生成一个输出，该输出按模型显示销售总量，并按邮政编码显示销售总量，首先按最高值排序。输出应该类似于以下内容：

vehicleModel    Sales_By_Model    customerZip   Sales_By_ZIP
        S-10                12          18956              3
   Silverado                10          22789              2
       F-150                 9          12345              2

我尝试了以下代码，但我不认为这是正确的方法，因为它没有将vehicleModel与Sales_By_Model的总数组合在一起：

SELECT DISTINCT v.vehicleModel, COUNT(*) OVER (PARTITION BY s.vehicleVIN) "SALES_BY_MODEL", c.customerZip, COUNT(*) OVER (PARTITION BY c.customerZip )"SALES_BY_ZIP" 
FROM SALES s, VEHICLES v, CUSTOMERS c
WHERE s. vehicleVIN = v. vehicleVIN
and  c. customerID = s. customerID    
ORDER BY 2 DESC , 4 DESC;

这是我从上面所做的查询中得到的输出：

VEHICLEMODEL    SALES_BY_MODEL CUSTOMERZIP     SALES_BY_ZIP
accord                       1 89523                      6
altima                       1 89523                      6
escalade                     1 89523                      6
f-150                        1 89523                      6
impala                       1 89523                      6

尽管查询确实会提取必要的数据并连接表，但它不会组合vehicleModel(s)来增加SALES_BY_MODEL的计数。同样的情况也发生在customerZIP上，尽管它似乎至少正确地计算了拉链。

我的问题是:是否有一种方法可以将vehicleModel和customerZip的计数结合起来查询这些表？如果是的话，我该怎么做呢？通过显式调用JOIN，我知道上面使用的查询不是完全正确的SQL语法。我的目标是在一个选择查询中完成这个任务。

提前感谢您的指导/协助！

编辑:下面是几行示例输入数据：

INSERT INTO CUSTOMERS
(customerID,customerFirName,customerLasName,customerMiName,customerStreet,customerState,customerCity,customerZip)
VALUES
(1,'Steven','Christman','J','11111 Address Way','Maryland','Hollywood','20636');
INSERT INTO CUSTOMERS
(customerID,customerFirName,customerLasName,customerMiName,customerStreet,customerState,customerCity,customerZip)
VALUES
(2,'Bob','Seagram','A','22222 Seagram Lane','Texas','Houston','77001');
INSERT INTO CUSTOMERS
(customerID,customerFirName,customerLasName,customerMiName,customerStreet,customerState,customerCity,customerZip)
VALUES
(3,'Sally','Anderson','P','33333 Pheonix Drive','Arizona','Pheonix','85001');

INSERT INTO VEHICLES
(vehicleVIN,vehicleType,vehicleMake,vehicleModel,vehicleWhereFrom,vehicleWholesaleCost,vehicleTradeID)
VALUES
('147258HHE91K3RT','compact','chevrolet','spark','Maryland',20583.00,NULL);
INSERT INTO VEHICLES
(vehicleVIN,vehicleType,vehicleMake,vehicleModel,vehicleWhereFrom,vehicleWholesaleCost,vehicleTradeID)
VALUES
('789456ERT0923RFB6','Midsize','ford','Taurus','washington, d.c.',25897.22,1);
INSERT INTO VEHICLES
(vehicleVIN,vehicleType,vehicleMake,vehicleModel,vehicleWhereFrom,vehicleWholesaleCost,vehicleTradeID)
VALUES
('1234567890QWERTYUIOP','fullsize','Lincoln','towncar','Virginia',44222.10,NULL);

INSERT INTO SALES
(saleID,grossSalePrice,vehicleStatus,saleDate,saleMileage,customerID,salespersonID,vehicleVIN)
VALUES
(1,25987.28,'sold',date '2012-10-15',10,1,1,'147258HHE91K3RT');
INSERT INTO SALES
(saleID,grossSalePrice,vehicleStatus,saleDate,saleMileage,customerID,salespersonID,vehicleVIN)
VALUES
(2,29999.99,'sold',date '2012-10-17',50087,2,2,'789456ERT0923RFB6');
INSERT INTO SALES
(saleID,grossSalePrice,vehicleStatus,saleDate,saleMileage,customerID,salespersonID,vehicleVIN)
VALUES
(3,47490.88,'sold',date '2012-11-05',30,3,3,'1234567890QWERTYUIOP');

正如您可能注意到的，上面的输入数据反映了一些其他属性的输入，而我在最初的文章中没有列出这些属性(我在其中显示了表创建)，因为这些属性中的大多数与这里的目标无关。

Answer 1

实现这一点的一个示例方法表明，您实际上正在运行两个独立的查询，只是假装它们是相关的。

这样做会增加执行成本，降低测试和/或维护的能力，防止代码重用，并且通常违背我能想到的每一个相关的软件工程原则。

所以，这是如何做一件非常糟糕的事情..。

WITH
  ranked_model_sales AS
(
  SELECT
    vehicles.vehicleModel,
    COUNT(*)                                     AS total_model_sales,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC)   AS rank_id
  FROM
    sales
  INNER JOIN
    vehicles
      ON sales.vehicleVIN = vehicles.vehicleVIN
  GROUP BY
    vehicles.vehicleModel
),
  ranked_zip_sales AS
(
  SELECT
    customers.customerZip,
    COUNT(*)                                     AS total_zip_sales,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC)   AS rank_id
  FROM
    sales
  INNER JOIN
    customers
      ON sales.customerID= customers.customerID
  GROUP BY
    customers.customerZip
)
SELECT
  m.vehicleModel,
  m.total_model_sales,
  z.customerZip,
  z.total_zip_sales
FROM
  ranked_model_sales    m
FULL OUTER JOIN
  ranked_zip_sales      z
    ON m.rank_id = z.rank_id
ORDER BY
  COALESCE(m.rank_id, z.rank_id)