子查询和连接sql

wdebmtf2 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(302)

我想找出哪家制造商的车辆涉及最多的销售，导致2个或更多的产品点。并显示制造商名称和制造商车辆涉及的销售总数。

CREATE TABLE product (
    prod_code          NUMBER(3) NOT NULL,
    prod_description   VARCHAR2(75) NOT NULL,
    prod_points        NUMBER(2) NOT NULL
);

CREATE TABLE product_sale (
        sale_no         NUMBER(8) NOT NULL,
        sale_datetime   DATE NOT NULL,
        sale_location   VARCHAR2(100) NOT NULL,
        prod_code       NUMBER(3) NOT NULL,
        officer_id     NUMBER(8) NOT NULL,
        lic_no   CHAR(10) NOT NULL,
        veh_no  CHAR(17) NOT NULL
    );

CREATE TABLE vehicle (
    veh_no         CHAR(17) NOT NULL,
    veh_manufyr     DATE NOT NULL,
    veh_maincolor   VARCHAR2(20) NOT NULL,
    veh_manufname   VARCHAR2(30) NOT NULL,
    veh_modname     VARCHAR2(20) NOT NULL,
    veh_type        CHAR(2) NOT NULL
);

我不知道如何关联3个不同的连接并找到输出。
预期产量：

sql Join count subquery oracle

来源：https://stackoverflow.com/questions/62263359/subquery-and-joins-sql

1条答案

按热度按时间

biswetbf1#

像这样的(（未测试）

SELECT veh_manufname, total_sales_count
FROM (
   SELECT v.veh_manufname, 
          count(*) as total_sales_count,
          count( CASE WHEN prod_code IN (
                     SELECT prod_code FROM product WHERE prod_points >= 2
                ) THEN 1 END ) as sales_point_2
   FROM product_sale p
   JOIN vehicle v ON v.veh_no = p.veh_no
   GROUP BY v.veh_manufname
)
ORDER BY sales_point_2 DESC
LIMIT 1
-- LIMIT 1 works on most databases: MySql, PostgreSql, SQLite ....
-- If you are using ORACLE then remove the above line and uncomment the below one
-- FETCH 1 ROWS ONLY

-- If you are using SQL Server then (probably) SELECT TOP 1 will work but I'am not sure

赞(0）回复(0）举报 2021-07-26

我来回答

子查询和连接sql

1条答案

相关问题

热门标签

最新问答