sql sever中如何从A表中将B表包含的与A表相同的数据剔除？(A表数据多余B表，且包含全部B表数据)

Question

排重的条件是这三个字段要完全相同才能排除掉
如A表中3个字段
姓名 性别 生日
a 1 19850205
b 1 19860205
c 0 19840328
d 0 19810928
e 0 19780626
e 1 19880716
B表中3个字段
姓名 性别 生日
a 1 19850205
c 0 19840328
e 0 19780626
想要的结果是
姓名 性别 生日
a 1 19850205
c 0 19840328
e 0 19780626
想要的结果描述错了。。。是想要将上述的结果从A表剔除。。
即结果为
姓名 性别 生日
b 1 19860205
d 0 19810928
e 1 19880716

Answer 1

select a.* from a,b
where a. 姓名 = b.姓名
and a.性别 = b.性别
and a. = b.生日

ps :中文字段名 貌似要打引号 还是中括号 你试试吧
本人不建议中文做字段名的

Answer 2

CREATE TABLE #A (
姓名 VARCHAR(2),
性别 INT,
生日 INT
)
go

INSERT INTO #A
SELECT 'a', 1, 19850205 UNION ALL
SELECT 'b', 1, 19860205 UNION ALL
SELECT 'c', 0, 19840328 UNION ALL
SELECT 'd', 0, 19810928 UNION ALL
SELECT 'e', 0, 19780626 UNION ALL
SELECT 'e', 1, 19880716

CREATE TABLE #B (
姓名 VARCHAR(2),
性别 INT,
生日 INT
)
GO

INSERT INTO #B
SELECT 'a', 1, 19850205 UNION ALL
SELECT 'c', 0, 19840328 UNION ALL
SELECT 'e', 0, 19780626
GO

SELECT * FROM #A
EXCEPT
SELECT * FROM #B
go

姓名 性别 生日
-- ----------- -----------
b 1 19860205
d 0 19810928
e 1 19880716

(3 行受影响)

Answer 3

Should you buy a product just because some celebrities use it? The Sedu ionic ceramic tourmaline flat iron ,ghd ceramic hair straightenerhair straightener is one of those products. There's a rumor that Jennifer Lopez and Jennifer Aniston have used a Sedu.However, whether or not this rumor is true, the real effectiveness of a Sedu has received many rave reviews by women who have transformed their hair thanks to the Sedu.The Sedu ionic ceramic tourmaline flat iron hair straightener has a number of features that make other flat irons seem ordinary.Sedu uses tourmaline,Hair Straightener, a gemstone that when heated generates up to six times more negative ions than ordinary flat irons. Negative ions smoothes the outer layer of the hair making it look shiny. Hair bombarded with negative ions help to eliminate frizz or split ends and makes it more manageable.With the Sedu, the hair passes between smooth ceramic plates that won't kink or damage hair. The Sedu plates generate infrared heat that penetrates the inner core of each strand of hair so the hair's outer layer won't dry out. The temperature setting on the Sedu is adjustable to make it safer or easier to straighten any type of hair. If the hair is fragile or damaged,GHD MK4 series, the user can select a safer lower temperature. The temperature can be set higher to make the effort of straightening thick or coarse hair easier.To top off all these great features, the Sedu warms up fast. It only takes 25 seconds for the Sedu to reach the temperature selected. Ordinary flat irons may take up to 60 seconds to reach the same temperature. Straightening curvy or wavy hair takes less time with a Sedu.Recapping what we have just said, the Sedu heats up fast, can straighten any hair type,pink ghd, won't damage it and delivers six times more negative ions than ordinary irons making any women's hair shinier and manageable.So,CHI Tribal Zebra Collection, as you can see the Sedu ionic ceramic tourmaline flat iron hair straightener doesn't need the buzz that someone famous is using it. Sedu's effectiveness and customer testimonials are more than enough.Copyright 2007 by Wanda Lam

Answer 4

SELECT * INTO #C FROM A;

insert #C select * from B ;

select distinct * from #C

DROP TABLE #C

Answer 5

select * from A
INTERSECT
select * from B;本回答被网友采纳