T04 集合运算

最新推荐文章于 2022-07-20 11:34:31 发布
最新推荐文章于 2022-07-20 11:34:31 发布
阅读量364 收藏
点赞数
分类专栏： SQL学习笔记 文章标签： sql mysql big data
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiao_a_tong/article/details/123720329
版权

集合运算

表的加减法

集合在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

以下的文氏图展示了几种集合的基本运算。

在这里插入图片描述

在这里插入图片描述

表的加法–UNION

eg:

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

UNION 等集合运算符通常都会除去重复的记录。这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行

练习题：

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

结果应该类似于:
在这里插入图片描述

我的解答：

SELECT product_id, product_name, product_type, sale_price,  pruchase_price
FROM product
WHERE sale_price - purchase_price > purchase_price*0.5
UNION
SELECT product_id, product_name, product_type, sale_price,  pruchase_price
FROM product
WHERE sale_price < 800;

不用UNION :

SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price < 800 
    OR sale_price > 1.5 * purchase_price;

对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了。

练习题 :

分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品。

我的解答：

-- UNION
SELECT*
FROM product
WHERE sale_price < purchase_price*1.3
UNION
SELECT*
FROM product
WHERE sale_price / purchase_price IS NULL;
-- OR
SELECT*
FROM product
WHERE sale_price < purchase_price*1.3
OR sale_price / purchase_price IS NULL;

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

在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了。

练习题:

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

图片

我的解答：

SELECT*
FROM product
WHERE sale_price < purchase_price*1.5
UNION ALL
SELECT*
FROM product
WHERE purchase_price<1000;

隐式数据类型转换

数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, '1'
  FROM Product
 UNION
SELECT product_id, product_name,sale_price
  FROM Product2;

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

练习题:

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

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

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

上述代码的查询结果:

在这里插入图片描述

MySQL 8.0 不支持交运算INTERSECT

需要用 inner join 来求得交集:

SELECT p1.product_id, p1.product_name
  FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

差集,补集与表的减法

当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素, 采取直接忽略的策略,因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。

MySQL 8.0 还不支持 EXCEPT 运算

不过, 借助第六章学过的NOT IN 谓词, 我们同样可以实现表的减法。

练习题:

找出只存在于Product表但不存在于Product2表的商品。

我的解答：

SELECT*
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2);

EXCEPT 与 NOT 谓词

通过上述练习题的MySQL解法, 我们发现, 使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果。

练习题:

使用NOT谓词进行集合的减法运算, 求出Product表中, 售价高于2000,但利润低于30%的商品, 结果应该如下表所示。

图片

我的解答：

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

NTERSECT 与 AND 谓词

对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现。

练习题:

使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示。

在这里插入图片描述

参考答案

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

对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合. 两个集合的对称差等于 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

通过观察集合运算的文氏图, 我们发现, 两个集合的交可以看作是两个集合的并去掉两个集合的对称差。

连结(JOIN)

UNION和INTERSECT 等集合运算, 这些集合运算的特征就是以行方向为单位进行操作.

在这里插入图片描述

注: 连结**更适合从多张表获取信息。

连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算.

内连结(INNER JOIN)

-- 语法格式
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
-- 其中 INNER 关键词表示使用了内连结

对比一下product和shopProduct两张表, 可以发现, 商品编号列是一个公共列, 因此很自然的事情就是用这个商品编号列来作为连接的“桥梁”，将Product和ShopProduct这两张表连接起来。

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

关于内连结,需要注意以下三点:

要点一: 进行连结时需要在 FROM 子句中使用多张表.

之前的 FROM 子句中只有一张表, 而这次我们同时使用了 ShopProduct 和 Product 两张表,使用关键字 INNER JOIN 就可以将两张表连结在一起了:

FROM ShopProduct AS SP INNER JOIN Product AS P

要点二:必须使用 ON 子句来指定连结条件.

在进行内连结时 ON 子句是必不可少的。

ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上, 它能起到与 WHERE 相同的筛选作用。

要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用。

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

结合 WHERE 子句使用内连结

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

增加 WHERE 子句的方式有好几种:

-- 第一种：上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件。
SELECT*
FROM (
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id) AS STEP1
WHERE shop_name = '东京'
AND product_type = '衣服';

