create table #t (item_id varchar(40) ,--商品id
tran_date datetime ,--交易日期
tran_qty numeric(20,6)---交易数量
)
--由于同一天存在多笔交易,因此,需按交易日期分组计算日交易数量,
--再按日交易数量排序--示例数据如下:
insert into #t
select 'a01','2006-02-15 14:00',500
union
select 'a01','2006-02-15 18:00',100
union
select 'a01','2006-02-16 11:00',2000
union
select 'a01','2006-02-18 09:00',3000
union
select 'a01','2006-02-19 20:00',500select * from #t a order by
(select sum(tran_qty) from #t b where
convert(char(10),a.tran_date,120)=convert(char(10),b.tran_date,120)) descdrop table #titem_id tran_date tran_qty
---------------------------------------- ------------------------------------------------------ ----------------------
a01 2006-02-18 09:00:00.000 3000.000000
a01 2006-02-16 11:00:00.000 2000.000000
a01 2006-02-15 14:00:00.000 500.000000
a01 2006-02-15 18:00:00.000 100.000000
a01 2006-02-19 20:00:00.000 500.000000(所影响的行数为 5 行)
tran_date datetime ,--交易日期
tran_qty numeric(20,6)---交易数量
)
--由于同一天存在多笔交易,因此,需按交易日期分组计算日交易数量,
--再按日交易数量排序--示例数据如下:
insert into #t
select 'a01','2006-02-15 14:00',500
union
select 'a01','2006-02-15 18:00',100
union
select 'a01','2006-02-16 11:00',2000
union
select 'a01','2006-02-18 09:00',3000
union
select 'a01','2006-02-19 20:00',500select * from #t a order by
(select sum(tran_qty) from #t b where
convert(char(10),a.tran_date,120)=convert(char(10),b.tran_date,120)) descdrop table #titem_id tran_date tran_qty
---------------------------------------- ------------------------------------------------------ ----------------------
a01 2006-02-18 09:00:00.000 3000.000000
a01 2006-02-16 11:00:00.000 2000.000000
a01 2006-02-15 14:00:00.000 500.000000
a01 2006-02-15 18:00:00.000 100.000000
a01 2006-02-19 20:00:00.000 500.000000(所影响的行数为 5 行)
,请教各位,有没有其它性能比较好一些的写法?多谢!