create table aaa (A_1 int ,A_2 int ,A_3 int ,A_4 int ,A_5 int , A_6 int ,A_7 int ,A_8 int ,A_9 int ,A_10 int ,A_11 int ,A_12 int)insert into aaa values (123,22,0,33,99,0,0,0,0,0,0,0) select 13-charindex('1',reverse( (case when a_1=0 then '0' else '1' end )+ (case when a_2=0 then '0' else '1' end )+ (case when a_3=0 then '0' else '1' end )+ (case when a_4=0 then '0' else '1' end )+ (case when a_5=0 then '0' else '1' end )+ (case when a_6=0 then '0' else '1' end )+ (case when a_7=0 then '0' else '1' end )+ (case when a_8=0 then '0' else '1' end )+ (case when a_9=0 then '0' else '1' end )+ (case when a_10=0 then '0' else '1' end )+ (case when a_11=0 then '0' else '1' end )+ (case when a_12=0 then '0' else '1' end ) )) from aaa
一個比較麻煩的方法﹐沒調試 select (case when a_12 > 0 then 12 when a_11 > 0 then 11 ... end) as max_row from ...
一個比較麻煩的方法﹐沒調試 select (case when a_12 > 0 then 12 when a_11 > 0 then 11 ... end) as max_row from ...
感觉用null值代替0值就更好了,否则还要用case函数替换: ------------------------------------------------------ select COALESCE(case A_12 when 0 then null else A_12 end, case A_11 when 0 then null else A_11 end, case A_10 when 0 then null else A_10 end, case A_9 when 0 then null else A_9 end, case A_8 when 0 then null else A_8 end, case A_7 when 0 then null else A_7 end, case A_6 when 0 then null else A_6 end, case A_5 when 0 then null else A_5 end, case A_4 when 0 then null else A_4 end, case A_3 when 0 then null else A_3 end, case A_2 when 0 then null else A_2 end, case A_1 when 0 then null else A_1 end) from your table
net_steven(吃素的狼)的方法也不错,看来这个问题解决方法也挺多的:) 不过他不是要得出值,是要得出所在月份。改一下就好了: select COALESCE(case A_12 when 0 then null else 12 end, case A_11 when 0 then null else 11 end, case A_10 when 0 then null else 10 end, case A_9 when 0 then null else 9 end, case A_8 when 0 then null else 8 end, case A_7 when 0 then null else 7 end, case A_6 when 0 then null else 6 end, case A_5 when 0 then null else 5 end, case A_4 when 0 then null else 4 end, case A_3 when 0 then null else 3 end, case A_2 when 0 then null else 2 end, case A_1 when 0 then null else 1 end) from aaa
(A_1 int ,A_2 int ,A_3 int ,A_4 int ,A_5 int ,
A_6 int ,A_7 int ,A_8 int ,A_9 int ,A_10 int ,A_11 int ,A_12 int)insert into aaa values (123,22,0,33,99,0,0,0,0,0,0,0)
select
13-charindex('1',reverse(
(case when a_1=0 then '0' else '1' end )+
(case when a_2=0 then '0' else '1' end )+
(case when a_3=0 then '0' else '1' end )+
(case when a_4=0 then '0' else '1' end )+
(case when a_5=0 then '0' else '1' end )+
(case when a_6=0 then '0' else '1' end )+
(case when a_7=0 then '0' else '1' end )+
(case when a_8=0 then '0' else '1' end )+
(case when a_9=0 then '0' else '1' end )+
(case when a_10=0 then '0' else '1' end )+
(case when a_11=0 then '0' else '1' end )+
(case when a_12=0 then '0' else '1' end )
))
from aaa
select (case when a_12 > 0 then 12 when a_11 > 0 then 11 ... end) as max_row from ...
select (case when a_12 > 0 then 12 when a_11 > 0 then 11 ... end) as max_row from ...
liuzxit(刀仔割大树)的方法不错。
------------------------------------------------------
select COALESCE(case A_12 when 0 then null else A_12 end,
case A_11 when 0 then null else A_11 end,
case A_10 when 0 then null else A_10 end,
case A_9 when 0 then null else A_9 end,
case A_8 when 0 then null else A_8 end,
case A_7 when 0 then null else A_7 end,
case A_6 when 0 then null else A_6 end,
case A_5 when 0 then null else A_5 end,
case A_4 when 0 then null else A_4 end,
case A_3 when 0 then null else A_3 end,
case A_2 when 0 then null else A_2 end,
case A_1 when 0 then null else A_1 end)
from your table
不过他不是要得出值,是要得出所在月份。改一下就好了:
select COALESCE(case A_12 when 0 then null else 12 end,
case A_11 when 0 then null else 11 end,
case A_10 when 0 then null else 10 end,
case A_9 when 0 then null else 9 end,
case A_8 when 0 then null else 8 end,
case A_7 when 0 then null else 7 end,
case A_6 when 0 then null else 6 end,
case A_5 when 0 then null else 5 end,
case A_4 when 0 then null else 4 end,
case A_3 when 0 then null else 3 end,
case A_2 when 0 then null else 2 end,
case A_1 when 0 then null else 1 end)
from aaa