未系统学习过sql，通过搜索完成的一些sql语句组合逻辑

阅读必看

本人还没学习过sql，这些sql语句是通过要完成的逻辑从搜索引擎上搜到后完成的，只做留念使用！

多表联合查询

需要完成的逻辑：

首先通过群id分组，查询出每个群的成员，然后查询每个群的成员所带的不同装备的总数

代码：

	select GID_id,GName,装备名,count(Equips.id) as 装备总数
    from RescueGroupGhat join  Users on RescueGroupGhat.id = Users.GID_id
    join Equips on Users.id =Equips.Euid_id
    group by GID_id,装备名;

理解:

在执行完join on之后就会形成一张临时的中间表，然后针对临时表在进行where筛选、之后分组、排序、分页等操作，这样就将连表查询条件和数据过滤条件严格分开，不可以将连表查询条件写在where子句中

根据自己的条件分组

需要完成的逻辑：

要查询中国各分区的人数，但是数据库中存得是省份。

代码：

SELECT region as '地区', COUNT(*) as '人数' FROM
                    (
SELECT
case
WHEN RescuedPlace LIKE BINARY '湖北%' OR RescuedPlace LIKE BINARY '河南%' OR RescuedPlace LIKE BINARY '湖南%' THEN '华中地区'
WHEN RescuedPlace LIKE BINARY '山西%' OR RescuedPlace LIKE BINARY '河北%' OR RescuedPlace LIKE BINARY '北京%' OR RescuedPlace LIKE BINARY '天津%' OR RescuedPlace LIKE BINARY '内蒙古%' THEN '华北地区'	
WHEN RescuedPlace LIKE BINARY '浙江%' OR RescuedPlace LIKE BINARY '安徽%' OR RescuedPlace LIKE BINARY '上海%' OR RescuedPlace LIKE BINARY '江西%' OR RescuedPlace LIKE BINARY '山东%' OR RescuedPlace LIKE BINARY '江苏%' OR RescuedPlace LIKE BINARY '福建%' OR RescuedPlace LIKE BINARY '台湾%' THEN '华东地区'
WHEN RescuedPlace LIKE BINARY '广西%' OR RescuedPlace LIKE BINARY '广东%' OR RescuedPlace LIKE BINARY '海南%' OR RescuedPlace LIKE BINARY '香港%' OR RescuedPlace LIKE BINARY '澳门%' THEN '华南地区'
WHEN RescuedPlace LIKE BINARY '青海%' OR RescuedPlace LIKE BINARY '宁夏%' OR RescuedPlace LIKE BINARY '陕西%' OR RescuedPlace LIKE BINARY '甘肃%' OR RescuedPlace LIKE BINARY '新疆%' THEN '西北地区'
WHEN RescuedPlace LIKE BINARY '吉林%' OR RescuedPlace LIKE BINARY '黑龙江%' OR RescuedPlace LIKE BINARY '辽宁%' THEN '东北地区'
WHEN RescuedPlace LIKE BINARY '贵州%' OR RescuedPlace LIKE BINARY '云南%' OR RescuedPlace LIKE BINARY '重庆%' OR RescuedPlace LIKE BINARY '四川%' OR RescuedPlace LIKE BINARY '西藏%' THEN '西南地区'
                        
END 
as region FROM RescueGroupGhat
)a GROUP BY region

理解：

这个我也不知道怎么理解，select 后面的region 就是数据库中不存在的字段，是我们自己的条件生成的临时字段，后面还有一个它的进阶sb版

逻辑：

逻辑升级，根据月份分组，再根据中国各分区分组求各分区各月份的总人数

代码：
(补充按月份进行分组的代码):

select month(date_no) as month
from earnings 
where year(date_no) = 2017  
group by month (date_no) 

或者（下面的代码百度搜的）
时间格式
SELECT DATE_FORMAT(create_time,'%Y%u') weeks FROM role GROUP BY weeks;  
SELECT DATE_FORMAT(create_time,'%Y%m%d') days FROM role GROUP BY days;   
SELECT DATE_FORMAT(create_time,'%Y%m') months FROM role GROUP BY months   
时间戳格式
SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks FROM role GROUP BY weeks;  
SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days FROM role GROUP BY days;   
SELECT FROM_UNIXTIME(create_time,'%Y%m') months FROM role GROUP BY months   

完成逻辑的代码：

