create table TMAA (名称 VARCHAR(10),价格一 INT ,价格二 INT,价格三 INT ) INSERT INTO TMAA SELECT '铅笔',0,1,2 UNION ALL SELECT '铅笔2',3,1,2 UNION ALL SELECT '铅笔3',0,1,0 UNION ALL SELECT '铅笔4',5,1,2 UNION ALL SELECT '铅笔5',0,0,0 select 名称,价格=(case when 价格一=0 then case when 价格二 =0 then 价格三 else 价格二 end else 价格一 end) from TMAA 结果: 铅笔 1 铅笔2 3 铅笔3 1 铅笔4 5 铅笔5 0
create table #1(name varchar(50),j1 decimal(10,1),j2 decimal(10,1),j3 decimal(10,1)) insert into #1 select '铅笔',0,1.2,1.5 insert into #1 select '铅笔1',1,1.8,1.3 insert into #1 select '铅笔2',1.2,2.6,2.1insert into #1 select '铅笔3',0,0,2.2 insert into #1 select '铅笔4',1,0,0 insert into #1 select '铅笔5',1,2,2.2 insert into #1 select '铅笔1',0,0,0select name ,isnull(coalesce(j1,j2,j3),0) as '价格' from ( select name, case when j1=0 then null else j1 end as j1 , case when j2=0 then null else j2 end as j2 , case when j3=0 then null else j3 end as j3 from #1 )t铅笔 1.2 铅笔1 1.0 铅笔2 1.2 铅笔3 2.2 铅笔4 1.0 铅笔5 1.0 铅笔1 0.0
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([名称] nvarchar(5),[价格一] decimal(18,2),[价格二] decimal(18,2),[价格三] decimal(18,2)) Insert #T select N'铅笔',0,1.2,1.5 union all select N'铅笔1',1,1.8,1.3 union all select N'铅笔2',1.2,2.6,2.1 union all select N'铅笔3',0,0,2.2 union all select N'铅笔4',1,0,0 union all select N'铅笔5',1,2,2.2 union all select N'铅笔1',0,0,0 Go Select [名称], [价格]=isnull(nullif(isnull(nullif([价格一],0),[价格二]),0),[价格三]) from #T(7 行受影响) 名称 价格 ----- --------------------------------------- 铅笔 1.20 铅笔1 1.00 铅笔2 1.20 铅笔3 2.20 铅笔4 1.00 铅笔5 1.00 铅笔1 0.00(7 行受影响)
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([名称] nvarchar(5),[价格一] decimal(18,2),[价格二] decimal(18,2),[价格三] decimal(18,2)) Insert #T select N'铅笔',0,1.2,1.5 union all select N'铅笔1',1,1.8,1.3 union all select N'铅笔2',1.2,2.6,2.1 union all select N'铅笔3',0,0,2.2 union all select N'铅笔4',1,0,0 union all select N'铅笔5',1,2,2.2 union all select N'铅笔1',0,0,0 Go select 名称, 价格=case when 价格一=0 then case when 价格二 =0 then 价格三 else 价格二 end else 价格一 end from #T名称 价格 ----- --------------------------------------- 铅笔 1.20 铅笔1 1.00 铅笔2 1.20 铅笔3 2.20 铅笔4 1.00 铅笔5 1.00 铅笔1 0.00(7 行受影响)
create table #1(name varchar(50),j1 decimal(10,1),j2 decimal(10,1),j3 decimal(10,1)) insert into #1 select '铅笔',0,1.2,1.5 insert into #1 select '铅笔1',1,1.8,1.3 insert into #1 select '铅笔2',1.2,2.6,2.1insert into #1 select '铅笔3',0,0,2.2 insert into #1 select '铅笔4',1,0,0 insert into #1 select '铅笔5',1,2,2.2 insert into #1 select '铅笔1',0,0,0select name , isnull(coalesce(case when j1=0 then null else j1 end, case when j2=0 then null else j2 end, case when j3=0 then null else j3 end),0) as '价格' from #1 /*name 价格 -------------------------------------------------- ------------ 铅笔 1.2 铅笔1 1.0 铅笔2 1.2 铅笔3 2.2 铅笔4 1.0 铅笔5 1.0 铅笔1 .0(所影响的行数为 7 行) */drop table #1
create table TMAA (名称 VARCHAR(10),价格一 INT ,价格二 INT,价格三 INT )
INSERT INTO TMAA
SELECT '铅笔',0,1,2 UNION ALL
SELECT '铅笔2',3,1,2 UNION ALL
SELECT '铅笔3',0,1,0 UNION ALL
SELECT '铅笔4',5,1,2 UNION ALL
SELECT '铅笔5',0,0,0 select
名称,价格=(case when 价格一=0 then case when 价格二 =0 then 价格三 else 价格二 end else 价格一 end) from TMAA
结果:
铅笔 1
铅笔2 3
铅笔3 1
铅笔4 5
铅笔5 0
insert into #1 select '铅笔',0,1.2,1.5
insert into #1 select '铅笔1',1,1.8,1.3
insert into #1 select '铅笔2',1.2,2.6,2.1insert into #1 select '铅笔3',0,0,2.2
insert into #1 select '铅笔4',1,0,0
insert into #1 select '铅笔5',1,2,2.2
insert into #1 select '铅笔1',0,0,0select name ,isnull(coalesce(j1,j2,j3),0) as '价格' from (
select name,
case when j1=0 then null else j1 end as j1 ,
case when j2=0 then null else j2 end as j2 ,
case when j3=0 then null else j3 end as j3
from #1 )t铅笔 1.2
铅笔1 1.0
铅笔2 1.2
铅笔3 2.2
铅笔4 1.0
铅笔5 1.0
铅笔1 0.0
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([名称] nvarchar(5),[价格一] decimal(18,2),[价格二] decimal(18,2),[价格三] decimal(18,2))
Insert #T
select N'铅笔',0,1.2,1.5 union all
select N'铅笔1',1,1.8,1.3 union all
select N'铅笔2',1.2,2.6,2.1 union all
select N'铅笔3',0,0,2.2 union all
select N'铅笔4',1,0,0 union all
select N'铅笔5',1,2,2.2 union all
select N'铅笔1',0,0,0
Go
Select
[名称],
[价格]=isnull(nullif(isnull(nullif([价格一],0),[价格二]),0),[价格三])
from
#T(7 行受影响)
名称 价格
----- ---------------------------------------
铅笔 1.20
铅笔1 1.00
铅笔2 1.20
铅笔3 2.20
铅笔4 1.00
铅笔5 1.00
铅笔1 0.00(7 行受影响)
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([名称] nvarchar(5),[价格一] decimal(18,2),[价格二] decimal(18,2),[价格三] decimal(18,2))
Insert #T
select N'铅笔',0,1.2,1.5 union all
select N'铅笔1',1,1.8,1.3 union all
select N'铅笔2',1.2,2.6,2.1 union all
select N'铅笔3',0,0,2.2 union all
select N'铅笔4',1,0,0 union all
select N'铅笔5',1,2,2.2 union all
select N'铅笔1',0,0,0
Go
select
名称,
价格=case when 价格一=0 then case when 价格二 =0 then 价格三 else 价格二 end else 价格一 end
from
#T名称 价格
----- ---------------------------------------
铅笔 1.20
铅笔1 1.00
铅笔2 1.20
铅笔3 2.20
铅笔4 1.00
铅笔5 1.00
铅笔1 0.00(7 行受影响)
insert into #1 select '铅笔',0,1.2,1.5
insert into #1 select '铅笔1',1,1.8,1.3
insert into #1 select '铅笔2',1.2,2.6,2.1insert into #1 select '铅笔3',0,0,2.2
insert into #1 select '铅笔4',1,0,0
insert into #1 select '铅笔5',1,2,2.2
insert into #1 select '铅笔1',0,0,0select name ,
isnull(coalesce(case when j1=0 then null else j1 end,
case when j2=0 then null else j2 end,
case when j3=0 then null else j3 end),0) as '价格'
from #1 /*name 价格
-------------------------------------------------- ------------
铅笔 1.2
铅笔1 1.0
铅笔2 1.2
铅笔3 2.2
铅笔4 1.0
铅笔5 1.0
铅笔1 .0(所影响的行数为 7 行)
*/drop table #1
如果 有十列如: 价格一, 价格二, 价格三, 价格四,价格五,价格六,价格七,价格八还是按这样的取价顺序得到/*name 价格
-------------------------------------------------- ------------
铅笔 1.2
铅笔1 1.0
铅笔2 1.2
铅笔3 2.2
铅笔4 1.0
铅笔5 1.0
铅笔1 .0
这样的结果,是不是要写十个字字段呢?