如何比较按一列分组的SQL中的列值？

Question

我正在从一个汽车数据库中练习SQL，我有下面的汽车表/视图，我需要选择一个比引擎/摩托车卖得更多的品牌。这意味着我必须选择只有汽车类型的UNIT_SOLD大于引擎或摩托车的值的行。

BRAND  UNIT_SOLD    TYPE
ACURA   1     MOTORCYCLE
CHEVY   3     ENGINE
CHEVY   1     CAR
DODGE   1     ENGINE
FORD    1     MOTORCYCLE
FORD    1     ENGINE
FORD    3     CAR
NISSAN  1     MOTORCYLE
NISSAN  1     ENGINE
NISSAN  2     CAR
TOYOTA  1     MOTORCYCLE
TOYOTA  3     CAR
VOLVO   3     MOTORCYCLE
VW  2     CAR

例如，在前面的示例中，我必须返回：

FORD
NISSAN
TOYOTA
VW

说明:前几个品牌的汽车销量超过了发动机/摩托车。雪佛兰不被认为是因为他们出售3台发动机和只有1辆汽车。

我一直试图用有条件的SELECT语句来解决这个问题，但是我不知道这是否是一个好方法。

非常感谢。

SELECT BRAND
 FROM CARS A
WHERE A.TYPE = 'CAR'
 AND A.UNIT_SELLS > (SELECT B.UNIT_SELLS FROM CARS B WHERE B.TYPE = 'ENGINE' AND A.MAKER=B.MAKER)
 OR  A.UNIT_SELLS > (SELECT B.UNIT_SELLS FROM CARS B WHERE B.TYPE = 'MOTORCYCLE' AND A.BRAND=B.BRAND);

SELECT BRAND
 FROM CARS A
WHERE A.TYPE = 'CAR'
 AND A.UNIT_SELLS > (SELECT B.UNIT_SELLS FROM CARS B WHERE B.TYPE = 'ENGINE' AND A.MAKER=B.MAKER)
 OR  A.UNIT_SELLS > (SELECT B.UNIT_SELLS FROM CARS B WHERE B.TYPE = 'MOTORCYCLE' AND A.BRAND=B.BRAND);

目前，我能够得到汽车的unit_sold更大的行，但我仍然不能得到那些行的任何数量的汽车是出售，他们没有任何摩托车/发动机出售。

Answer 1

使用条件聚合，您可以得到每个总数并进行比较。

SQL DEMO

 SELECT brand,
        -- just for debug
        SUM(CASE WHEN TYPE = 'CAR'    THEN UNIT_SOLD ELSE 0 END) as total_cars,
        SUM(CASE WHEN TYPE = 'ENGINE' THEN UNIT_SOLD ELSE 0 END) as total_engines,
        SUM(CASE WHEN TYPE = 'MOTORCYCLE' THEN UNIT_SOLD ELSE 0 END) as total_motorcycles
 FROM cars
 GROUP BY brand
 HAVING SUM(CASE WHEN TYPE = 'CAR'    THEN UNIT_SOLD ELSE 0 END) >
        SUM(CASE WHEN TYPE = 'ENGINE' THEN UNIT_SOLD ELSE 0 END) 

    AND SUM(CASE WHEN TYPE = 'CAR'        THEN UNIT_SOLD ELSE 0 END) >
        SUM(CASE WHEN TYPE = 'MOTORCYCLE' THEN UNIT_SOLD ELSE 0 END) 

输出

|  brand | total_cars | total_engines | total_motorcycles |
|--------|------------|---------------|-------------------|
|   FORD |          3 |             1 |                 1 |
| NISSAN |          2 |             1 |                 0 |
| TOYOTA |          3 |             0 |                 1 |
|     VW |          2 |             0 |                 0 |