-- 第二种：标准写法
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id
   WHERE shop_name = '东京'
AND product_type = '衣服' ;
-- 执行顺序：FROM 子句->WHERE 子句->SELECT 子句

-- 第三种：可以将 WHERE 子句中的条件直接添加在 ON 子句中
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON （SP.product_id = P.product_id
 AND shop_name = '东京'
AND product_type = '衣服' );

-- 第四种：先分别在两个表使用 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 ShopProduct
 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--不使用子查询
SELECT SP.shop_id, SP.shop_name, P.product_id, P.product_name, P.product_type, P.purchase_price
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product.id
WHERE P.product_type = '衣服';
--  2--使用子查询
SELECT SP.shop_id, SP.shop_name,  P.product_id, P.product_name, P.product_type, P.purchase_price
FROM ShopProduct AS SP
INNER JOIN (
SELECT product_id, product_name, product_type, purchase_price
    FROM Product 
    WHERE product_type = '衣服'
)AS P
ON SP.product_id = P.product.id；

练习题:

分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。

在这里插入图片描述

我的解答：

-- 不使用子查询
SELECT SP.shop_id, SP.shop_name, SP.product_id, SP.quantity, P.product_name, P.product_type, P.sale_price
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product.id
WHERE P.sale_price < 2000
AND SP.shop_id = '000A';
-- 使用子查询
SELECTSP.shop_id, SP.shop_name, SP.product_id, SP.quantity, P.product_name, P.product_type, P.sale_price
FROM (SELECT shop_id, shop_name, product_id, quantity
      FROM ShopProduct 
     WHERE shop_id = '000A')AS SP
INNER JOIN (
SELECT product_id, product_name, product_type, sale_price
    FROM Product 
    WHERE sale_price < 2000)AS P
ON SP.product_id = P.product.id；

结合 GROUP BY 子句使用内连结

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待。最简单的情形, 是在内连结之前就使用 GROUP BY 子句.

练习题:

每个商店中, 售价最高的商品的售价分别是多少?

我的解答：

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

自连结(SELF JOIN)

之前的内连结, 连结的都是不一样的两个表。但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。

内连结与关联子查询

找出每个商品种类当中售价高于该类商品的平均售价的商品.当时我们是使用关联子查询来实现的。使用内连结同样可以解决这个问题:

  • 首先, 使用 GROUP BY 按商品类别分类计算每类商品的平均价格。
  • 接下来, 将上述查询与表 Product 按照 product_type (商品种类)进行内连结。
  • 最后, 增加 WHERE 子句, 找出那些售价高于该类商品平均价格的商品.完整的代码如下:
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 shopproduct NATURAL JOIN Product

上述查询得到的结果, 会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。

在这里插入图片描述

练习题:

试写出与上述自然连结等价的内连结。

-- 参考答案
SELECT  SP.product_id,SP.shop_id,SP.shop_name,SP.quantity
       ,P.product_name,P.product_type,P.sale_price
       ,P.purchase_price,P.regist_date  
  FROM shopproduct AS SP 
 INNER JOIN Product AS P 
    ON SP.product_id = P.product_id

使用自然连结还可以求出两张表或子查询的公共部分, 例如求表 Product 和表 Product2 中的公共部分, 也可以用自然连结来实现:

SELECT * FROM Product NATURAL JOIN Product2

在这里插入图片描述

这个结果和书上给的结果并不一致, 少了运动 T 恤, 这是由于运动 T 恤的 regist_date 字段为空, 在进行自然连结时, 两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行。

如果我们将查询语句进行修改:

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
   AND P1.product_name = P2.product_name
   AND P1.product_type = P2.product_type
   AND P1.sale_price   = P2.sale_price
   AND P1.regist_date  = P2.regist_date)

得到如下结果
在这里插入图片描述

注意上述结果和 P230 的结果并不一致–少了 product_id='0003’这一行, 观察源表数据可发现, 少的这行数据的 regist_date 为缺失值, 回忆第六章讲到的 IS NULL 谓词, 我们得知, 这是由于缺失值是不能用等号进行比较导致的。

如果我们仅仅用 product_id 来进行连结:

SELECT P1.*
  FROM Product AS P1
 INNER JOIN Product2 AS P2
    ON P1.product_id = P2.product_id

