这种统计用语句怎么写？
id carid count date
1 200710 50 2007-10-21
2 200710A 30 2007-10-21
3 200711 80 2007-10-21
4 200712 40 2007-10-21
5 200712A 20 2007-10-21
。。。。。。。。。。。。。。。。。。more
其中id 是自动的，carid带A的包含在不带A的数里，carid 只有这××××××和××××××A这两种形式
如何能统计出
carid countA count
200710 30 50
200711 0 80
200712 20 40
。。。。。。。。。。。。。more
不知道大家看明白没有？
[解决办法]
create table table1(id int,carid varchar(7),[count] int,[date] datetime);
go
insert into table1
select 1,'200710 ',50,'2007-10-21'
union select 2,'200710A',30,'2007-10-21'
union select 3,'200711 ',80,'2007-10-21'
union select 4,'200712 ',40,'2007-10-21'
union select 5,'200712A',20,'2007-10-21'
go
select * from table1
select left(carid,6) as carid,sum(case when carid like '%A' then count else 0 end) as countA,sum(case when carid like '%A' then 0 else count end) as count from table1 group by left(carid,6)
go
drop table table1
(5 行受影响)
id carid count date
----------- ------- ----------- -----------------------
1 200710 50 2007-10-21 00:00:00.000
2 200710A 30 2007-10-21 00:00:00.000
3 200711 80 2007-10-21 00:00:00.000
4 200712 40 2007-10-21 00:00:00.000
5 200712A 20 2007-10-21 00:00:00.000
(5 行受影响)
carid countA count
------- ----------- -----------
200710 30 50
200711 0 80
200712 20 40
(3 行受影响)
[解决办法]
create table tb(id int, carid varchar(10),[count] int, date datetime)insert into tb values(1, '200710' , 50, '2007-10-21') insert into tb values(2, '200710A', 30, '2007-10-21') insert into tb values(3, '200711' , 80, '2007-10-21') insert into tb values(4, '200712' , 40, '2007-10-21') insert into tb values(5, '200712A', 20, '2007-10-21') goselect t1.carid , isnull(t2.countA,0) countA,t1.[count] from(select left(carid,6) carid, sum([count]) [count] from tb group by left(carid,6)) t1 left join(select carid , sum([count]) countA from tb where len(carid) = 7 group by carid) t2on t1.carid = left(t2.carid,6)drop table tb/*carid countA count ---------- ----------- ----------- 200710 30 80200711 0 80200712 20 60（所影响的行数为 3 行）*/
