表1为lmdjb
XL lmyear LMMONTH LMDAY BH ISBN DWMC YSXM LB
A 2009 8 20 1 1 CC 4567 A1
A 2009 8 20 1 1 CC 4567 A2
B 2009 8 20 3 4 C5 4567 A1
B 2009 8 20 1 3 RR 4567 A1
C 2009 8 20 5 1 CC 4567 A1
C 2009 8 20 7 5 EE 4567 A2
A 2009 8 20 1 1 CC 4567 A2表2为khcpdjb
KHMC XL LB lxdh
CC A A1 123
CC A A2
C5 B A1 258
RR B A1 1587
CC C A2
EE C A2 222
查询结果
XL lmyear LMMONTH LMDAY BH ISBN DWMC YSXM LB lxdh
A 2009 8 20 1 1 CC 4567 A1 123
A 2009 8 20 1 1 CC 4567 A2
B 2009 8 20 3 4 C5 4567 A1 258
B 2009 8 20 1 3 RR 4567 A1 1587
C 2009 8 20 5 1 CC 4567 A1
C 2009 8 20 7 5 EE 4567 A2 222
A 2009 8 20 1 1 CC 4567 A2 123表1有多少条记录查询结果就有多少条记录。只是把表2中的lxdh加在表1后面得到结果。表1中的dwmc,xl.lb与表2中的khmc,xl,lb是对应的。
XL lmyear LMMONTH LMDAY BH ISBN DWMC YSXM LB
A 2009 8 20 1 1 CC 4567 A1
A 2009 8 20 1 1 CC 4567 A2
B 2009 8 20 3 4 C5 4567 A1
B 2009 8 20 1 3 RR 4567 A1
C 2009 8 20 5 1 CC 4567 A1
C 2009 8 20 7 5 EE 4567 A2
A 2009 8 20 1 1 CC 4567 A2表2为khcpdjb
KHMC XL LB lxdh
CC A A1 123
CC A A2
C5 B A1 258
RR B A1 1587
CC C A2
EE C A2 222
查询结果
XL lmyear LMMONTH LMDAY BH ISBN DWMC YSXM LB lxdh
A 2009 8 20 1 1 CC 4567 A1 123
A 2009 8 20 1 1 CC 4567 A2
B 2009 8 20 3 4 C5 4567 A1 258
B 2009 8 20 1 3 RR 4567 A1 1587
C 2009 8 20 5 1 CC 4567 A1
C 2009 8 20 7 5 EE 4567 A2 222
A 2009 8 20 1 1 CC 4567 A2 123表1有多少条记录查询结果就有多少条记录。只是把表2中的lxdh加在表1后面得到结果。表1中的dwmc,xl.lb与表2中的khmc,xl,lb是对应的。
a.*,
isnull(b.lxdh,'') as lxdh
from
lmdjb a
left join
khcpdjb b
on
a.dwmc=b.dwmc and a.xl=b.xl and a.lb=b.lb
a.*,b.LB,isnull(b.lxdh,'')as lxdh
from
lmdjb a, khcpdjb b
where
a.XL=b.xl
---测试数据---
if object_id('[lmdjb]') is not null drop table [lmdjb]
go
create table [lmdjb]([XL] varchar(1),[lmyear] int,[LMMONTH] int,[LMDAY] int,[BH] int,[ISBN] int,[DWMC] varchar(2),[YSXM] int,[LB] varchar(2))
insert [lmdjb]
select 'A',2009,8,20,1,1,'CC',4567,'A1' union all
select 'A',2009,8,20,1,1,'CC',4567,'A2' union all
select 'B',2009,8,20,3,4,'C5',4567,'A1' union all
select 'B',2009,8,20,1,3,'RR',4567,'A1' union all
select 'C',2009,8,20,5,1,'CC',4567,'A1' union all
select 'C',2009,8,20,7,5,'EE',4567,'A2' union all
select 'A',2009,8,20,1,1,'CC',4567,'A2'
if object_id('[khcpdjb]') is not null drop table [khcpdjb]
go
create table [khcpdjb]([KHMC] varchar(2),[XL] varchar(1),[LB] varchar(2),[lxdh] int)
insert [khcpdjb]
select 'CC','A','A1',123 union all
select 'CC','A','A2',null union all
select 'C5','B','A1',258 union all
select 'RR','B','A1',1587 union all
select 'CC','C','A2',null union all
select 'EE','C','A2',222
---查询---
select
a.*,
isnull(ltrim(b.lxdh),'') as lxdh
from
lmdjb a
left join
khcpdjb b
on
a.dwmc=b.khmc and a.xl=b.xl and a.lb=b.lb---结果---
XL lmyear LMMONTH LMDAY BH ISBN DWMC YSXM LB lxdh
---- ----------- ----------- ----------- ----------- ----------- ---- ----------- ---- ------------
A 2009 8 20 1 1 CC 4567 A1 123
A 2009 8 20 1 1 CC 4567 A2
B 2009 8 20 3 4 C5 4567 A1 258
B 2009 8 20 1 3 RR 4567 A1 1587
C 2009 8 20 5 1 CC 4567 A1
C 2009 8 20 7 5 EE 4567 A2 222
A 2009 8 20 1 1 CC 4567 A2 (所影响的行数为 7 行)
a.*,b.LB,isnull(b.lxdh,'')as lxdh
from
lmdjb a, khcpdjb b
where
a.XL=b.xl
and
a.lb=b.lb
and
a.dwmc=b.dwmc
---测试数据---
if object_id('[lmdjb]') is not null drop table [lmdjb]
go
create table [lmdjb]([XL] varchar(1),[lmyear] int,[LMMONTH] int,[LMDAY] int,[BH] int,[ISBN] int,[DWMC] varchar(2),[YSXM] int,[LB] varchar(2))
insert [lmdjb]
select 'A',2009,8,20,1,1,'CC',4567,'A1' union all
select 'A',2009,8,20,1,1,'CC',4567,'A2' union all
select 'B',2009,8,20,3,4,'C5',4567,'A1' union all
select 'B',2009,8,20,1,3,'RR',4567,'A1' union all
select 'C',2009,8,20,5,1,'CC',4567,'A1' union all
select 'C',2009,8,20,7,5,'EE',4567,'A2' union all
select 'A',2009,8,20,1,1,'CC',4567,'A2'
if object_id('[khcpdjb]') is not null drop table [khcpdjb]
go
create table [khcpdjb]([KHMC] varchar(2),[XL] varchar(1),[LB] varchar(2),[lxdh] int)
insert [khcpdjb]
select 'CC','A','A1',123 union all
select 'CC','A','A2',null union all
select 'C5','B','A1',258 union all
select 'RR','B','A1',1587 union all
select 'CC','C','A2',null union all
select 'EE','C','A2',222select
a.*,b.LB,isnull(b.lxdh,'')as lxdh
from
lmdjb a, khcpdjb b
where
a.XL=b.xl
and
a.lb=b.lb
and
a.dwmc=b.khmc
/*
XL lmyear LMMONTH LMDAY BH ISBN DWMC YSXM LB LB lxdh
---- ----------- ----------- ----------- ----------- ----------- ---- ----------- ---- ---- -----------
A 2009 8 20 1 1 CC 4567 A1 A1 123
A 2009 8 20 1 1 CC 4567 A2 A2 0
B 2009 8 20 3 4 C5 4567 A1 A1 258
B 2009 8 20 1 3 RR 4567 A1 A1 1587
C 2009 8 20 7 5 EE 4567 A2 A2 222
A 2009 8 20 1 1 CC 4567 A2 A2 0(所影响的行数为 6 行)
*/
if object_id('[ta]') is not null drop table [ta]
create table [ta]([XL] varchar(1),[lmyear] int,[LMMONTH] int,[LMDAY] int,[BH] int,[ISBN] int,[DWMC] varchar(2),[YSXM] int,[LB] varchar(2))
insert [ta]
select 'A',2009,8,20,1,1,'CC',4567,'A1' union all
select 'A',2009,8,20,1,1,'CC',4567,'A2' union all
select 'B',2009,8,20,3,4,'C5',4567,'A1' union all
select 'B',2009,8,20,1,3,'RR',4567,'A1' union all
select 'C',2009,8,20,5,1,'CC',4567,'A1' union all
select 'C',2009,8,20,7,5,'EE',4567,'A2' union all
select 'A',2009,8,20,1,1,'CC',4567,'A2'select * from [ta]--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([KHMC] varchar(2),[XL] varchar(1),[LB] varchar(2),[lxdh] int)
insert [tb]
select 'CC','A','A1',123 union all
select 'CC','A','A2',null union all
select 'C5','B','A1',258 union all
select 'RR','B','A1',1587 union all
select 'CC','C','A2',null union all
select 'EE','C','A2',222select * from [tb]
select a.*,b.lxdh from ta a
left join tb b
on a.[DWMC]=b.[KHMC] and a.[XL]=b.[XL] and a.[LB]=b.[LB]
/*
XL lmyear LMMONTH LMDAY BH ISBN DWMC YSXM LB lxdh
---- ----------- ----------- ----------- ----------- ----------- ---- ----------- ---- -----------
A 2009 8 20 1 1 CC 4567 A1 123
A 2009 8 20 1 1 CC 4567 A2 NULL
B 2009 8 20 3 4 C5 4567 A1 258
B 2009 8 20 1 3 RR 4567 A1 1587
C 2009 8 20 5 1 CC 4567 A1 NULL
C 2009 8 20 7 5 EE 4567 A2 222
A 2009 8 20 1 1 CC 4567 A2 NULL(7 行受影响)
*/
11楼,我还是用抢分存储呀
要替换null就用树哥那个
A 2009 8 20 1 1 CC 4567 A2
a.*,
isnull(ltrim(b.lxdh),'') as lxdh
from
lmdjb a
left join
(select khmc,xl,lb,sum(lxdh) lxdh from khcpdjb group by khmc,xl,lb) b
on
a.dwmc=b.khmc and a.xl=b.xl and a.lb=b.lb
聚合一下就好了。