SQL学习（4）——集合运算

北海虽赊，扶摇可接

于 2022-10-19 23:29:31 发布

阅读量1.2k

点赞数 2

分类专栏： SQL基础教程_DataWhale 文章标签： sql 学习数据库

本文链接：https://blog.csdn.net/weixin_46336048/article/details/127383322

版权

SQL基础教程_DataWhale 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

集合运算

1. 表的加减法
2. 连结(JOIN)
3. 练习题

1. 表的加减法

1.1. 集合运算

将表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。
在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

1.2. 加法（并集）UNION

1.2.1. UNION

两个表求加法（交集）：
表1：product：
在这里插入图片描述
表2：product2：

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

在这里插入图片描述
练习题:

假设连锁店想要增加成本利润率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。

SELECT  product_id,product_name,product_type,sale_price,purchase_price
			from product
			where sale_price > 1.5*purchase_price
 UNION
SELECT  product_id,product_name,product_type,sale_price,purchase_price
			from product
			where sale_price < 800;

-- 等价于
 SELECT  product_id,product_name,product_type,sale_price,purchase_price
			from product
			where sale_price > 1.5*purchase_price or sale_price < 800;

在这里插入图片描述
在同一个表中，使用 UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果。

将两个不同的表中的结果合并在一起, 就不得不使用 UNION 。而且, 即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION。

1.2.2. 包含重复行的集合运算 UNION ALL

有时候需要需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了。
例如, 想要知道 product 和 product2 中所包含的商品种类及每种商品的数量, 第一步,就需要将两个表的商品种类字段选出来, 然后使用 UNION ALL 进行不去重地合并. 接下来再对两个表的结果按 product_type 字段分组计数。

-- 保留重复行
SELECT product_id, product_type
  FROM product
 UNION ALL
SELECT product_id, product_type
  FROM product2;

在这里插入图片描述

练习题:

商店决定对product表中成本利润低于50% 或者售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集.

SELECT  *
			from product
			where sale_price < 1.5*purchase_price
 UNION ALL
SELECT  *
			from product
			where sale_price < 1000;

在这里插入图片描述

1.2.3. 隐式数据类型转换

通常来说, 会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, '10086'
  FROM product
 UNION
SELECT product_id, product_name,sale_price
  FROM product2;

在这里插入图片描述
需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜

练习题:

使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性。

例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容。

SELECT SYSDATE(),SYSDATE(),SYSDATE()
 UNION
SELECT 'chars',123,null;

在这里插入图片描述

1.3. 交运算INTERSECT

虽然集合的交运算在SQL标准中已经出现多年了, 然而很遗憾的是, 截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作。

此时需要用 inner join 来求得交集：

SELECT p1.product_id, p1.product_name
  FROM product p1
INNER JOIN product2 p2
ON p1.product_id=p2.product_id;

在这里插入图片描述

1.4. 交集、补集：表的减法

MySQL 8.0 还不支持表的减法运算符 EXCEPT. 不过, 借助学过的NOT IN 谓词, 我们同样可以实现表的减法。
练习题:
找出只存在于product表但不存在于product2表的商品。
在这里插入图片描述
使用NOT谓词进行集合的减法运算, 求出 product 表中, 售价高于2000、成本利润率不低于 30% 的商品：

SELECT * 
  FROM product
 WHERE sale_price > 2000 AND product_id NOT IN (SELECT product_id 
                            FROM product
							WHERE  sale_price < 1.3*purchase_price);

在这里插入图片描述
对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现：
练习题:
使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品。

SELECT * 
  FROM product
 WHERE sale_price < 1500 AND  sale_price > 1.5*purchase_price;

在这里插入图片描述

1.5 对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。
对称差也是个非常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差。
上述方法在其他数据库里也可以用来简单地实现表或查询结果的对称差运算: 首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差。

但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差。

练习题:
使用product表和product2表的对称差来查询哪些商品只在其中一张表：

-- 使用 NOT IN 实现两个表的差集
SELECT *  
	FROM product
	WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION 
SELECT *  
	FROM product2
	WHERE product_id NOT IN (SELECT product_id FROM product);

