我有两张表 zzexam ,jhexam
zzexam如下
username useraccount avg_jcr
张参 0001 2
李好 0002 3username useraccount avg_jcr
张参 0001 4
王正 0003 5查询结果useraccount useraccount zzexam.avg_jcr jhexam.avg_jcr张参 0001 2 4王正 0003 null 5李好 0002 3 null哪位大哥、大姐帮助解决一下
zzexam如下
username useraccount avg_jcr
张参 0001 2
李好 0002 3username useraccount avg_jcr
张参 0001 4
王正 0003 5查询结果useraccount useraccount zzexam.avg_jcr jhexam.avg_jcr张参 0001 2 4王正 0003 null 5李好 0002 3 null哪位大哥、大姐帮助解决一下
b.avg_jcr as jhexam.avg_jcr from zzexam a,jhexam b
where a.username=b.username
declare @B table (id int,[name] varchar(30),glass varchar(2))insert into @A(id,name,sex)
select 1,'ww','M' union all
select 2,'aa','F'insert into @B(id,name,glass)
select 1,'ww','A' union all
select 3,'bb','B'--select t1.* ,t2.glass from @A t1 ,@B t2 where t1.id=t2.id
select isnull(t1.id,t2.id) id,isnull(t1.name,t2.name) name,t1.sex,t2.glass from @A t1 full join @B t2 on t1.id=t2.id
类似.楼主换换就行了.
ISNULL(A.USERNAME,B.USERNAME) AS USERNAME,
ISNULL(A.USERACCOUNT,B.USERACCOUNT) AS USERACCOUNT,
A.avg_jcr AS zzexam.avg_jcr,
B.avg_jcr AS jhexam.avg_jcr
FROM
zzexam A
FULL JOIN
jhexam B
ON
A.USERNAME=B.USERNAME AND A.USERACCOUNT=B.USERACCOUNT
select a.username,a.useraccount,a.avg_jcr as zzexam.avg_jcr, b.avg_jcr as jhexam.avg_jcr from zzexam a
FULL JOIN jhexam b ON a.username=b.username
from zzexam A full join jhexam B
on A.useraccount=N.useraccount
if object_id('zzexam') is not null
drop table zzexam
create table zzexam
(
username varchar(10),
useraccount varchar(5),
avg_jcr int
)if object_id('jhexam') is not null
drop table jhexam
create table jhexam
(
username varchar(10),
useraccount varchar(5),
avg_jcr int
)insert zzexam
select '张参','0001',2 union all
select '李好','0002',3insert jhexam
select '张参','0001',4 union all
select '王正','0003',5select * from zzexam
select * from jhexamselect isnull(z.username,j.username) as username,
isnull(z.useraccount,j.useraccount) as useraccount,z.avg_jcr,j.avg_jcr
from zzexam z full join
jhexam j on z.useraccount =j.useraccount /***
username useraccount avg_jcr avg_jcr
---------- ----------- ----------- -----------
张参 0001 2 4
王正 0003 NULL 5
李好 0002 3 NULL(所影响的行数为 3 行)
**/
select isnull(z.username,j.username) as username,
isnull(z.useraccount,j.useraccount) as useraccount,
z.avg_jcr,j.avg_jcr
from zzexam z
full join jhexam j
on z.useraccount =j.useraccount
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-27 13:39:07
----------------------------------------------------------------
--> 测试数据:[zzexam]
if object_id('[zzexam]') is not null drop table [zzexam]
create table [zzexam]([username] varchar(4),[useraccount] varchar(4),[avg_jcr] int)
insert [zzexam]
select '张参','0001',2 union all
select '李好','0002',3
--> 测试数据:[jhexam]
if object_id('[jhexam]') is not null drop table [jhexam]
create table [jhexam]([username] varchar(4),[useraccount] varchar(4),[avg_jcr] int)
insert [jhexam]
select '张参','0001',4 union all
select '王正','0003',5
--------------开始查询--------------------------
select
isnull(a.useraccount,b.username) as username,
isnull(a.useraccount,b.useraccount) as useraccount,
a.avg_jcr as [zzexam.avg_jcr],
b.avg_jcr as [jhexam.avg_jcr]
from
zzexam a
full join
jhexam b
on
a.username=b.username and a.USERACCOUNT=B.USERACCOUNT
----------------结果----------------------------
/*username useraccount zzexam.avg_jcr jhexam.avg_jcr
-------- ----------- -------------- --------------
0001 0001 2 4
0002 0002 3 NULL
王正 0003 NULL 5
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-27 13:39:07
----------------------------------------------------------------
--> 测试数据:[zzexam]
if object_id('[zzexam]') is not null drop table [zzexam]
create table [zzexam]([username] varchar(4),[useraccount] varchar(4),[avg_jcr] int)
insert [zzexam]
select '张参','0001',2 union all
select '李好','0002',3
--> 测试数据:[jhexam]
if object_id('[jhexam]') is not null drop table [jhexam]
create table [jhexam]([username] varchar(4),[useraccount] varchar(4),[avg_jcr] int)
insert [jhexam]
select '张参','0001',4 union all
select '王正','0003',5
--------------开始查询--------------------------
select
isnull(a.username,b.username) as username,
isnull(a.useraccount,b.useraccount) as useraccount,
a.avg_jcr as [zzexam.avg_jcr],
b.avg_jcr as [jhexam.avg_jcr]
from
zzexam a
full join
jhexam b
on
a.username=b.username and a.USERACCOUNT=B.USERACCOUNT
----------------结果----------------------------
/*username useraccount zzexam.avg_jcr jhexam.avg_jcr
-------- ----------- -------------- --------------
张参 0001 2 4
李好 0002 3 NULL
王正 0003 NULL 5
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-27 13:39:07
----------------------------------------------------------------
--> 测试数据:[zzexam]
if object_id('[zzexam]') is not null drop table [zzexam]
create table [zzexam]([username] varchar(4),[useraccount] varchar(4),[avg_jcr] int)
insert [zzexam]
select '张参','0001',2 union all
select '李好','0002',3
--> 测试数据:[jhexam]
if object_id('[jhexam]') is not null drop table [jhexam]
create table [jhexam]([username] varchar(4),[useraccount] varchar(4),[avg_jcr] int)
insert [jhexam]
select '张参','0001',4 union all
select '王正','0003',5
--------------开始查询--------------------------
select
isnull(a.username,b.username) as username,
isnull(a.useraccount,b.useraccount) as useraccount,
a.avg_jcr as [zzexam.avg_jcr],
b.avg_jcr as [jhexam.avg_jcr]
from
zzexam a
full join
jhexam b
on
a.username=b.username and a.USERACCOUNT=B.USERACCOUNT
order by username desc
----------------结果----------------------------
/*username useraccount zzexam.avg_jcr jhexam.avg_jcr
-------- ----------- -------------- --------------
张参 0001 2 4
王正 0003 NULL 5
李好 0002 3 NULL
*/
看来 isnull()
方法起到了很大的作用。