数据库存储格式如下:
goodsXs goodsKc onlyId
1 1 20090908215208921
2 2 20090908215208921
1 1 20090908221123843 2 2 20090907221123843
1 1 20090907222220531
1 1 20090907222220531
我想要查询绑定到datagrid如下格式:
goodsXs goodsKc onlyId
4 4 20090908
4 4 20090907
goodsXs goodsKc onlyId
1 1 20090908215208921
2 2 20090908215208921
1 1 20090908221123843 2 2 20090907221123843
1 1 20090907222220531
1 1 20090907222220531
我想要查询绑定到datagrid如下格式:
goodsXs goodsKc onlyId
4 4 20090908
4 4 20090907
goodsXs goodsKc onlyId
1 1 20090908215208921
2 2 20090908215208921
1 1 20090908221123843 2 2 20090907221123843
1 1 20090907222220531
1 1 20090907222220531
就是汇总goodsXs和goodsKc 截取onlyId这一段数字的前八位
我想要查询绑定到datagrid如下格式:
goodsXs goodsKc onlyId
4 4 20090908
4 4 20090907
你要确保你的日期都是8位就可以了
select sum(goodsXs),sum(goodsKc),substring(onlyId,0,8)
from temp
group by substring(onlyId,0,8)
select Substring('20090908215208921',0,9)
declare @t1 table(goodsXs int ,goodsKc int,onlyId varchar(20))
insert into @t1 values(1 , 1 , 20090908215208921 )
insert into @t1 values(2 , 2 , 20090908215208921 )
insert into @t1 values(1 , 1 , 20090908221123843 )
insert into @t1 values(2 , 2 , 20090907221123843 )
insert into @t1 values(1 , 1 , 20090907222220531 )
insert into @t1 values(1 , 1 , 20090907222220531 )select sum(goodsXs) as goodsXs, sum(goodsKc) as goodsKc , Left( onlyId,8) as onlyId from @t1
group by Left( onlyId,8)
select sum(goodsXs) as goodsXs, sum(goodsKc) as goodsKc , Left(onlyId,8) as onlyId from tablename
group by onlyId
insert into @t1 values(1 , 1 , 20090908215208921 )
insert into @t1 values(2 , 2 , 20090908215208921 )
insert into @t1 values(1 , 1 , 20090908221123843 )
insert into @t1 values(2 , 2 , 20090907221123843 )
insert into @t1 values(1 , 1 , 20090907222220531 )
insert into @t1 values(1 , 1 , 20090907222220531 )
select goodsXs=sum(goodsXs),goodsKc=sum(goodsKc),onlyId=left(onlyId,8) from @t1 group by left(onlyId,8) order by left(onlyId,8)desc
/*
goodsXs goodsKc onlyId
4 4 20090908
4 4 20090907 */