现在要统计name字段里以A、B、C字母开头的句子总数,返回结果必须要以下样式:(total字段的数字是返回以name字段对应的字母开头的总数,数字是假设的) name total
-----------------
1. A 4
2. B 12
3. C 0请问各位语法怎样写?
-----------------
1. A 4
2. B 12
3. C 0请问各位语法怎样写?
调试欢乐多
select upper(substring(name,1,1)) name, count(1) total
from tb
group by upper(substring(name,1,1));
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:tb1
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tb1')
AND type in (N'U'))
DROP TABLE tb1
GO---->建表
create table tb1([id] int,[data] varchar(20))
insert tb1
select 2,'a3213' union all
select 3,'a2313' union all
select 4,'c1531' union all
select 5,'b2341'
GO--> 查询结果
SELECT LEFT(data,1),COUNT(1)
FROM tb1
group by LEFT(data,1)
--> 删除表格
--DROP TABLE tb1
GROUP BY SUBSTRING(NAME,1,1) HAVING SUBSTRING(NAME,1,1) IN ('A','B','C')
upper(left(name,1)) as name, count(1) as total
from
tb
group by
upper(left(name,1));
--1
select left(name,1) as name, count(1) as total
from tb group by left(name,1)--2
select substring(name,1,1) as name, count(1) as total
from tb substring(name,1,1)
小F与爱觉的SQL真的很强,关键是非常热心,难得!