查询一个月(5月)每天加分记录
SELECT
date(t.date),
count(1)
FROM
t_addmcoinrecord t
WHERE
date BETWEEN '2019-05-01'
AND '2019-05-30'
GROUP BY
date(t.date)
ORDER BY date(t.date)
查询近四年每月使用数据量(按日期顺序查询)
SELECT
YEAR(date), – 年份(日期) –
MONTH(date),
count(1)
FROM
t_addmcoinrecord t
WHERE
date BETWEEN ‘2015-05-01’
AND ‘2019-05-30’
GROUP BY
YEAR(date),
MONTH(date)
查询近四年每月数据量(顺序查询)
查询多少员工
select count(1) from t_user
查询加分次数
select count(1) from t_addmcoinrecord t
查询当月(5月)加分类型数据量(倒序)
SELECT
t.typeID,
tt.type_Name, -- 类型表名称 --
count(1)
FROM
t_addmcoinrecord t
LEFT JOIN t_type tt on tt.type_ID = t.typeID -- 查询加分表ID和类型表ID相同的数据 --
where
date BETWEEN '2019-05-01' AND '2019-05-30'
GROUP BY
t.typeID
ORDER BY
count(tt.type_Name) DESC -- 按照类型数量倒序排序 --
查询当月(5月)部门数据量(倒序)
select
t.departmentName,
count(1)
FROM
t_addmcoinrecord t
WHERE
date between '2019-05-01' and '2019-05-30'
GROUP BY
t.departmentName
ORDER BY
count(t.departmentName) DESC
将数据做成表格形式如下:
sql训练: