34,575
社区成员
发帖
与我相关
我的任务
分享
---测试数据---
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([ITEMNO] int,[ITEMCODE] varchar(1),[ITEMNAME] varchar(4),[MEMO] varchar(4))
insert [tb1]
select 1,'D','DDDD','DDDD' union all
select 2,'C','CCCC','CCCC' union all
select 3,'B','BBBB','BBBB' union all
select 4,'E','EEEE','EEEE'
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([ITEMCODE] varchar(1),[EX] varchar(2))
insert [tb2]
select 'E','E1' union all
select 'D','D1' union all
select 'C','C1' union all
select 'C','C2' union all
select 'E','E1'
---查询---
select ITEMNO,ITEMCODE,EX,ITEMNAME,MEMO
from
(
SELECT
itemno as px,
CASE WHEN EX='' THEN LTRIM(ITEMNO) ELSE '' END AS ITEMNO,
CASE WHEN EX='' THEN LTRIM(ITEMCODE) ELSE '' END AS ITEMCODE,
EX,
ITEMNAME,
MEMO
FROM
(
select ITEMNO,ITEMCODE,'' as EX,ITEMNAME,MEMO from tb1
union all
select tb1.itemno,tb2.itemcode,tb2.ex,'','' from tb2 left join tb1 on tb2.itemcode=tb1.itemcode
) t) t
order by px,ex
---结果---
ITEMNO ITEMCODE EX ITEMNAME MEMO
------------ -------- ---- -------- ----
1 D DDDD DDDD
D1
2 C CCCC CCCC
C1
C2
3 B BBBB BBBB
4 E EEEE EEEE
E1
E1
(所影响的行数为 9 行)
select
isnull(a.ITEMNO,'') ITEMNO,isnull(a.ITEMCODE,'') ITEMCODE,isnull(b.EX,'') EX,isnull(a.ITEMNAME,'') ITEMNAME,isnull(a.MEMO,'') MEMO
from tb1 a
full join (select x.ITEMNO,ITEMCODE,y.EX from tb1 x join tb2 y on x.ITEMCODE=y.ITEMCODE) b
on a.ITEMCODE=b.ITEMCODE
---测试数据---
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([ITEMNO] int,[ITEMCODE] varchar(1),[ITEMNAME] varchar(4),[MEMO] varchar(4))
insert [tb1]
select 1,'D','DDDD','DDDD' union all
select 2,'C','CCCC','CCCC' union all
select 3,'B','BBBB','BBBB' union all
select 4,'E','EEEE','EEEE'
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([ITEMCODE] varchar(1),[EX] varchar(2))
insert [tb2]
select 'E','E1' union all
select 'D','D1' union all
select 'C','C1' union all
select 'C','C2' union all
select 'E','E1'
---查询---
select
a.ITEMNO,a.ITEMCODE,isnull(b.EX,'') AS EX,a.ITEMNAME,a.MEMO
from tb1 a
left join tb2 b on a.ITEMCODE=b.ITEMCODE
---结果---
ITEMNO ITEMCODE ITEMNAME MEMO
----------- -------- ---- -------- ----
1 D D1 DDDD DDDD
2 C C1 CCCC CCCC
2 C C2 CCCC CCCC
3 B BBBB BBBB
4 E E1 EEEE EEEE
4 E E1 EEEE EEEE
(所影响的行数为 6 行)
select ITEMNO,ITEMCODE,isnull(EX,'') EX,ITEMNAME,MEMO
from tb1 left join tb2 on tb1.ITEMCODE = b.ITEMCODE
order by a.ITEMNO
select
a.ITEMNO,a.ITEMCODE,isnull(b.EX,''),a.ITEMNAME,a.MEMO
from tb1 a
left join tb2 b on a.ITEMNO=b.ITEMNO