查询结果:
在这里插入图片描述

这次就一致了。

外连结(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)>

左连结与右连结

由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别.

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

使用左连结

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
      FROM ShopProduct AS SP
      LEFT OUTER JOIN
      Product AS P
      ON SP.product_id = P.product_id
       ORDER BY product_id;

● 外连结要点 1: 选取出单张表中全部的信息

由于内连结只能选取出同时存在于两张表中的数据,因此只在 Product 表中存在的 2 种商品并没有出现在结果之中.相反,对于外连结来说,只要数据存在于某一张表当中,就能够读取出来.使用外连结能够得到固定行数的结果.外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在（在原表之外）的信息”.相反,只包含表内信息的连结也就被称为内连结了。

● 外连结要点 2：使用 LEFT、RIGHT 来指定主表.

外连结还有一点非常重要,那就是要把哪张表作为主表.最终的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表.使用哪一个都可以.通常使用 LEFT 的情况会多一些,但也并没有非使用这个不可的理由,使用 RIGHT 也没有问题。

结合 WHERE 子句使用左连结

练习题:

使用外连结从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果。

图片

注意高压锅和圆珠笔两种商品在所有商店都无货, 所以也应该包括在内。

我的解答：

SELECT  P.product_id, P.product_name, P.sale_price, SP.shop_id, SP.shop_name, SP.quantity
      FROM 
      (SELECT* 
       FROM ShopProduct
       WHERE quantity < 50) AS SP
      RIGHT OUTER JOIN
      Product AS P
      ON SP.product_id = P.product_id
       ORDER BY product_id;

在 MySQL 中实现全外连结

MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。

多表连结

多表进行内连结

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROM ShopProduct 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';

多表进行外连结

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 ShopProduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id

ON 子句进阶–非等值连结

除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。

非等值自左连结(SELF JOIN)

练习题:

希望对 Product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品,

SELECT  product_id, product_name, sale_price, COUNT(p2_id) AS my_rank
  FROM (--使用自左连结对每种商品找出价格不低于它的商品
        SELECT P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,P2.product_id AS P2_id
               ,P2.product_name AS P2_name
               ,P2.sale_price AS P2_price 
          FROM Product AS P1 
          LEFT OUTER JOIN Product AS P2 
            ON P1.sale_price <= P2.sale_price 
        ) AS X
 GROUP BY product_id, product_name, sale_price
 ORDER BY my_rank;

练习题:

请按照商品的售价从低到高,对售价进行累计求和

首先, 按照题意, 对每种商品使用自左连结, 找出比该商品售价价格更低或相等的商品

SELECT	product_id, product_name, sale_price
       ,SUM(P2_price) AS cum_price 
  FROM
        (SELECT  P1.product_id, P1.product_name, P1.sale_price
                ,P2.product_id AS P2_id
                ,P2.product_name AS P2_name
                ,P2.sale_price AS P2_price 
           FROM Product AS P1 
           LEFT OUTER JOIN Product AS P2 
             ON ((P1.sale_price > P2.sale_price)
             OR (P1.sale_price = P2.sale_price 
            AND P1.product_id<=P2.product_id))
	      ORDER BY P1.sale_price,P1.product_id) AS X
 GROUP BY product_id, product_name, sale_price
 ORDER BY sale_price,cum_price;

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

交叉连结的语法有如下几种形式:

-- 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;
-- 交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积.

交叉连结没有应用到实际业务之中的原因有两个.一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持。

练习题

4.1

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

我的解答：

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

4.2

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

我的解答：

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);
SELECT * 
FROM (SELECT * 
      FROM product 
      UNION 
      SELECT * 
      FROM product2) AS u
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)
);

4.3

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

我的解答：

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.product_type, P.sale_price, MP.maxp AS '该类商品的最大售价' 
FROM product AS P 
INNER JOIN shop_product AS SP
ON SP.product_id = P.product_id
INNER JOIN (
			SELECT product_type, MAX(sale_price) AS maxp 
    FROM product 
            GROUP BY product_type
            ) AS MP
ON MP.product_type = P.product_type AND P.sale_price = MP.maxp;

4.4

分别使用内连结和关联子查询每一类商品中售价最高的商品。

我的解答：

