数据库中连个表:
A表
name num1
----------------
TiK 7
Bob 8
Ann 7
B表
name num2
----------------
TiK 8
Juen 7
Ann 7怎么写检索语句,才能得到以下想要的结果?name num1 num2
----------------
TiK 7 8
Ann 7 7
Bob 8
Juen 7我使用union,得到的不是想要的结果。
A表
name num1
----------------
TiK 7
Bob 8
Ann 7
B表
name num2
----------------
TiK 8
Juen 7
Ann 7怎么写检索语句,才能得到以下想要的结果?name num1 num2
----------------
TiK 7 8
Ann 7 7
Bob 8
Juen 7我使用union,得到的不是想要的结果。
A表
name num
----------------
TiK 7
Bob 8
Ann 7
B表
name num
----------------
TiK 8
Juen 7
Ann 7 想得到的表是这个样子
name num1(对应的是A的num) num2(对应的是B的num)
---------------- ---------------------------------
TiK 7 8
Ann 7 7
Bob 8
Juen 7
SELECT NAME,NUM1,0 NUM2 FROM TA
UNION ALL
SELECT NAME,0 NUM1,NUM2 FROM TB
)T
GROUP BY NAME
from A表full join B表
on A表.姓名=B表.姓名 /*全外连接实现*/
SELECT ISNULL(A.NAME,B.NAME)NAME,ISNULL(A.NUM1,'')NUM1,ISNULL(B.NUM2,'')NUM2
FROM A RIGHT JOIN B
ON A.NAME=B.NAME
FROM A full JOIN B
ON A.NAMEs=B.NAMEs
是全连接
CREATE TABLE [dbo].[a](
[names] [varchar](150) COLLATE Chinese_PRC_CI_AS NULL,
[num] [int] NULL
) ON [PRIMARY]insert into a(names,num )values(XXX)/*插入数据*/
.........CREATE TABLE [dbo].[b](
[names] [varchar](150) COLLATE Chinese_PRC_CI_AS NULL,
[num] [int] NULL
) ON [PRIMARY]
insert into b(names,num )values(XXX)/*插入数据*/
...........-------调试------
SELECT ISNULL(A.NAMEs,B.NAMEs)NAMEs,ISNULL(A.NUM,'')NUM1,ISNULL(B.NUM,'')NUM2
FROM A full JOIN B
ON A.NAMEs=B.NAMEs
SELECT NAME,NUM1,0 NUM2 FROM TA
UNION ALL
SELECT NAME,0 NUM1,NUM2 FROM TB
)T
GROUP BY NAME
FROM A left JOIN B
ON A.NAME=B.NAME
union
SELECT B.NAME,A.NUM,B.NUM
FROM B left JOIN A
ON A.NAME=B.NAME
呵呵谢谢指正!!!
SELECT ISNULL(A.NAME,B.NAME)NAME,ISNULL(A.NUM1,'')NUM1,ISNULL(B.NUM2,'')NUM2
FROM A FULL JOIN B
ON A.NAME=B.NAME
CREATE TABLE A(NAME CHAR(5),NUM1 INT)
INSERT A
SELECT 'TiK', 7 UNION
SELECT 'Bob', 8 UNION
SELECT 'Ann', 7 CREATE TABLE B(NAME CHAR(5),NUM2 INT)
INSERT B
SELECT 'TiK', 8 UNION
SELECT 'Juen', 7 UNION
SELECT 'Ann', 7 --SELECT * FROM A
--SELECT * FROM BSELECT ISNULL(A.NAME,B.NAME)NAME,ISNULL(A.NUM1,'')NUM1,ISNULL(B.NUM2,'')NUM2
FROM A FULL JOIN B
ON A.NAME=B.NAME NAME NUM1 NUM2
----- ----------- -----------
Ann 7 7
Juen 0 7
TiK 7 8
Bob 8 0(所影响的行数为 4 行)
--SQL code---测试数据---
if object_id('tableA') is not null drop table tableA
if object_id('tableB') is not null drop table tableB
go
create table tableA(name varchar(10),num1 int)
insert into tableA
select 'TiK',7
union all select 'Bob',8
union all select 'Ann',7
create table tableB(name varchar(10),num2 int)
insert into tableB
select 'TiK',7
union all select 'Juen',7
union all select 'Ann',7select isnull(a.name,b.name) 'Sname', a.num1,
b.num2 from
tableA a full join tableB b on a.name = b.name
---结果TiK 7 7
Bob 8 NULL
Ann 7 7
Juen NULL 7