sql查询之嵌套查询+逗号分隔字段的统计

嵌套查询

单行嵌套查询

当主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝, >, <, >=, <=, <>）来进行比较

例子：查询一个学生借阅图书量在班级里面排名

select count(*)+1 as classtop from hdm_students    
where hdm_students.m_borrowingAmount> ( select a.m_borrowingAmount  
from hdm_students a     
where a.m_status=1 and a.m_deleted=0 and m_classId= 1  and a.m_id=2)

多行子查询使用ALL操作符号

SELECT DISTINCT 返回唯一不同的值

例子：查询有一门以上的成绩高于Kaka的最高成绩的学生的名字:

select stName
from Student
where stId in(
select distinct stId from score where score >all(
select score from score where stId=(
select stId from Student where stName= 'Kaka'
)));

多行子查询使用ANY操作符号

例子：查询有一门以上的成绩高于Kaka的最高成绩的学生的名字:

select stName
from Student
where stId in(
select distinct stId from score where score >any(
select score from score where stId=(
select stId from Student where stName='Kaka'
)));

子查询使用IN操作符号

例：查询薪水和李华相等的员工

select emp.empno,emp.ename from scott.emp where sal in (
 select sal from scott.emp where ename=’’李华”
);

在HAVING子句中使用子查询

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

例子：

SELECT job,AVG(sal) 
FROM EMP GROUP BY job
HAVING AVG(sal)>(
SELECT sal FROM EMP WHERE ename='MARTIN'
)；

在子查询中使用 limit

需要limit子查询语句的外面再包一层查询

select * from table where id in (select t.id from (select * from table limit 10)as t);

逗号分隔字段的统计

逗号分隔的字段的统计

m_labelIds为逗号分隔的字段 hdf_config 为任意一张id自增的表（存在几条数据可以查几条）

例：查询图书表中图书类型的排行

select  substring_index(substring_index(a.m_labelIds,',',b.id),',',-1) as type_id
,count( substring_index(substring_index(a.m_labelIds,',',b.id),',',-1))
from hdm_books a
join hdf_config b
on b.id <= (length(a.m_labelIds) - length(replace(a.m_labelIds,',',''))+1)
group by type_id 

嵌套查询的逗号分隔的字段的统计

select  m_id,m_name,m_remark from hdm_book_labels where m_id in 
(select * from (
select  substring_index(substring_index(a.m_labelIds,',',b.id),',',-1) as type_id
from hdm_books a
join hdf_config b
on b.id <= (length(a.m_labelIds) - length(replace(a.m_labelIds,',',''))+1)
where a.m_id in (
select  m_bookId from hdm_orders_detail hod where   m_userType=1  and hod.m_status!=0 and hod.m_status!=9 and hod.m_status!=1 and  hod.m_userId=1
)group by type_id 
limit 3
)as t)