Mysql函数
一,列表分组之后查询所有组内某一个时间字段最大的数据
ContractEndDate :某一个时间字段
RECORDID:表ID
SELECT A.* from (select * from (SELECT * FROM hrm_contract order by ContractEndDate DESC limit 10000) a GROUP BY a.A00) B
left join hrm_contract A on a.RECORDID=b.RECORDID
二,多级菜单表根据parent_id查询包括父节点在内的所有子节点id
https://blog.csdn.net/haogexiaole/article/details/85110127
SELECT
acct_id
FROM
(
SELECT
t1.acct_id,
IF
( find_in_set( prd_acct_id, @pids ) > 0, @pids := concat( @pids, ',', acct_id ), 0 ) AS ischild
FROM
( SELECT acct_id, prd_acct_id FROM ff_acct t ORDER BY prd_acct_id, acct_id ) t1,
( SELECT @pids := '43bc7cfd875268a2e211eddad895da6b' ) t2
) t3
WHERE
ischild != 0 UNION
SELECT
'43bc7cfd875268a2e211eddad895da6b' AS acct_id
FROM
DUAL
三,多级菜单表根据子节点id查询包括子节点在内的所有父节点parent_id
表结构
查询sql
SELECT T2.id
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM user WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 7, @l := 0) vars, -- 查询id为5的所有上级
user h
WHERE @r <> 0) T1
JOIN user T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
查询结果
四,group by 对多个字段进行分组
group by 字段一:将所有具有相同字段一的记录放到一个分组里
group by 字段一,字段二 :将所有具有相同字段一和字段二的记录放到一个分组里
五,Case When和聚合函数count、sum的使用
根据贫困标志,取出不同标准下的的人数有多少,以区县位单位。
1代表已脱贫人口;2代表返贫人口;3代表未脱贫
select
t3.region_id as 'regionId',
t3.region_name as 'regionName',
count(case when t1.tricolor = 1 then 1 else null end ) as 'greenTotal',
count(case when t1.tricolor = 2 then 1 else null end ) as 'yellowTotal',
count(case when t1.tricolor = 3 then 1 else null end )as 'redTotal'
from pa_household_member t1
left join pa_household t2 on t2.household_id = t1.household_id
left join system_region t3 on t3.region_id = t2.area
group by t2.area
count(1) 算上一条记录 count(null)为0;利用此特性进行分类统计。不用写复杂的子查询。count在套住case when ..... 例如: count (case when ....end )
作者:樊同学
-------------------------------------------
个性签名:独学而无友,则孤陋而寡闻。做一个灵魂有趣的人!
如果觉得这篇文章对你有小小的帮助的话,记得在右下角点个“推荐”哦,博主在此感谢!
万水千山总是情,打赏一分行不行,所以如果你心情还比较高兴,也是可以扫码打赏博主,哈哈哈(っ•̀ω•́)っ✎⁾⁾!