时间戳

首页 > 解决方案 > 带有计数条件的 SQL 查询

sql - 带有计数条件的 SQL 查询

问题描述

这是我的表结构：

CREATE TABLE CITY(
  CITY_ID NUMBER(3) CONSTRAINT CITY_ID_PK PRIMARY KEY,
  CITY_NAME VARCHAR2(20) CONSTRAINT CITY_NAME_NN NOT NULL);

CREATE TABLE PILOT(
  PILOT_ID NUMBER(3) CONSTRAINT PILOT_ID_PK PRIMARY KEY,
  LAST_NAME VARCHAR2(20) CONSTRAINT LAST_NAME_NN NOT NULL,
  FIRST_NAME VARCHAR2(20) CONSTRAINT FIRST_NAME_NN NOT NULL,
  CITY_ID NUMBER(3) CONSTRAINT CITY_ID_FK REFERENCES CITY(CITY_ID),
  SALARY NUMBER(7,2) CONSTRAINT SALARY_CK CHECK (SALARY >= 5000 AND SALARY <= 7000));

CREATE TABLE PLANE(
  PLA_ID NUMBER(2) CONSTRAINT PLANE_ID_PK PRIMARY KEY,
  PLA_DESC VARCHAR2(20) CONSTRAINT PLANE_DESC_NN NOT NULL,
  MAX_PASSENGER NUMBER(3),
  CITY_ID NUMBER(3) CONSTRAINT PLANE_CITY_ID_FK REFERENCES CITY(CITY_ID),
  CONSTRAINT MAX_PASSENGER_CK CHECK (MAX_PASSENGER <= 500));

CREATE TABLE FLIGHT(
  FLIGHT_ID NUMBER(3) CONSTRAINT FLIGHT_ID_PK PRIMARY KEY,
  PILOT_ID NUMBER(3) CONSTRAINT FLIGHT_PILOT_ID_FK REFERENCES PILOT(PILOT_ID),
  PLA_ID NUMBER(2) CONSTRAINT FLIGHT_PLA_ID_FK REFERENCES PLANE(PLA_ID),
  CITY_DEP NUMBER(3) CONSTRAINT FLIGHT_CITY_DEP_FK REFERENCES CITY(CITY_ID),
  CITY_ARR NUMBER(3) CONSTRAINT FLIGHT_CITY_ARR_FK REFERENCES CITY(CITY_ID),
  DEP_DATE DATE,
  DEP_TIME NUMBER(4),
  ARR_TIME NUMBER(4),
  CONSTRAINT ARR_TIME_CK CHECK (ARR_TIME > DEP_TIME));

我在这个实验室中遇到的问题是显示在蒙特利尔以外执行两次或更多航班的飞行员（ID 和姓名）（要求我在查询中使用城市名称而不是 ID）

到目前为止，这是我想出的：

SELECT PILOT_ID, LAST_NAME, FIRST_NAME
FROM PILOT
JOIN FLIGHT USING (PILOT_ID)
WHERE CITY_DEP=(SELECT CITY_ID
                FROM CITY
                WHERE CITY_NAME='MONTREAL')

显然，这让我得到了部分答案，但它并没有准确显示我需要的信息，这只是让这场战斗 >= 2 次的飞行员。

标签： sqloracle12c

解决方案

编辑了有关数据结构的新信息

了解你的目标

我相信我理解您的目标，即查询一天内至少离开蒙特利尔两次的飞行员的飞行员级别数据。

查询解决方案

如果我的假设是正确的，我相信您可以通过执行类似的操作来满足您的需求：

CREATE GLOBAL TEMPORARY TABLE flight_per_day ON COMMIT PRESERVE ROWS AS
SELECT
       p.pilot_id,
       f.dep_date,
       COUNT(CASE WHEN c.city_name = 'MONTREAL' THEN 1 ELSE NULL END) as 
        montreal_cnt
  FROM flights f
       LEFT JOIN pilot p ON p.pilot_id = f.pilot_id
       LEFT JOIN city c on f.city_dep = c.city_id
 GROUP BY 1, 2;


    SELECT
           p.pilot_id,
           p.first_name,
           p.last_name
      FROM flight_per_day fp
           LEFT JOIN pilot p ON p.pilot_id = fp.pilot_id
     WHERE fp.montreal_cnt>=2

或者没有临时表你可以做

SELECT
       p.pilot_id,
       p.first_name,
       p.last_name
  FROM
       (SELECT
              p.pilot_id,
              f.dep_date,
              -- Find the total number of flights (COUNT) where (CASE WHEN) a flight departs from Montreal (THEN) count it otherwise (ELSE) ignore it (NULL)
              COUNT(CASE WHEN c.city_name = 'MONTREAL' THEN 1 ELSE NULL END) as 
                montreal_cnt
         FROM flights f
              -- Join in pilot table to get the counts by pilot_id
              LEFT JOIN pilot p ON p.pilot_id = f.pilot_id
              -- Join in city table to get city_name instead of city_id
              LEFT JOIN city c on f.city_dep = c.city_id
        GROUP BY 1, 2) fp
       LEFT JOIN pilot p ON p.pilot_id = fp.pilot_id
-- Only give me the data for pilots who have flown out of Montreal at least twice in one day
 WHERE fp.montreal_cnt>=2

推荐阅读