【LeetCode-SQL专项突破】-第5天:合并
📢📢📢📣📣📣
哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10年DBA工作经验
一位上进心十足的【大数据领域博主】!😜😜😜
中国DBA联盟(ACDU)成员,目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发,备份恢复,安装迁移,性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】,欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱!❤️❤️❤️
文章目录
- 前言
-
- 175. 组合两个表
- 1581. 进店却未进行过交易的顾客
- 1148.文章浏览I
前言
SQL每个人都要用,但是用来衡量产出的并不是SQL本身,你需要用这个工具,去创造其它的价值。
175. 组合两个表
🚀 表: Person+-------------+---------+| 列名 | 类型 |+-------------+---------+| PersonId | int || FirstName | varchar || LastName | varchar |+-------------+---------+personId 是该表的主键列。该表包含一些人的 ID 和他们的姓和名的信息。表: Address+-------------+---------+| 列名 | 类型 |+-------------+---------+| AddressId | int || PersonId | int || City | varchar || State| varchar |+-------------+---------+addressId 是该表的主键列。该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。🚀 需求编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中,则报告为空 null 。以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入: Person表:+----------+----------+-----------+| personId | lastName | firstName |+----------+----------+-----------+| 1 | Wang | Allen || 2 | Alice | Bob|+----------+----------+-----------+Address表:+-----------+----------+---------------+------------+| addressId | personId | city | state |+-----------+----------+---------------+------------+| 1 | 2 | New York City | New York || 2 | 3 | Leetcode | California |+-----------+----------+---------------+------------+输出: +-----------+----------+---------------+----------+| firstName | lastName | city | state |+-----------+----------+---------------+----------+| Allen | Wang | Null | Null || Bob| Alice | New York City | New York |+-----------+----------+---------------+----------+解释: 地址表中没有 personId = 1 的地址,所以它们的城市和州返回 null。addressId = 1 包含了 personId = 2 的地址信息。🐴🐴 答案# Write your MySQL query statement belowselect a.firstName,a.lastName,b.city,b.statefrom Person a left join Address bon a.PersonId = b.PersonId/* Write your T-SQL query statement below */select a.firstName,a.lastName,b.city,b.statefrom Person a left join Address bon a.PersonId = b.PersonId/* Write your PL/SQL query statement below */select a.firstName "firstName",a.lastName "lastName",b.city "city",b.state "state"from Person a,Address bwhere a.PersonId = b.PersonId(+)order by 1
1581. 进店却未进行过交易的顾客
🚀 表:Visits+-------------+---------+| Column Name | Type |+-------------+---------+| visit_id | int || customer_id | int |+-------------+---------+visit_id 是该表的主键。该表包含有关光临过购物中心的顾客的信息。表:Transactions+----------------+---------+| Column Name | Type |+----------------+---------+| transaction_id | int || visit_id| int || amount | int |+----------------+---------+transaction_id 是此表的主键。此表包含 visit_id 期间进行的交易的信息。🚀 需求有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个 SQL 查询,来查找这些顾客的 ID ,以及他们只光顾不交易的次数。返回以 任何顺序 排序的结果表。查询结果格式如下例所示。示例 1:输入:Visits+----------+-------------+| visit_id | customer_id |+----------+-------------+| 1 | 23 || 2 | 9 || 4 | 30 || 5 | 54 || 6 | 96 || 7 | 54 || 8 | 54 |+----------+-------------+Transactions+----------------+----------+--------+| transaction_id | visit_id | amount |+----------------+----------+--------+| 2| 5 | 310 || 3| 5 | 300 || 9| 5 | 200 || 12 | 1 | 910 || 13 | 2 | 970 |+----------------+----------+--------+输出:+-------------+----------------+| customer_id | count_no_trans |+-------------+----------------+| 54 | 2|| 30 | 1|| 96 | 1|+-------------+----------------+解释:ID = 23 的顾客曾经逛过一次购物中心,并在 ID = 12 的访问期间进行了一笔交易。ID = 9 的顾客曾经逛过一次购物中心,并在 ID = 13 的访问期间进行了一笔交易。ID = 30 的顾客曾经去过购物中心,并且没有进行任何交易。ID = 54 的顾客三度造访了购物中心。在 2 次访问中,他们没有进行任何交易,在 1 次访问中,他们进行了 3 次交易。ID = 96 的顾客曾经去过购物中心,并且没有进行任何交易。如我们所见,ID 为 30 和 96 的顾客一次没有进行任何交易就去了购物中心。顾客 54 也两次访问了购物中心并且没有进行任何交易。🐴🐴 答案# Write your MySQL query statement belowselect customer_id "customer_id",count(*) "count_no_trans" from Visits awhere not exists (select * from Transactions b where a.visit_id = b.visit_id )group by customer_idorder by 2 desc/* Write your T-SQL query statement below */select customer_id,count(*) count_no_trans from Visits awhere not exists (select * from Transactions b where a.visit_id = b.visit_id )group by customer_idorder by count_no_trans desc/* Write your PL/SQL query statement below */select customer_id "customer_id",count(*) "count_no_trans" from Visits awhere not exists (select * from Transactions b where a.visit_id = b.visit_id )group by customer_idorder by 2 desc
1148.文章浏览I
🚀 Views 表:+---------------+---------+| Column Name | Type |+---------------+---------+| article_id | int || author_id | int || viewer_id | int || view_date | date |+---------------+---------+此表无主键,因此可能会存在重复行。此表的每一行都表示某人在某天浏览了某位作者的某篇文章。请注意,同一人的 author_id 和 viewer_id 是相同的。🚀 需求请编写一条 SQL 查询以找出所有浏览过自己文章的作者,结果按照 id 升序排列。查询结果的格式如下所示:Views 表:+------------+-----------+-----------+------------+| article_id | author_id | viewer_id | view_date |+------------+-----------+-----------+------------+| 1 | 3 | 5 | 2019-08-01 || 1 | 3 | 6 | 2019-08-02 || 2 | 7 | 7 | 2019-08-01 || 2 | 7 | 6 | 2019-08-02 || 4 | 7 | 1 | 2019-07-22 || 3 | 4 | 4 | 2019-07-21 || 3 | 4 | 4 | 2019-07-21 |+------------+-----------+-----------+------------+结果表:+------+| id |+------+| 4 || 7 |+------+🐴🐴 答案# Write your MySQL query statement belowselect distinct author_id idfrom Viewswhere author_id = viewer_idorder by id/* Write your T-SQL query statement below */select distinct author_id idfrom Viewswhere author_id = viewer_idorder by id/* Write your PL/SQL query statement below */select distinct author_id "id"from Viewswhere author_id = viewer_idorder by 1
体系化学习SQL,请关注CSDN博客
https://blog.csdn.net/weixin_41645135/category_11653817.html
创作打卡挑战赛 赢取流量/现金/CSDN周边激励大奖