求个SQL或者过程:有一个student表name字段,查询时要实现功能
name
李大
李大山
李自成
李小成
查询表的时候“select * from student where title like '李%'”,要对表结果进行分组“Group by 李_”,加一位分组,并统计结果,结果集如下
name count
李大 2
李小 1
李自 1
name
李大
李大山
李自成
李小成
查询表的时候“select * from student where title like '李%'”,要对表结果进行分组“Group by 李_”,加一位分组,并统计结果,结果集如下
name count
李大 2
李小 1
李自 1
from (
select * from student where title like '李%')
group by substr(name,2)
select substr(name,1,2) as name ,sum(1) as count
from student
group by substr(name,1,2) [/code]
from student
group by substr(name,1,2)
with tmp as
(
select '李大' name from dual
union all
select '李大山' name from dual
union all
select '李自成' name from dual
union all
select '李小成' name from dual
)
select name, sum(cnt)
from(select substr(name,1,2) name,count(*) cnt
from tmp
group by substr(name,1,2)
)
group by name;
[/code]
with tmp as
(
select '李大' name from dual
union all
select '李大山' name from dual
union all
select '李自成' name from dual
union all
select '李小成' name from dual
)
select name, sum(cnt)
from(select substr(name,1,2) name,count(*) cnt
from tmp
group by substr(name,1,2)
)
group by name;
[/code]
[code=SQ]
SQL> with tmp as
2 (
3 select '李大' name from dual
4 union all
5 select '李大山' name from dual
6 union all
7 select '李自成' name from dual
8 union all
9 select '李小成' name from dual
10 )
11 select name, sum(cnt)
12 from(select substr(name,1,2) name,count(*) cnt
13 from tmp
14 group by substr(name,1,2)
15 )
16 group by name
17 ;
NAME SUM(CNT)
------ ----------
李大 2
李小 1
李自 1
[/code]
from student
group by substr(name,1,2);
[/code]
select substr(name,1,2) as name ,count(*) as count
from student
group by substr(name,1,2);[/code]