select a.[id], (case when a.part_id is null then a.service_id else a.part_id end) as [part/service] ,
c.description ,isnull(b.vat_percent,0) from 表1 a left join 表2 b on a.vat_id=b.vat_id
left join 表3 c on a.service_id=a.service_id
c.description ,isnull(b.vat_percent,0) from 表1 a left join 表2 b on a.vat_id=b.vat_id
left join 表3 c on a.service_id=a.service_id
解决方案 »
- SQL查询问题,按要求GROUP BY以后,列出表
- 管理数据库的问题
- select CONVERT ( nvarchar(9),9,00)
- 存储过程执行时出现错误,应该如何修正呢,谢谢!
- 分页存储过程
- 怎么优化复杂的SQL查询语句
- 怎样在查询记录集Recordset中怎么再进行SQL查询!
- 热爱VC++ C# PB VB Java JSP 来交流一下好吗?希望你是我的改变者,真诚的感谢你。
- 如何从一个sqlserver表里,取最后的5条记录呢?
- Win8.1装了Sql2000企业管理器里管理索引弹错,丢失PFUTIL80.dll
- 帮我写个SQL代码,得出相映结果!有两张表,结构如下,谢谢!!!
- 怎么求一表中某列具有相同数据行?
case when isnull(a.a.part_id,0) = 0 then c.description else b.description end as des,
isnull(d.vat_percent,0) as vat
from 表1 a
left join 表3 b on a.part_id = b.part_id
left join 表4 c on a.service_id= c.service_id
left join 表2 d on a.vat_id = d.vat_id
a.id,
[part/service] = isnull(a.part_id,'')+isnull(a.service_id,''),
[description] = case
when a.part_id is not null then
(select description from 表3 where part_id=a.part_id)
else
(select description from 表4 where service_id=a.service_id)
end,
[vat_percent] = b.vat_percent
from
表1 a,
表2 b
where
a.vat_id = b.vat_id
order by
a.id
(id int,part_id varchar(20),service_id varchar(20),vat_id varchar(20))insert @t values (1,'A',null,'V17')
insert @t values (2,null,'X','V13')
insert @t values (3,'B',null,null)
insert @t values (4,null,'C',null)select id,[part/service]=isnull(part_id,service_id),[description]=case when
service_id is null then part_id+' part' else service_id+' service' end,
vat_id=isnull(stuff(vat_id,1,patindex('%[^0-9]%',vat_id),'')+'%',0)
from @tid part/service description vat_id
----------- -------------------- ---------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A A part 17%
2 X X service 13%
3 B B part 0
4 C C service 0(所影响的行数为 4 行)
insert into @t1
select 1, 'A', Null, 'V17'union all
select 2, Null, 'X', 'V13' union all
select 3, 'B', Null, Null union all
select 4, Null, 'C', Null
--select * from @t1declare @t2 table(vat_id char(3),vat_percent decimal(28,2))
insert into @t2
select 'V17',0.17 union all
select 'V13',0.13declare @t3 table(part_id varchar(20),description varchar(50))
insert into @t3
select 'A','A part'union all
select 'B','B part'
declare @t4 table(service_id varchar(20),description varchar(50))
insert into @t4
select 'X','X service'union all
select 'C','C service'select a.id,
'part/service'=case when a.part_id is null then a.service_id else a.part_id end,
'description'=case when a.part_id is null then d.description else c.description end,
'vat_percent'=case when a.vat_id is null then 0 else b.vat_percent end
from @t1 a left join @t2 b on a.vat_id=b.vat_id
left join @t3 c on a.part_id=c.part_id
left join @t4 d on a.service_id=d.service_id--测试结果
/*
id part/service description vat_percent
----------- -------------------- -------------------------------------------------- ------------------------------
1 A A part .17
2 X X service .13
3 B B part .00
4 C C service .00(所影响的行数为 4 行)
*/