表a:如下:
name initNo dtWorkDate mAmount mPrice mCost type
2#锂 08 2010-08-27 -400 120 -4800 out
2#锂 08 2010-08-27 500 120 6000 in
HL46# 07 2010-08-30 -100 50 -500 out
HL46# 07 2010-08-12 -20 50 -100 out
HL46# 07 2010-07-30 120 50 600 in现在我要得到如下顺序排列的表:
name initNo dtWorkDate mAmount mPrice mCost type
2#锂 08 2010-08-27 500 120 6000 in
2#锂 08 2010-08-27 -400 120 -4800 out
HL46# 07 2010-07-30 120 50 600 in
HL46# 07 2010-08-12 -20 50 -100 out
HL46# 07 2010-08-30 -100 50 -500 out要求:
按name、initNo分组,按dtWorkDate排序,如果dtWorkDate相同,按type=in的排在type=out之前
name initNo dtWorkDate mAmount mPrice mCost type
2#锂 08 2010-08-27 -400 120 -4800 out
2#锂 08 2010-08-27 500 120 6000 in
HL46# 07 2010-08-30 -100 50 -500 out
HL46# 07 2010-08-12 -20 50 -100 out
HL46# 07 2010-07-30 120 50 600 in现在我要得到如下顺序排列的表:
name initNo dtWorkDate mAmount mPrice mCost type
2#锂 08 2010-08-27 500 120 6000 in
2#锂 08 2010-08-27 -400 120 -4800 out
HL46# 07 2010-07-30 120 50 600 in
HL46# 07 2010-08-12 -20 50 -100 out
HL46# 07 2010-08-30 -100 50 -500 out要求:
按name、initNo分组,按dtWorkDate排序,如果dtWorkDate相同,按type=in的排在type=out之前
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (name varchar(5),initNo varchar(2),dtWorkDate datetime,mAmount int,mPrice int,mCost int,type varchar(3))
insert into #tb
select '2#锂','08','2010-08-27',-400,120,-4800,'out' union all
select '2#锂','08','2010-08-27',500,120,6000,'in' union all
select 'HL46#','07','2010-08-30',-100,50,-500,'out' union all
select 'HL46#','07','2010-08-12',-20,50,-100,'out' union all
select 'HL46#','07','2010-07-30',120,50,600,'in'select * from #tb
order by name,initNo,case when type='in' then 0 else 1 end ,dtWorkDate name initNo dtWorkDate mAmount mPrice mCost type
----- ------ ----------------------- ----------- ----------- ----------- ----
2#锂 08 2010-08-27 00:00:00.000 500 120 6000 in
2#锂 08 2010-08-27 00:00:00.000 -400 120 -4800 out
HL46# 07 2010-07-30 00:00:00.000 120 50 600 in
HL46# 07 2010-08-12 00:00:00.000 -20 50 -100 out
HL46# 07 2010-08-30 00:00:00.000 -100 50 -500 out(5 行受影响)
order by name,initNo,type,dtWorkDate