2022.7.11-2022.7.17
196.删除表内重复的电子邮箱,只保留唯一的电子邮箱:
select p1
from person p1 , person p2
where p1.id=p2.id and p1.id>p2.id
627.变更性别
update salary
set
sex = case sex
when 'm' then 'f'
else 'm'
end;
1667.修复表中的名字
select user_id,
concat(upper(left(name,1)),lower(substring(name,2)))as name
from Users
order by user_id
# concat函数将多个字符床拼接在一起
# upper函数将字符串中的所有符号转换为大写
# lelt(str,length)函数从最左边开始截取字符串,length是截取的长度
# lower函数将字符床中的所有符号转换为小写
# substring(str,2)从第二个截取到最后
1484.按日期分组销售产品
select
sell_date,
//对product日期相同product不同进行聚合,作为num_sold表输出
count(distinct product) num_sold,
//对product日期相同product不同的,将多个字符串连接成一个字符串,作为product输出
GROUP_CONCAT(distinct product) products
from
activities
group by sell_date
order by sell_date;
#group by 用于结合聚合函数,根据一个或多个列对结果集进行分组
#order by 进行排序
#count(),将多个字符串连接成一个字符串
#GROUP_CONCAT函数将组中的字符串连接成为具有各种选项的单个字符串
1527.患某种疾病的患者
select patient_id,patient_name,conditions
from Patients
where conditions like 'DIAB1%' or conditions like '% DIAB1%';
1965.丢失信息的雇员:
写出一个查询语句,找到所有 丢失信息 的雇员id。当满足下面一个条件时,就被认为是雇员的信息丢失:
雇员的 姓名 丢失了,或者
雇员的 薪水信息 丢失了,或者
返回这些雇员的id employee_id , 从小到大排序 。
select employee_id from (
select employee_id from Employees
union all//联合多表
select employee_id from Salaries
) as ans
group by employee_id//按照id分组
having count(employee_id) = 1 //条件筛选,如果id只出现了一次,视为丢失
order by employee_id;//升序排列
1795.608每个产品在不同商店的价格
请你重构 Products 表,查询每个产品在不同商店的价格,使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售,则不输出这一行。
输出结果表中的 顺序不作要求 。
select
product_id,'store1' as store, store1 as price
from
Products
where
store1 is not null
union all
select
product_id,'store2', store2
from
Products
where
store2 is not null
union all
select
product_id,'store3', store3
from
Products
where
store3 is not null
608.树节点
给定一个表tree,id是树节点的编号,p_id是它父节点的id
树中每个节点属于以下三种类型之一:
叶子:如果这个节点没有任何孩子节点。
根:如果这个节点是整棵树的根,即没有父节点。
内部节点:如果这个节点既不是叶子节点也不是根节点。
# Write your MySQL query statement below
SELECT
id, 'Root' AS Type
FROM
tree
WHERE
p_id IS NULL
UNION
SELECT
id, 'Leaf' AS Type
FROM
tree
WHERE
id NOT IN (SELECT DISTINCT
p_id
FROM
tree
WHERE
p_id IS NOT NULL)
AND p_id IS NOT NULL
UNION
SELECT
id, 'Inner' AS Type
FROM
tree
WHERE
id IN (SELECT DISTINCT
p_id
FROM
tree
WHERE
p_id IS NOT NULL)
AND p_id IS NOT NULL
ORDER BY id;
176.第二高的薪水:
编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null
方法一:
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
//limit m offset n,意思是从 第n条记录开始,返回m条记录
//desc关键字
方法二:
select max(Salary) SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee)
175.组合两个表:
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId
多表的联结又分为以下几种类型:
1)左联结(left join),联结结果保留左表的全部数据
2)右联结(right join),联结结果保留右表的全部数据
3)内联结(inner join),取两表的公共数据
1581.进店却未进行过交易的顾客:
# Write your MySQL query statement below
SELECT
customer_id, COUNT(customer_id) count_no_trans
FROM
visits v//visits表的代称v
LEFT JOIN
transactions t ON v.visit_id = t.visit_id//保留左表合并,在visit_id相同的情况下
WHERE amount IS NULL//没有购物
GROUP BY customer_id;//按照customer_id 分组
1148.文章浏览1:
# Write your MySQL query statement below
select distinct author_id as id
from Views
where author_id = viewer_id
order by id;
197.上升的温度:
SELECT
weather.id AS 'Id'
FROM
weather
JOIN
weather w ON DATEDIFF(weather.recorddate, w.recorddate) = 1
AND weather.Temperature > w.Temperature
//使用datediff来比较两个日期类型的值
不使用join的第二种写法
SELECT w2.Id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
AND w1.Temperature < w2.Temperature
607.销售员:
SELECT
s.name
FROM
salesperson s
WHERE
s.sales_id NOT IN (SELECT
o.sales_id
FROM
orders o
LEFT JOIN
company c ON o.com_id = c.com_id
WHERE
c.name = 'RED')
;
mysql 查询手机号加敏
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
java手机号校验规则最新
Java手机号校验规则。
正则表达式 Java 字符串