-- 内连结
SELECT P.product_id, P.product_name, P.product_type, P.sale_price
FROM product AS P
INNER JOINx
(SELECT product_type, MAX(sale_price) AS maxp
FROM product
 GROUP BY product_type) AS MP
 ON MP.product_type = P.product_type AND P.sale_price = MP.maxp;
-- 关联子查询
SELECT P.product_id, P.product_name, P.product_type, P.sale_price
FROM product AS P
WHERE sale_price = (SELECT MAX(sale_price) 
					FROM product AS P1 
				    WHERE P.product_type = P1.product_type 
				    GROUP BY product_type);

4.5

用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

我的解答：

SELECT P.product_id, P.product_name, P.sale_price,
	(SELECT SUM(sale_price) FROM product AS P1
	WHERE P.sale_price > P1.sale_price
	OR (P.sale_price = P1.sale_price AND P.product_id >= P1.product_id)
	) AS '累计求和'
FROM product AS P 
ORDER BY sale_price;

okok本章结束~

xiao_a_tong
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
OXStack:用于基本 SQLLite 函数并帮助对象关系映射和基本配置的库
07-10
OXStack 用于基本 SQLLite 函数并帮助对象关系映射和基本配置的库。 目的 利用配置 XML 和 SQLLite 数据库快速开发简单/快速的业务应用程序。 例子 假设我们有一个表beers包含字段brand和alc_perc 。 我们可以使用以下方法获取这些信息： public class Beer : BaseRTEntity { public string Brand { get ; set ; } [ RTDataCol ( " alc_perc " )] public double Alcohol { get ; set ; } public Beer ( DataRow dr , DataConnector dc ) : base ( dr , dc ) { } } public class Beers : BaseRT < Beer> {
0x04 二分.ppt
09-15
这个问题可以通过二分答案 `T` 来解决，因为对于大于最优解 `S` 的 `T` 都是合法的。类似地，在Best Cow Fences问题中，寻找平均数最大的连续子段，同样可以通过二分答案来解决，利用子段和与平均数的关系来验证二...
阿里云天池龙珠计划 sql篇---stack07
wangjunlei666的博客
01-30 1071
表的集合运算，加减法 集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。 在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。 表的加法-union SELECT product_id, product_name FROM p
窥探SQL: 4.集合运算
MH6_Power的博客
12-22 135
窥探SQL: 4.集合运算1.表的加减法1.1集合运算1.2 表的加法-UNIONUNION 与 OR 谓词包含重复行的集合运算UNION ALLbag模型 和 set模型2.连结（JOIN) 1.表的加减法 1.1集合运算 集合，在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行. 在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT
【task13】hive的窗口函数
weixin_45746689的博客
02-12 218
目录窗口函数概念及使用方法窗口函数种类专用窗口函数聚合函数在窗口函数上的使用窗口函数的应用---计算移动平移窗口函数适用范围及注意事项grouping运算符rollup---计算合计及小计 窗口函数概念及使用方法 OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。 窗口函数也称为OLAP函数。为了便于理解，称之为窗口函数。常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。 窗口函数的通
TAINCHI_TASK03_SQL （视图、子查询、函数）
Z_Sam
02-07 266
TAINCHI_TASK03_SQL （视图、子查询、函数） 3.1 视图 3.1.1 什么是视图 视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。 3.1.2 视图与表有什么区别 《sql基础教程 第2版》用一句话非常凝练的概括了视图与表的区别—“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表
java常用工具类的使用
03-19
在Java开发类库中，提供了很多工具类，我们即将学习最常见的工具类，比如对日期的操作，对集合的操作等。具体更多的工具类，请参考JavaDoc文档。 2. java.util.Date类 Date类包装了毫秒值，毫秒值表示自1970年1月1...
数据库原理A卷.pdf
10-11
4. 集合运算：传统的集合运算包括并、交、差和笛卡尔积。在关系数据库中，这些运算是关系代数的基础。 5. VFP语言：Visual FoxPro（VFP）是一种数据库编程语言，其命令不区分大小写。 6. 时间表示：`{^2007-04-01 5:...
2013年高三数学5月份百题精练（1）
08-19
1. 集合的交集：题目中提到的集合问题涉及到集合的交集运算，MN=∩，需要理解集合交集的概念，即找出同时属于集合M和集合N的所有元素。 2. 复数及其在复平面上的位置：复数11,zi 22zi  ，计算复数的乘积并...
外国语学校高三第二次数学月考试题(文科).pdf
12-13
7. **直线过定点与等差数列**：直线mymx04)1()13(经过等差数列{na}的第一项和第二项，由此确定定点坐标，并利用等差数列的性质求解T10，即数列{nb}的前10项和。 8. **函数性质的综合应用**：给出的函数需要满足三个...
Hi3531DV200 H.265编解码AI处理器简介1
最新发布
08-04
产品简介文档版本06发布日期版权所有 :copyright: 上海海思技术有限公司 2020。保留一切权利。非经本公司书面许可，任何单位和个人不得擅自摘抄、复制本文档内容的部分或全
stack()函数
游戏boy
04-13 3773
a=torch.Tensor(range(1,7)).view(2,3) b=torch.Tensor(range(7,13)).view(2,3) c=torch.Tensor(range(13,19)).view(2,3) d=torch.stack((a,b,c),dim=0) print(a) print(b) print(c) print(d) ########## dim=0 te...
mysql堆叠注入条件,SQL注入 堆叠注入（Stack Injection）
weixin_28930803的博客
03-23 357
注入原理平常我们注入时都是通过对原来sql语句传输数据的地方进行相关修改，注入情况会因为该语句本身的情况而受到相关限制，例如一个select语句，那么我们注入时也只能执行select操作，无法进行增、删、改，其他语句也同理，所以可以说我们能够注入的十分有限。但堆叠注入则完全打破了这种限制，其名字顾名思义，就是可以堆一堆sql注入进行注入，这个时候我们就不受前面语句的限制可以为所欲为了。其原理也很简...
MLSQL Stack 应用场景示例
allwefantasy的专栏
04-27 1586
前言 MLSQL Stack 都有哪些应用场景呢？毕竟现在是个场景为王的时代。其实MLSQL Stack有无限的可能性等待大家挖掘。下面我们提一些已经在应用的。 数据同步组件 比如从各个数据库里把数据同步到HDFS上。 ## ETL平台 配合调度，传统的批处理平台。 流式平台 MLSQL 很好的支持了Structured Streaming. 开发平台 什么意思呢？就是根据业务需求，用M...
第四章：集合运算
seven_7799的博客
07-20 1199
第四章：集合运算
【学习笔记】Task04：集合运算-表的加减法和join等-天池龙珠计划SQL训练营 & 练习题详细解答
NewComerSyt的博客
08-10 234
Task04：集合运算-表的加减法和join等-天池龙珠计划SQL训练营 教程链接 COMMENTS: 这部分内容对于未接触增进没有接触过SQL语言的读者来说可能有点冗长和费劲，一方面语句的理解有些困难，另一方面本章文字内容非常多，非常容易让读者放弃。 当然了，文中的例子质量非常高，想必是作者在实际工作中遇到的很多情况的影子。 UNION UNION 等集合运算符通常都会除去重复的记录. UNION ALL 进行不去重地合并. MySQL 8.0 还不支持 EXCEPT 运算，不支持交运算INTERSEC
STL——stack 常用函数
tian__si的博客
02-18 350
1. push（） 2. top（） 3. pop（） 4. empty（） 5. size（） stack 用来模拟实现一些递归，防止程序对栈内存的限制而导致程序运行出错。
stack的使用方法
chen_zan_yu_的博客
03-11 651
size() 返回的元素数 top() 返回栈顶的元素 pop() 从栈中去除元素 push(x) 向栈中添加元素x empty() 在栈为空时返回true #include&lt;iostream&gt; #include&lt;stack&gt; using namespace std; int main() { stack&lt;int&gt;s; s.push(3)...
stack用法
大数据之道
11-04 1624
//stack用法 using System; using System.Collections.Generic; using System.Text; using System.Collections; namespace ConsoleApplication1 { class Program { static void Main(string[] args)
关系代数基础：集合运算与专门操作详解
5. **集合运算的条件**：进行集合运算时，要求参与运算的关系R和S具有相同的属性数量且对应属性的数据类型相同。只有满足这些条件，关系R和S才能进行并、交、差等运算。 关系代数的目的是提供一种简洁、通用的方式...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值