有一表tb 有如下几个字段type key value
default a aa
default b bb
style1 a xxaa现在想select出来的表是这样key value
a xxaa
b bb输入参数是type(比如输入style1)
select每一项的时候比如key是a的时候先搜索一下type是style1且key是a的有没有值,有则取 没有则取type是default且key是a的值
default a aa
default b bb
style1 a xxaa现在想select出来的表是这样key value
a xxaa
b bb输入参数是type(比如输入style1)
select每一项的时候比如key是a的时候先搜索一下type是style1且key是a的有没有值,有则取 没有则取type是default且key是a的值
union all
select * from tb where not exists(select 1 from tb where type=@tpye)
and type='default'
select tb1.key,isnull(tb2.value,tb1.value)
from tb tb1 left join tb tb2 on tb1.key = tb2.key and tb2.[type] = 'style1'
where tb1.[type] = 'default'
SELECT *
FROM tb
WHERE TYPE = @type
UNION ALL
SELECT *
FROM tb t
WHERE NOT EXISTS(
SELECT 1
FROM tb
WHERE [key] = t.[key]
AND TYPE = @type
)
AND TYPE = 'default'改一下
from tb a left join tb b
on a.key=b.key
where a.type='default'
and b.type='style1'
(
[type] varchar(10),
[key] varchar(10),
[value] varchar(20)
)
insert test
select
'default', 'a' ,'aa' union select
'default', 'b', 'bb'union select
'style1' ,'a', 'xxaa'
go
declare @type varchar(10)
set @type='style1'
;with cte as
(select [key],[value] from test where type=@type)
select * from cte
union all
select [key],[value]
from test
where not exists(select * from cte where test.[key]=[key])