在这里插入图片描述
借助并集和差集迂回实现交集运算 INTERSECT
通过观察集合运算的文氏图, 我们发现, 两个集合的交可以看作是两个集合的并去掉两个集合的对称差。

2. 连结(JOIN)

连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算. 可以说,连结是 SQL 查询的核心操作, 掌握了连结, 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询。
SQL 中的连结有多种分类方法, 最基础的是内连结和外连结。
在这里插入图片描述

2.1. 内连结(INNER JOIN)

内连结的语法格式是:

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

练习题：
找出东京商店里的衣服类商品的商品名称,商品价格,商品种类,商品数量信息。

2.1.1. 使用内连结从两个表获取信息

按照内连结的语法, 在 FROM 子句中使用 INNER JOIN 将两张表连接起来, 并为 ON 子句指定连结条件为 shopproduct.product_id=product.product_id, 就得到了如下的查询语句:

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name
		,P.product_type,P.sale_price,SP.quantity
  FROM shop_product AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id;

在这里插入图片描述
SELECT 子句中的列最好按照表名.列名的格式来使用：
当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间。如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名, 否则查询语句会报错。

2.1.2. 结合 WHERE 子句使用内连结

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。

1、第一种增加 WEHRE 子句的方式, 就是把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件。

SELECT *
  FROM (-- 第一步查询的结果
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name
		,P.product_type,P.sale_price,SP.quantity
  FROM shop_product AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id) AS STEP1
 WHERE shop_name = '东京'
   AND product_type = '衣服';

在这里插入图片描述

2、由于WHERE 子句将在 FROM 子句之后执行, 也就是说, 在做完 INNER JOIN … ON 得到一个新表后, 才会执行 WHERE 子句, 得到标准的写法:

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name
		,P.product_type,P.sale_price,SP.quantity
  FROM shop_product AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 WHERE shop_name = '东京' AND product_type = '衣服';

在这里插入图片描述

两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 最后, SELECT 子句选出了那些我们需要的列。

3、此外, 一种不是很常见的做法是,还可以将 WHERE 子句中的条件直接添加在 ON 子句中, 这时候 ON 子句后最好用括号将连结条件和筛选条件括起来。（不是太容易阅读, 不建议大家使用）

 SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
  FROM shop_product AS SP
 INNER JOIN product AS P
    ON (SP.product_id = P.product_id AND shop_name = '东京' AND product_type = '衣服');

在这里插入图片描述

4、先筛选再连结：
连结再筛选的标准写法的执行顺序是, 两张完整的表做了连结之后再做筛选,如果要连结多张表, 或者需要做的筛选比较复杂时, 在写 SQL 查询时会感觉比较吃力。
在结合 WHERE 子句使用内连结的时候, 我们也可以更改任务顺序, 并采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来。

