求统计语句,对SIZE字段,前1位是4的 在SUM新的字段中显示2,前1位是3的 在SUM新的字段中显示1jobno operatedate size SUM
-------------------- ----------------------- --------------------
060801350 2006-08-28 00:00:00.000 40P 2
060801328 2006-08-29 00:00:00.000 40C 2
060801297 2006-08-28 00:00:00.000 40H 2
060801348 2006-08-29 00:00:00.000 40H 2
060801353 2006-08-29 00:00:00.000 20G 1
operatedate,
size,
[SUM]=case left(size,1) when 4 then 2 when 3 then 1 else [sum] end
from tb
select jobno,operatedate,size,
sum=case when left(size,1)='4' then 2 when left(size,1)='3' then 1 else 0 end from 表名
use PracticeDB
go
if exists (select 1 from sysobjects where name ='tb')
drop table tb
go
create table tb (jobno varchar(10),operatedate datetime,size varchar(5))
insert into tb
select '060801350', '2006-08-28 00:00:00.000', '40P' union all
select '060801328', '2006-08-29 00:00:00.000', '40C' union all
select '060801297', '2006-08-28 00:00:00.000', '40H' union all
select '060801348', '2006-08-29 00:00:00.000', '40H' union all
select '060801353', '2006-08-29 00:00:00.000', '20G'
SELECT * FROM TBjobno operatedate size
060801350 2006-08-28 00:00:00.000 40P
060801328 2006-08-29 00:00:00.000 40C
060801297 2006-08-28 00:00:00.000 40H
060801348 2006-08-29 00:00:00.000 40H
060801353 2006-08-29 00:00:00.000 20Gselect *,case when LEFT(size,1)=4 then 2
when LEFT(size,1)=2 then 1
else 0 end as SUM
from tbjobno operatedate size SUM
060801350 2006-08-28 00:00:00.000 40P 2
060801328 2006-08-29 00:00:00.000 40C 2
060801297 2006-08-28 00:00:00.000 40H 2
060801348 2006-08-29 00:00:00.000 40H 2
060801353 2006-08-29 00:00:00.000 20G 1