SQL刷题记录2

用户180554289221
136 阅读3分钟

5/13 23:25 唉还是学习吧，再刷7道题

拼接函数

day3是拼接函数专场吧
1、功能：将多个字符串连接成一个字符串。
2、语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
3、举例：
例1:select concat (id, name, score) as info from tt2;
中间有一行为null是因为tt2表中有一行的score值为null。

select user_id,
CONCAT(Upper(left(name,1)),Lower(substring(name,2))) name 
from users 
order by user_id;

select
    sell_date,
    count(distinct product) num_sold,
    GROUP_CONCAT(distinct product) products
from
    activities
group by sell_date
order by sell_date;

正则表达式

在MySQL中，RLIKE运算符用于确定字符串是否匹配正则表达式。它是REGEXP_LIKE()的同义词。 如果字符串与提供的正则表达式匹配，则结果为1，否则为0。

select 
    * 
from 
    patients 
where 
    conditions rlike '^DIAB1|.*\\sDIAB1';

select patient_id,patient_name,conditions 
from Patients
where conditions regexp '^DIAB1.*|[[:space:]]DIAB1.*' 
#[[:space:]]代表空格，分类：即有空格和无空格两种

UNION

# mysql不支持满外联结，所以我们考虑用union分别查询之后拼在一起
#上面这个是缺薪水的
select e.employee_id
from employees as e left outer join salaries as s
on e.employee_id = s.employee_id
where s.salary is null
union #Union可以去重
#下面这个是缺名字的
select s.employee_id
from employees as e right outer join salaries as s
on e.employee_id = s.employee_id
where e.name is null
order by employee_id;

select 
    employee_id 
from 
    (
    select employee_id from employees
    union all 
    select employee_id from salaries
) as t
group by 
    employee_id
having 
    count(employee_id) = 1
order by 
    employee_id;

行列互转

行转列列转行一般就是两个思路

  1. CASE WHEN
  2. UNION ALL 不会对结果去重，效率比 UNION 更高。 如果结果集中存在重复数据建议使用 UNION 本题的具体解法：
  3. 一列一列处理：把“列名”做为新列的value（如本题的store），把原来的value也作为新列（如本题的price），这是一个查询，其他列不要
  4. 用union all拼接每一列的结果
  5. 注意本题如果这一产品在商店里没有出售，则不输出这一行，所以要原列 is not null的筛选条件
# Write your MySQL query statement below
select product_id, 'store1' as store, store1 as price
from Products where store1 is not null
union all
select product_id, 'store2' as store, store2 as price
from Products where store2 is not null
union all
select product_id, 'store3' as store, store3 as price
from Products where store3 is not null;

当然啦，there is example for行转列

SELECT name,
  MAX(CASE WHEN subject='语文' THEN score ELSE 0 END) AS "语文",
  MAX(CASE WHEN subject='数学' THEN score ELSE 0 END) AS "数学",
  MAX(CASE WHEN subject='英语' THEN score ELSE 0 END) AS "英语"
FROM student1
GROUP BY name;

#将不同的薪资按降序排序，然后使用LIMIT子句获得第二高的薪资
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

使用 UNION 

# 解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

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;

方法 II：使用流控制语句 CASE 

SELECT
    id AS `Id`,
    CASE
        WHEN tree.id = (SELECT atree.id FROM tree atree WHERE atree.p_id IS NULL)
          THEN 'Root'
        WHEN tree.id IN (SELECT atree.p_id FROM tree atree)
          THEN 'Inner'
        ELSE 'Leaf'
    END AS Type
FROM
    tree
ORDER BY `Id`;

方法 III；使用 IF 函数

SELECT
    id AS `Id`,
    CASE
        WHEN tree.id = (SELECT atree.id FROM tree atree WHERE atree.p_id IS NULL)
          THEN 'Root'
        WHEN tree.id IN (SELECT atree.p_id FROM tree atree)
          THEN 'Inner'
        ELSE 'Leaf'
    END AS Type
FROM
    tree
ORDER BY `Id`
avatar
文章
阅读
粉丝