select id, case when t1 is not null then 1 when t2 is not null then 2 when t3 is not null then 3 when t4 is not null then 4 when t5 is not null then 5 ELSE 0 END from 表
那如果是两个XX呢,X是内容:(
select id,patindex('%0%',T1+T2+T3+T4+T5) from 表
T1-T5为字符型 select id,charindex('A',(case T1 when '0' then 'A' else 'B' end) + (case T2 when '0' then 'A' else 'B' end) + (case T3 when '0' then 'A' else 'B' end) + (case T4 when '0' then 'A' else 'B' end) + (case T5 when '0' then 'A' else 'B' end)) as index from tablenameT1-T5为数字型 select id,charindex('A',(case T1 when 0 then 'A' else 'B' end) + (case T2 when 0 then 'A' else 'B' end) + (case T3 when 0 then 'A' else 'B' end) + (case T4 when 0 then 'A' else 'B' end) + (case T5 when 0 then 'A' else 'B' end)) as index from tablename
select ID,charindex('A',(case T1 when '0' then 'A' else 'B' end) + (case T2 when '0' then 'A' else 'B' end) + (case T3 when '0' then 'A' else 'B' end) + (case T4 when '0' then 'A' else 'B' end) + (case T5 when '0' then 'A' else 'B' end)) as [index]
select ID,charindex('A',(case T1 when '0' then 'A' else 'B' end) + (case T2 when '0' then 'A' else 'B' end) + (case T3 when '0' then 'A' else 'B' end) + (case T4 when '0' then 'A' else 'B' end) + (case T5 when '0' then 'A' else 'B' end)) as [index] FROM yourtable
select id,patindex('%[^X]%',T1+T2+T3+T4+T5) from 表
id,
case
when t1 is not null then 1
when t2 is not null then 2
when t3 is not null then 3
when t4 is not null then 4
when t5 is not null then 5
ELSE 0
END
from 表
select id,charindex('A',(case T1 when '0' then 'A' else 'B' end) +
(case T2 when '0' then 'A' else 'B' end) +
(case T3 when '0' then 'A' else 'B' end) +
(case T4 when '0' then 'A' else 'B' end) +
(case T5 when '0' then 'A' else 'B' end)) as index
from tablenameT1-T5为数字型
select id,charindex('A',(case T1 when 0 then 'A' else 'B' end) +
(case T2 when 0 then 'A' else 'B' end) +
(case T3 when 0 then 'A' else 'B' end) +
(case T4 when 0 then 'A' else 'B' end) +
(case T5 when 0 then 'A' else 'B' end)) as index
from tablename
(case T2 when '0' then 'A' else 'B' end) +
(case T3 when '0' then 'A' else 'B' end) +
(case T4 when '0' then 'A' else 'B' end) +
(case T5 when '0' then 'A' else 'B' end)) as [index]
(case T2 when '0' then 'A' else 'B' end) +
(case T3 when '0' then 'A' else 'B' end) +
(case T4 when '0' then 'A' else 'B' end) +
(case T5 when '0' then 'A' else 'B' end)) as [index]
FROM yourtable