DB----函数查询

最新推荐文章于 2022-07-11 06:00:00 发布

damys

最新推荐文章于 2022-07-11 06:00:00 发布

阅读量275

点赞数

分类专栏： DB 文章标签： mysql

本文链接：https://blog.csdn.net/damys/article/details/102935093

版权

DB 专栏收录该内容

40 篇文章 0 订阅

订阅专栏

二个表的查询

使用join 二个表的结构，测试数据

表1：class
INSERT INTO `class` (`id`, `sid`, `name`) VALUES (1, 2, 'A');
INSERT INTO `class` (`id`, `sid`, `name`) VALUES (2, 3, 'B');
 
表2：school
INSERT INTO `school` (`id`, `sid`, `name`, `price`) VALUES (1, 2, 'tom', 5);
INSERT INTO `school` (`id`, `sid`, `name`, `price`) VALUES (2, 2, 'jack', 9);
INSERT INTO `school` (`id`, `sid`, `name`, `price`) VALUES (3, 3, 'rose', 4);
INSERT INTO `school` (`id`, `sid`, `name`, `price`) VALUES (4, 3, 'erice', 7);

查询

select SUM(price) from school where sid = 2
 
select a.*,SUM(b.price) from class as a JOIN school as b where a.sid = b.sid group by a.sid
 
使用别名：
select a.*,SUM(b.price) as sum_price from class as a JOIN school as b where a.sid = b.sid group by a.sid
 

一个表查询到的值作为条件查询同一个表的值
select a.*, (select school_name from table_school  where a.city_pid=school_id) as city_name 
from table_school as a where a.school_id = :school_id;

求和（函数判断）1: 查询同一个表同一字段不同类型

select a.education_sn,
    SUM(if(c.pay_type = 0,c.pay_fee,0)) as educ_fee,
    SUM(if(c.pay_type = 1,c.pay_fee,0)) as filing_fee,
    SUM(if(c.pay_type = 2,c.pay_fee,0)) as collect_fee,
    SUM(if(c.pay_type = 3,c.pay_fee,0)) as guidance_fee
from ah_education as a 
JOIN ah_pay as c on a.eid = c.eid where a.is_delete = 0 and a.eid = 107 GROUP by a.education_sn;

求和(原始)2: 查询同一个表同一字段不同类型

select a.education_sn,
	( select SUM(pay_fee) from ah_pay where pay_type = 0 and eid = 107 ) as educ_fee ,
	( select SUM(pay_fee) from ah_pay where pay_type = 1 and eid = 107 ) as filing_fee ,
	( select SUM(pay_fee) from ah_pay where pay_type = 2 and eid = 107 ) as collect_fee ,
	( select SUM(pay_fee) from ah_pay where pay_type = 3 and eid = 107 ) as guidance_fee 
from ah_education as a 
JOIN ah_pay as c on a.eid = c.eid where a.is_delete = 0 and a.eid = 107 GROUP by a.education_sn

统计

select 
		count(case when a.status = 0 then 0 end) as count0,
		count(case when a.status = 1 then 1 end) as count1,
		count(case when a.status = 2 then 2 end) as count2,
		count(case when a.status = 3 then 3 end) as count3
        from ah_reservation as a 
        where  a.submit_manager_id = 95 and a.is_delete = 0 
and DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(from_unixtime(a.create_time)) 
GROUP by a.submit_manager_id