SELECT month (RescuedTime) as '月份', region as '地区', COUNT(*) as '人数' FROM
(
	SELECT
	case
	WHEN RescuedPlace LIKE BINARY '湖北%' OR RescuedPlace LIKE BINARY '河南%' OR RescuedPlace LIKE BINARY '湖南%' THEN '华中地区'
	WHEN RescuedPlace LIKE BINARY '山西%' OR RescuedPlace LIKE BINARY '河北%' OR RescuedPlace LIKE BINARY '北京%' OR RescuedPlace LIKE BINARY '天津%' OR RescuedPlace LIKE BINARY '内蒙古%' THEN '华北地区'	
	WHEN RescuedPlace LIKE BINARY '浙江%' OR RescuedPlace LIKE BINARY '安徽%' OR RescuedPlace LIKE BINARY '上海%' OR RescuedPlace LIKE BINARY '江西%' OR RescuedPlace LIKE BINARY '山东%' OR RescuedPlace LIKE BINARY '江苏%' OR RescuedPlace LIKE BINARY '福建%' OR RescuedPlace LIKE BINARY '台湾%' THEN '华东地区'
	WHEN RescuedPlace LIKE BINARY '广西%' OR RescuedPlace LIKE BINARY '广东%' OR RescuedPlace LIKE BINARY '海南%' OR RescuedPlace LIKE BINARY '香港%' OR RescuedPlace LIKE BINARY '澳门%' THEN '华南地区'
	WHEN RescuedPlace LIKE BINARY '青海%' OR RescuedPlace LIKE BINARY '宁夏%' OR RescuedPlace LIKE BINARY '陕西%' OR RescuedPlace LIKE BINARY '甘肃%' OR RescuedPlace LIKE BINARY '新疆%' THEN '西北地区'
	WHEN RescuedPlace LIKE BINARY '吉林%' OR RescuedPlace LIKE BINARY '黑龙江%' OR RescuedPlace LIKE BINARY '辽宁%' THEN '东北地区'
	WHEN RescuedPlace LIKE BINARY '贵州%' OR RescuedPlace LIKE BINARY '云南%' OR RescuedPlace LIKE BINARY '重庆%' OR RescuedPlace LIKE BINARY '四川%' OR RescuedPlace LIKE BINARY '西藏%' THEN '西南地区'
	
	END 
	as region,RescuedTime FROM RescueGroupGhat
)a GROUP BY region, month (RescuedTime) 

理解：

就是将按自己定义的条件分组的基础上想办法加上按日期分组，再SELECT后加上月份【month (RescuedTime)】进行输出，最后GROUP BY要加上月份【month (RescuedTime)】分组，如果只是这样那么一定出错，会报一个没有日期字段的错误
需要在倒数第二行as 后面加上日期的字段【as region,！！RescuedTime！！ FROM RescueGroupGhat】，个人认为有临时字段的原因吧，反正能解决逻辑就是好办法！淦

查询今天为基准前七天的数据

DAYOFWEEK(a.date)这个是将日期转换成周几的函数，但是得到的数值会比真正的周几大一个数，这样我们都减一 DAYOFWEEK(a.date)-1 ，此时周日就是0，其他的就对应起来了

sql:

	SELECT DAYOFWEEK(a.date)-1 as '周几' ,IFNULL(b.count,0) as count
	FROM
	(
	SELECT CURDATE() as date
	UNION ALL
	SELECT DATE_SUB(CURDATE(),INTERVAL 1 day)as date
	UNION ALL
	SELECT DATE_SUB(CURDATE(),INTERVAL 2 day)as date
	UNION ALL
	SELECT DATE_SUB(CURDATE(),INTERVAL 3 day)as date
	UNION ALL
	SELECT DATE_SUB(CURDATE(),INTERVAL 4 day)as date
	UNION ALL
	SELECT DATE_SUB(CURDATE(),INTERVAL 5 day)as date
	UNION ALL
	SELECT DATE_SUB(CURDATE(),INTERVAL 6 day)as date
	)a 
	LEFT JOIN
	(
	select date_format(MessageTime,'%Y-%m-%d') as date, 
	count(1) as count 
	from MessagePush 
	where MessageTime >= date(now()) - interval 7 day 
	group by day(MessageTime)
	)b 

	on a.date = b.date
	ORDER BY a.date desc

结尾

淦，giao~加油