create table family (
id int,
name varchar(10),
parentId int
) insert into family values(1,"张三",5);
insert into family values(2,"张四",5);
insert into family values(3,"李三",6);
insert into family values(4,"李四",NULL);
insert into family values(5,"张_父",7);
insert into family values(6,"李_父",8);
insert into family values(7,"张_父_父",NULL);
insert into family values(NULL,NULL,NULL);--查出如下结构:
--家族 人数
------------
--张 4
--李 2
--李 1
不会写,请教一下大家,谢谢。
id int,
name varchar(10),
parentId int
) insert into family values(1,"张三",5);
insert into family values(2,"张四",5);
insert into family values(3,"李三",6);
insert into family values(4,"李四",NULL);
insert into family values(5,"张_父",7);
insert into family values(6,"李_父",8);
insert into family values(7,"张_父_父",NULL);
insert into family values(NULL,NULL,NULL);--查出如下结构:
--家族 人数
------------
--张 4
--李 2
--李 1
不会写,请教一下大家,谢谢。
from family
group by left(name,1)
MySQL中进行树状所有子节点的查询
在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾,在MySQL的目前版本中还没有对应的功能。 在MySQL中如果是有限的层次,比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4,则我们可以直接通过left join 来实现。 但很多时候我们...
select left(name,1),count(*)
from family
group by left(name,1)可行