SELECT 
SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
  FROM (-- 子查询 1:从 shopproduct 表筛选出东京商店的信息
        SELECT *
          FROM shop_product
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
   (SELECT *
      FROM product
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

在这里插入图片描述
练习题1:
找出每个商店里的衣服类商品的名称及价格等信息：
1、WEHRE 子句子查询的方式

SELECT *
  FROM (-- 第一步查询的结果
		SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
		FROM shop_product AS SP
		INNER JOIN product AS P
    ON SP.product_id = P.product_id) AS STEP1
 WHERE product_type = '衣服';

2、 WHERE 子句无子查询

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
FROM shop_product AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
WHERE product_type = '衣服';

3、 WHERE 子句中的条件直接添加在 ON 子句中

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
FROM shop_product AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id AND product_type = '衣服';

4、先筛选再连结

SELECT 
SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
  FROM (  SELECT * FROM shop_product ) AS SP
 INNER JOIN 
    (SELECT * FROM product
		 WHERE product_type = '衣服' ) AS P
 ON SP.product_id = P.product_id;

在这里插入图片描述
使用子查询由于每一层的目的很明确, 更适于阅读, 并且在外连结的情形下, 还能避免错误使用 WHERE 子句导致外连结失效的问题。

练习题2:
分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息。
1、WEHRE 子句子查询的方式

 SELECT *
  FROM (
		SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
		FROM shop_product AS SP
		INNER JOIN product AS P
    ON SP.product_id = P.product_id) AS STEP1
 WHERE shop_name = '东京'
   AND sale_price < 2000;

2、 WHERE 子句无子查询

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
FROM shop_product AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
WHERE shop_name = '东京' AND sale_price < 2000;

在这里插入图片描述

2.1.3 结合 GROUP BY 子句使用内连结

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待。
最简单的情形, 是在内连结之前就使用 GROUP BY 子句.
但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。
练习题:
每个商店中, 售价最高的商品的售价分别是多少?

SELECT SP.shop_id,SP.shop_name, MAX(P.sale_price) AS max_price
FROM shop_product AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name;

在这里插入图片描述
思考题:
上述查询得到了每个商品售价最高的商品, 但并不知道售价最高的商品是哪一个.如何获取每个商店里售价最高的商品的名称和售价?
在找出每个商店售价最高商品的价格后, 使用这个价格再与 product 列进行连结, 但这种做法在价格不唯一时会出现问题。

SELECT SP.shop_id,SP.shop_name, P.product_name, P.sale_price, MAX(P.sale_price) AS max_price
FROM shop_product AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name;

在这里插入图片描述

2.1.4. 特殊连结

内连结与关联子查询
内连结与关联子查询都可以解决一些具体问题，例如：找出每个商品种类当中售价高于该类商品的平均售价的商品.当时我们是使用关联子查询来实现的。可以用子查询进行处理，也可以用内连结处理。

SELECT  P1.product_id,P1.product_name ,P1.product_type,P1.sale_price,P2.avg_price
  FROM product AS P1
 INNER JOIN 
   (-- 提取种类平均售价
   SELECT product_type,AVG(sale_price) AS avg_price 
      FROM product 
     GROUP BY product_type) AS P2 
    ON P1.product_type = P2.product_type
 WHERE P1.sale_price > P2.avg_price;

在这里插入图片描述
自然连结(NATURAL JOIN)
自然连结是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。

SELECT *  FROM shop_product NATURAL JOIN product

在这里插入图片描述
把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。
练习题:
试写出与上述自然连结等价的内连结。

SELECT SP.* ,P.*
FROM shop_product AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id;

在这里插入图片描述
连结求交集

使用自然连结还可以求出两张表或子查询的公共部分

SELECT * 
  FROM (SELECT product_id, product_name
          FROM product ) AS A 
NATURAL JOIN 
   (SELECT product_id, product_name 
      FROM product2) AS B;

在这里插入图片描述

使用连结求交集
练习题: 使用内连结求 product 表和 product2 表的交集。

SELECT P1.*
  FROM product AS P1
 INNER JOIN product2 AS P2
    ON P1.product_id = P2.product_id;

在这里插入图片描述

2.2. 外连结(OUTER JOIN)

内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行。

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值;
右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值;
全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。

三种外连结的对应语法分别为:

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

2.2.1. 左连结

练习题: 统计每种商品分别在哪些商店有售, 需要包括那些在每个商店都没货的商品。

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price
  FROM product AS P
  LEFT OUTER JOIN shop_product AS SP
  ON SP.product_id = P.product_id;

在这里插入图片描述
在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结.如果使用内连结的话,根据 SELECT 语句执行时商店库存状况的不同,结果的行数也会发生改变,生成的单据的版式也会受到影响,而使用外连结能够得到固定行数的结果。

2.2.2. 结合 WHERE 子句使用左连结

练习题:
使用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店。

直接进行where筛选，少了在所有商店都无货的高压锅和圆珠笔。在WHERE过滤条件中增加 OR quantity IS NULL 的条件, 便可以得到预期的结果。

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,SP.quantity
  FROM product AS P
  LEFT OUTER JOIN shop_product AS SP
    ON SP.product_id = P.product_id
 WHERE quantity< 50 OR quantity IS NULL;

在这里插入图片描述

上述程式，在进行完外连结后才会执行WHERE子句, 因此那些主表中无法被匹配到的行就被WHERE条件筛选掉了。把WHERE子句挪到外连结之前进行: 先写个子查询,用来从shopproduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来。

2.3. 多表连结

2.3.1. 多表进行内连结

首先创建一个用于三表连结的表 Inventoryproduct.首先我们创建一张用来管理库存商品的表, 假设商品都保存在 P001 和 P002 这 2 个仓库之中。
根据表Inventoryproduct及 shopproduct 表和 product 表, 使用内连结找出每个商店都有那些商品, P001仓库每种商品的库存总量分别是多少。

连结第三张表的时候, 也是通过 ON 子句指定连结条件。

SELECT SP.shop_id,SP.shop_name,SP.product_id
       ,P.product_name,P.sale_price,IP.inventory_quantity
  FROM shop_product AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 INNER JOIN Inventoryproduct AS IP
    ON SP.product_id = IP.product_id
 WHERE IP.inventory_id = 'P001';

在这里插入图片描述

2.3.2. 多表进行外连结

SELECT P.product_id,P.product_name,P.sale_price
       ,SP.shop_id,SP.shop_name,IP.inventory_quantity
  FROM product AS P
  LEFT OUTER JOIN shop_product AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id;

在这里插入图片描述

2.4. ON 子句进阶–非等值连结

实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。

非等值自左连结(SELF JOIN)
使用非等值自左连结实现排名:
练习题1:
希望对 product 表中的商品按照售价赋予排名.
一个从集合论出发,使用自左连结的思路是：对每一种商品,找出售价不低于它的所有商品, 然后对售价
不低于它的商品使用 COUNT 函数计数。
使用非等值自左连结进行累计求和:

练习题:
请按照商品的售价从低到高,对售价进行累计求和
首先, 按照题意, 对每种商品使用自左连结, 找出比该商品售价价格更低或相等的商品。

2.5. 交叉连结—— CROSS JOIN(笛卡尔积)

在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合.
数据库表(或者子查询)的并,交和差都是在纵向上对表进行扩张或筛选限制等运算的, 这要求表的列数及对应位置的列的数据类型"相容", 因此这些运算并不会增加新的列, 而交叉连接(笛卡尔积)则是在横向上对表进行扩张, 即增加新的列, 这一点和连结的功能是一致的. 但因为没有了ON子句的限制, 会对左表和右表的每一行进行组合, 这经常会导致很多无意义的行出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有一些用处。

-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM shopproduct AS SP
 CROSS JOIN product AS P;
--2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM shopproduct AS SP , product AS P;

对满足相同规则的表进行交叉连结的集合运算符是 CROSS JOIN （笛卡儿积）.进行交叉连结时无法使用内连结和外连结中所使用的ON 子句,这是因为交叉连结是对两张表中的全部记录进行交叉组合。

因此结果中的记录数通常是两张表中行数的乘积.本例中,因为 shopproduct 表存在 13 条记录,product 表存在 8 条记录,所以结果中就包含了 13 × 8 = 104 条记录。

2.6. 连结的特定语法和过时语法

在笛卡尔积的基础上, 我们增加一个 WHERE 子句, 将之前的连结条件作为筛选条件加进去, 我们会发现, 得到的结果恰好是直接使用内连接的结果。

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price
FROM shop_product AS SP
CROSS JOIN product AS P
WHERE SP.product_id = P.product_id;

结果与2.1内连结内容相同。

3. 练习题

3.1. 找出 product 和 product2 中售价高于 500 的商品的基本信息。

SELECT * FROM product 
WHERE  sale_price > 500
UNION
SELECT * FROM product2
WHERE  sale_price > 500

在这里插入图片描述

3.2. 借助对称差的实现方式, 求product和product2的交集。

select * 
from (SELECT * FROM product UNION SELECT * FROM product2) as p_all
where  product_id  not in (SELECT product_id  FROM product WHERE product_id NOT IN
							(SELECT product_id FROM product2) 
                           UNION  
							SELECT product_id  FROM product2 WHERE product_id NOT IN 
							(SELECT product_id FROM product));

在这里插入图片描述

3.3. 每类商品中售价最高的商品都在哪些商店有售？

select product_id,product_name,product_type,sale_price
from product as p
where p.sale_price in  (select product_type, max(sale_price) as max_price
from product GROUP BY product_type) as P_max and  p.sale_price in P_max.max_price