我有这样的两个表:
Table A:
name Dept age
----------------
mary IT 23
henry fs 25
joyce pr 28Table B:
name Dept Sex
----------------
mary HR F
henry fs m
joyce ps F
我现在想要把这两个表比较,
得出不同记录,并放在一起比较,
并能多出一个字段Re做判断是否相同。
得出结果如下:name Dept_A Dept_B age sex Re
-------------------------------------
mary IT HR 23 f N
henry fs fs 25 m Y
joyce pr ps 28 f NRe就表示比较是否Dept一样,如果一样就标记Y,如果不一样就N.
可以么?请教各位了。
Table A:
name Dept age
----------------
mary IT 23
henry fs 25
joyce pr 28Table B:
name Dept Sex
----------------
mary HR F
henry fs m
joyce ps F
我现在想要把这两个表比较,
得出不同记录,并放在一起比较,
并能多出一个字段Re做判断是否相同。
得出结果如下:name Dept_A Dept_B age sex Re
-------------------------------------
mary IT HR 23 f N
henry fs fs 25 m Y
joyce pr ps 28 f NRe就表示比较是否Dept一样,如果一样就标记Y,如果不一样就N.
可以么?请教各位了。
解决方案 »
- 参数类型不正确,或不在可以接受的范围之内,或与其他参数冲突。(我的存储过程有错误了)
- 这样的查询还可以优化吗?如果不行,如何用存储过程来写以提高执行效率?
- 【求大神帮助】为什么我装SQL server一直装不上?要交作业了!!!!!!
- 这个update如何写?
- sqlserver大记录集操作
- 【死水问题】无法连接远程数据库:sqlserver不存在或访问被拒绝 急!
- sqlserver如何方便的在查询中生成顺序号
- 请教各位高手一个问题,如何把vfp数据库文件转换为excel文件?
- 为什莫用sql server2000复制数据库向导复制数据库失败,走过路过不要错过,高手进来拿分!!
- vc中调用ms sql server的本身存储过程时怎么取得返回值??
- 不在同一个网段上,连接sql server2000出错。
- sql server2005全文检索效能很差?
case when a.dept=b.dept then Y' else 'N' end as Re
from a join b on a.name=b.name
select a.name, a.dept as Dept_A ,b.dept as Dept_B, age ,sex ,
case when a.dept=b.dept then 'Y' else 'N' end as Re
from a join b on a.name=b.name
from tableA a inner join tableB b on a.name=b.name
select name, a.dept as Dept_A ,b.dept as Dept_B, age ,sex ,
case when a.dept=b.dept then 'Y' else 'N' end as Re
from a join b on a.name=b.name
name,
Dept_A=a.dept,
Dept_B=b.dept,
age,
sex,
Re =case when a.dept=b.dept then 'Y' else 'N' end
from a
join b
on a.name=b.name
insert into @A select 'mary ','IT',23
insert into @A select 'henry','fs',25
insert into @A select 'joyce','pr',28 declare @B table(name varchar(10),Dept varchar(10),sex char(1))
insert into @B select 'mary ','HR','F'
insert into @B select 'henry','fs','m'
insert into @B select 'joyce','ps','F'
select
A.name,A.Dept as Dept_A,B.Dept as Dept_B,A.age,B.Sex,
(case when A.Dept=B.Dept then 'Y' else 'N' end) as Re
from
@A A,@B B
where
A.name=B.name/*
name Dept_A Dept_B age Sex Re
---------- ---------- ---------- ----------- ---- ------
mary IT HR 23 F N
henry fs fs 25 m Y
joyce pr ps 28 F N
*/
case when a.dept=b.dept then Y' else 'N' end as Re
from tb1 a join tb2 b on a.name=b.name
drop table a
go
create table a(name varchar(10), Dept varchar(10), age int)
----------------
insert a select 'mary','IT',23
insert a select 'henry','fs',25
insert a select 'joyce','pr',28
goif object_id('b') is not null
drop table b
go
create table b(name varchar(10), Dept varchar(10), Sex varchar(10))
insert b select 'mary','HR','F'
insert b select 'henry','fs','m'
insert b select 'joyce','ps','F'
goselect
a.name,
Dept_A=a.dept,
Dept_B=b.dept,
age,
sex,
Re =case when a.dept=b.dept then 'Y' else 'N' end
from a
join b
on a.name=b.name/*
name Dept_A Dept_B age sex Re
---------- ---------- ---------- ----------- ---------- ------
mary IT HR 23 F N
henry fs fs 25 m Y
joyce pr ps 28 F N(3 行受影响)
*/
select name, a.dept as Dept_A ,b.dept as Dept_B, age ,sex ,
case when a.dept=b.dept then 'Y' else 'N' end as Re
from a join b on a.name=b.name很简单的问题!
insert #A select 'mary','IT',23
insert #A select 'henry','fs',25
insert #A select 'joyce','pr',28create table #B(name varchar(10), Dept varchar(10), Sex varchar(10))
insert #B select 'mary','HR','F'
insert #B select 'henry','fs','m'
insert #B select 'joyce','ps','F'select a.name,Dept_A=a.dept,Dept_B=b.dept,age,sex,Re =case when a.dept=b.dept then 'Y' else 'N' end
from #A a join #B b on a.name=b.namename Dept_A Dept_B age sex Re
---------- ---------- ---------- ----------- ---------- ------
mary IT HR 23 F N
henry fs fs 25 m Y
joyce pr ps 28 F N(3 行受影响)
哈哈~ 每天上CSDN ~ 每天回帖即可获得 10 分可用分
case when a.dept=b.dept then 'Y' else 'N' end as Re
from a join b on a.name=b.name
select name, a.dept as Dept_A ,b.dept as Dept_B, age ,sex ,
case when a.dept=b.dept then 'Y' else 'N' end as Re
from a join b on a.name=b.name
select a.name, a.dept as Dept_A ,b.dept as Dept_B, age ,sex ,
case when a.dept=b.dept and a.sex = b.sex then 'Y' else 'N' end as Re
from a join b on a.name=b.name
case when a.dept=b.dept then 'Y' else 'N' end as Re
from a join b on a.name=b.name
这么简单拿出来晒
select e1.[name],
(e1.dept)Dept_A,
(e2.dept)Dept_B,
(e1.age)age,
(e2.sex)sex,
(case
when e1.dept = e2.dept then 'Y'
else 'N'
end)Re
from e1,e2
where e1.[name] = e2.[name]
要用full outer join 才行declare @A table(name varchar(10),Dept varchar(10),age int)
insert into @A select 'mary ','IT',23
insert into @A select 'henry','fs',25
insert into @A select 'joyce','pr',28
insert into @A select 'tom','pr',28 declare @B table(name varchar(10),Dept varchar(10),sex char(1))
insert into @B select 'mary ','HR','F'
insert into @B select 'henry','fs','m'
insert into @B select 'joyce','ps','F'
insert into @B select 'may','ps','F' select
isnull(A.name,b.name) ,isnull(A.Dept, b.Dept) as Dept_A,B.Dept as Dept_B,A.age,B.Sex,
(case when A.Dept=B.Dept then 'Y' else 'N' end) as Re
from @A A FULL outer join @B B on A.name=B.name
mary IT HR 23 F N
henry fs fs 25 m Y
joyce pr ps 28 F N
tom pr NULL 28 NULL N
may ps ps NULL F N
Select A.name as Name,A.dept as Dept_A,B.dept as Dept_B, Age, Sex,(case when a.dept=b.dept then 'y' else 'n' end) as Re
from @A a join @b b on a.name=b.name
case when a.dept=b.dept then Y' else 'N' end as Re
from a join b on a.name=b.name
ISNULL(A.name,b.name) AS name
,A.Dept AS Dept_A
,B.Dept AS Dept_B
,ISNULL(A.age,b.age) AS age
,ISNULL(A.Sex,b.Sex) AS sex
,(CASE A.Dept WHEN B.Dept THEN 'Y' ELSE 'N' END) AS Re
FROM
A JOIN B
ON A.name=B.name
AND A.name=B.name
ISNULL(A.name,b.name) AS name
,A.Dept AS Dept_A
,B.Dept AS Dept_B
,ISNULL(A.age,b.age) AS age
,ISNULL(A.Sex,b.Sex) AS sex
,(CASE A.Dept WHEN B.Dept THEN 'Y' ELSE 'N' END) AS Re
FROM
A JOIN B
ON A.name=B.name
CREATE TABLE #Dept_A(Name NVARCHAR(20) NULL,Dept NVARCHAR(20) NULL,age INT NULL)INSERT INTO #Dept_A VALUES('mary','IT',23)
INSERT INTO #Dept_A VALUES('henry','fs',25)
INSERT INTO #Dept_A VALUES('joyce','pr',28)CREATE TABLE #Dept_B(Name NVARCHAR(20) NULL,Dept NVARCHAR(20) NULL,sex CHAR(1) NULL)INSERT INTO #Dept_B VALUES('mary','HR','F')
INSERT INTO #Dept_B VALUES('henry','fs','M')
INSERT INTO #Dept_B VALUES('joyce','ps','F')
SELECT t1.name,t1.Dept AS 'Dept_A',t2.Dept AS 'Dept_B',t1.age,t2.Sex,CASE WHEN t1.Dept = t2.Dept THEN 'Y' ELSE 'N' END AS Re
FROM #Dept_A t1 JOIN #Dept_B t2 ON t1.Name = t2.Name
DROP TABLE #Dept_A
DROP TABLE #Dept_B
case when a.dept=b.dept then Y' else 'N' end as Re
from a join b on a.name=b.name
ge wei
要用full outer join 才行 SQL code
declare @A table(name varchar(10),Dept varchar(10),age int)
insert into @A select 'mary ','IT',23
insert into @A select 'henry','fs',25
insert into @A select 'joyce','pr',28
insert into @A select 'tom','pr',28 declare @B table(name varchar(10),Dept varchar(10),sex char(1))
insert into @B select 'mary ','HR','F'
insert into @B select 'henry','fs','m'
insert into @B select 'joyce','ps','F'
insert into @B select 'may','ps','F' select
isnull(A.name,b.name) ,isnull(A.Dept, b.Dept) as Dept_A,B.Dept as Dept_B,A.age,B.Sex,
(case when A.Dept=B.Dept then 'Y' else 'N' end) as Re
from @A A FULL outer join @B B on A.name=B.nameSQL codeif object_id('a') is not null
drop table a
go
create table a(name varchar(10), Dept varchar(10), age int)
----------------
insert a select 'mary','IT',23
insert a select 'henry','fs',25
insert a select 'joyce','pr',28
go if object_id('b') is not null
drop table b
go
create table b(name varchar(10), Dept varchar(10), Sex varchar(10))
insert b select 'mary','HR','F'
in…
INSERT INTO #Dept_A VALUES('henry','fs',25)
INSERT INTO #Dept_A VALUES('joyce','pr',28)CREATE TABLE #Dept_B(Name NVARCHAR(20) NULL,Dept NVARCHAR(20) NULL,sex CHAR(1) NULL)INSERT INTO #Dept_B VALUES('mary','HR','F')
INSERT INTO #Dept_B VALUES('henry','fs','M')
INSERT INTO #Dept_B VALUES('joyce','ps','F')
SELECT t1.name,t1.Dept AS 'Dept_A',t2.Dept AS 'Dept_B',t1.age,t2.Sex,CASE WHEN t1.Dept = t2.Dept THEN 'Y' ELSE 'N' END AS Re
FROM #Dept_A t1 JOIN #Dept_B t2 ON t1.Name = t2.Name
DROP TABLE #Dept_A
DROP TABLE #Dept_B
CREATE TABLE #Dept_A(Name NVARCHAR(20) NULL,Dept NVARCHAR(20) NULL,age INT NULL) INSERT INTO #Dept_A VALUES('mary','IT',23)
INSERT INTO #Dept_A VALUES('henry','fs',25)
INSERT INTO #Dept_A VALUES('joyce','pr',28) CREATE TABLE #Dept_B(Name NVARCHAR(20) NULL,Dept NVARCHAR(20) NULL,sex CHAR(1) NULL) INSERT INTO #Dept_B VALUES('mary','HR','F')
INSERT INTO #Dept_B VALUES('henry','fs','M')
INSERT INTO #Dept_B VALUES('joyce','ps','F')
SELECT t1.name,t1.Dept AS 'Dept_A',t2.Dept AS 'Dept_B',t1.age,t2.Sex,CASE WHEN t1.Dept = t2.Dept THEN 'Y' ELSE 'N' END AS Re
FROM #Dept_A t1 JOIN #Dept_B t2 ON t1.Name = t2.Name
DROP TABLE #Dept_A
DROP TABLE #Dept_B
case when a.dept=b.dept then 'Y' else 'N' end as Re
from a join b on a.name=b.name