某个仓库的某个商品的库存如下:
storage_id ph storage_num in_date(最后到货时间)
gs14 456 2 2007-08-01
gs15 789 4 2007-04-01
gs16 012 2 2007-03-01
gs17 345 -3 2007-05-01
gs2 123 0 2007-01-01
我想得到
storage_id ph storage_num in_date
gs16 012 2 2007-03-01
gs15 789 4 2007-04-01
gs14 456 2 2007-08-01
gs17 345 -3 2007-05-01
gs2 123 0 2007-01-01
也就是说:storage_num>0的按“in_date → ph → storage_num”,而storage_num<=0的则按“storage_num → ph → in_date”请问这个SELECT如何写,谢谢。
storage_id ph storage_num in_date(最后到货时间)
gs14 456 2 2007-08-01
gs15 789 4 2007-04-01
gs16 012 2 2007-03-01
gs17 345 -3 2007-05-01
gs2 123 0 2007-01-01
我想得到
storage_id ph storage_num in_date
gs16 012 2 2007-03-01
gs15 789 4 2007-04-01
gs14 456 2 2007-08-01
gs17 345 -3 2007-05-01
gs2 123 0 2007-01-01
也就是说:storage_num>0的按“in_date → ph → storage_num”,而storage_num<=0的则按“storage_num → ph → in_date”请问这个SELECT如何写,谢谢。
union
select * from tab where storage_num<=0 order by storage_num , ph , in_date
union all
select * from tab where storage_num<=0 order by storage_num , ph , in_date
select * from tab where storage_num>0 order by in_date , ph , storage_num
union all
select * from tab where storage_num<=0 order by storage_num , ph , in_date这个不对,编译无法通过的
服务器: 消息 156,级别 15,状态 1,行 5
在关键字 'union' 附近有语法错误。
(select top 100 * from 表 where storage_num>0 order by in_date, ph, storage_num) a
union all
select * from
(select top 100 * from 表 where storage_num<=0 order by storage_num, ph, in_date) b/*
说明
表记录有多少,就“select top”多少,为什么要多此一举?因为SQL的UNION很变态:
除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
*/
-------------------------------
是啊,所以我把结果当成一个子查询,再外面在SELECT一次。怎么,结果不对吗
TO Limpire(昨夜小楼)可以用TOP 100 PERCENT,而不是TOP 10-------------------呵呵,谢谢啦,这个PERCENT我很少用,所以没有这个思维,谢谢你提醒啊!!
declare @Test table(storage_id varchar(10), ph int, storage_num int, in_date varchar(10))
insert @Test
select 'gs14',456, 2, '2007-08-01' union all
select 'gs15',789, 4, '2007-04-01' union all
select 'gs16',012, 2, '2007-03-01' union all
select 'gs17',345, -3, '2007-05-01' union all
select 'gs2',123, 0, '2007-01-01'
--select 'gs17',345, -3, '2007-05-01'
select * from @Test
order by
case when storage_num>0 then 1 else 2 end,
case when storage_num>0 then datediff(day,0,in_date) else storage_num end,
ph,
case when storage_num>0 then storage_num else datediff(day,0,in_date) end
datetime其实是浮点型,和numeric可以隐式互转,不用datediff可以。我的测试表in_date用varchar(10),例如:2007-12-01
和int不能隐式互转,所以要用datediff。