22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#ABC') is null
drop table #ABC
Go
Create table #ABC([a] int,[b] int,[c] int,[d] int)
Insert #ABC
select 0,1,0,2 union all
select 1,2,0,3 union all
select 0,0,6,1
Go
--测试数据结束
SELECT CASE WHEN a > 1 THEN 'a'
ELSE ''
END AS 符合条件字段1 ,
CASE WHEN b > 1 THEN 'b'
ELSE ''
END AS 符合条件字段2 ,
CASE WHEN c > 1 THEN 'c'
ELSE ''
END AS 符合条件字段3 ,
CASE WHEN d > 1 THEN 'd'
ELSE ''
END AS 符合条件字段4
FROM #ABC
WHERE a > 1
OR b > 1
OR c > 1
OR d > 1
;with abc (ID,a,b,c,d) AS (
select 1,0.3,1,0.4 ,2 union all
select 2,2,0.1,0.5 ,2 union all
select 3,0.3,1,0.4 ,0.2 union all
select 4,0.3,2,0.4 ,2 union all
select 5,3,1,4,2
)
select * from abc
cross apply(values('a',a),('b',b),('c',c),('d',d))c(col,val)
where c.val<1
+----+-----+-----+-----+-----+-----+-----+
| ID | a | b | c | d | col | val |
+----+-----+-----+-----+-----+-----+-----+
| 1 | 0.3 | 1 | 0.4 | 2 | a | 0.3 |
| 1 | 0.3 | 1 | 0.4 | 2 | c | 0.4 |
| 2 | 2 | 0.1 | 0.5 | 2 | b | 0.1 |
| 2 | 2 | 0.1 | 0.5 | 2 | c | 0.5 |
| 3 | 0.3 | 1 | 0.4 | 0.2 | a | 0.3 |
| 3 | 0.3 | 1 | 0.4 | 0.2 | c | 0.4 |
| 3 | 0.3 | 1 | 0.4 | 0.2 | d | 0.2 |
| 4 | 0.3 | 2 | 0.4 | 2 | a | 0.3 |
| 4 | 0.3 | 2 | 0.4 | 2 | c | 0.4 |
+----+-----+-----+-----+-----+-----+-----+