现在表里面有AXXXXXX 2011-02-01
AXXXXXX 2010-02-01
BXXXXXX 2011-02-01
AXXXXXX 2011-02-01
AXXXXXX 2009-02-01
AXXXXXX 2011-02-01
ZXXXXXX 2011-02-01我想查询出来2011年A开头的记录有多少条,B开头的记录有多少条,C。。Z开头的记录有多少条,请问怎么实现?
如果执行26个字母26次的话效率太底了,数据库里面三百多万条数据,我机子差点死机。有没有办法一条SQL语句搞定?
AXXXXXX 2010-02-01
BXXXXXX 2011-02-01
AXXXXXX 2011-02-01
AXXXXXX 2009-02-01
AXXXXXX 2011-02-01
ZXXXXXX 2011-02-01我想查询出来2011年A开头的记录有多少条,B开头的记录有多少条,C。。Z开头的记录有多少条,请问怎么实现?
如果执行26个字母26次的话效率太底了,数据库里面三百多万条数据,我机子差点死机。有没有办法一条SQL语句搞定?
select type=left(b,4),num=count(1) from ta group by left(b,4)union all
select left(a,1),count(1) from ta group by left(a,1)
left(col,1),count(1) as 次数
from
tb
group by
left(col,1)
where year([date])=2011
group by left(col1,1)
where year([date])=2011
group by left(col1,1)
select 'AXXXXXX','2011-02-01' union
select 'AXXXXXX','2010-02-01' union
select 'BXXXXXX','2011-02-01' union
select 'AXXXXXX','2011-03-01' union
select 'AXXXXXX','2009-02-01' union
select 'AXXXXXX','2011-04-01' union
select 'ZXXXXXX','2011-02-01'
SELECT left(ZM,1),count(1)from tb1,master..spt_values
WHERE type='P' and number<=25
and left(ZM,1)=CHAR(ASCII('A')+number)
and datename(yy,date1)='2011'
group by left(ZM,1)drop table tb1
select 'AXXXXXX','2011-02-01' union
select 'AXXXXXX','2010-02-01' union
select 'BXXXXXX','2011-02-01' union
select 'AXXXXXX','2011-03-01' union
select 'AXXXXXX','2009-02-01' union
select 'AXXXXXX','2011-04-01' union
select 'ZXXXXXX','2011-02-01'
SELECT left(ZM,1),count(1)from tb1
WHERE left(ZM,1) like '[A-Z]' and datename(yy,date1)='2011'
group by left(ZM,1)drop table tb1
可否问下count(1)是什么意思?
insert into @t
select 'AXXXXXX', '2011-02-01' union all
select 'AXXXXXX', '2010-02-01' union all
select 'BXXXXXX', '2011-02-01' union all
select 'AXXXXXX', '2011-02-01' union all
select 'AXXXXXX', '2009-02-01' union all
select 'AXXXXXX', '2011-02-01' union all
select 'ZXXXXXX', '2011-02-01' select left(item,1) item,left(dt,4) dt,count(dt) cx
from @t
group by left(item,1),left(dt,4)