22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
bh INT,
uname NVARCHAR(10)
)
CREATE TABLE B(
bh INT,
uname NVARCHAR(10),
rq CHAR(8),
zg NVARCHAR(10)
)
GO
INSERT INTO A VALUES (1,'王峰')
INSERT INTO A VALUES (2,'李洋')
INSERT INTO A VALUES (3,'常亮')
INSERT INTO A VALUES (4,'骆强')
INSERT INTO A VALUES (5,'鲁丽')
INSERT INTO A VALUES (6,'李雪')
INSERT INTO A VALUES (7,'康柏')
INSERT INTO A VALUES (8,'吴媛')
INSERT INTO A VALUES (9,'孙宁')
INSERT INTO A VALUES (10,'赵大亮')
GO
INSERT INTO B (bh,rq,zg) VALUES (8,'19780608','赵大')
INSERT INTO B (bh,rq,zg) VALUES (8,'19780608','赵大')
INSERT INTO B (bh,rq,zg) VALUES (8,'19780608','赵大')
INSERT INTO B (bh,rq,zg) VALUES (6,'19810201','赵大')
INSERT INTO B (bh,rq,zg) VALUES (7,'19900915','赵大')
INSERT INTO B (bh,rq,zg) VALUES (1,'19970701','赵大')
INSERT INTO B (bh,rq,zg) VALUES (2,'19820303','赵大')
INSERT INTO B (bh,rq,zg) VALUES (10,'19820303','吴晓')
INSERT INTO B (bh,rq,zg) VALUES (10,'19820303','吴晓')
INSERT INTO B (bh,rq,zg) VALUES (5,'19991118','赵大')
GO
SELECT
b.bh AS [编号]
,(SELECT a.uname FROM A WHERE a.bh=b.bh) AS [姓名]
,rq AS [日期]
,zg AS [主管]
--into C
FROM B
select B.编号, A.姓名
from B left join A on a.编号=b.编号
USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
bh INT,
uname NVARCHAR(10)
)
CREATE TABLE B(
bh INT,
uname NVARCHAR(10),
rq CHAR(8),
zg NVARCHAR(10)
)
GO
INSERT INTO A VALUES (1,'王峰')
INSERT INTO A VALUES (2,'李洋')
INSERT INTO A VALUES (3,'常亮')
INSERT INTO A VALUES (4,'骆强')
INSERT INTO A VALUES (5,'鲁丽')
INSERT INTO A VALUES (6,'李雪')
INSERT INTO A VALUES (7,'康柏')
INSERT INTO A VALUES (8,'吴媛')
INSERT INTO A VALUES (9,'孙宁')
INSERT INTO A VALUES (10,'赵大亮')
GO
INSERT INTO B (bh,rq,zg) VALUES (8,'19780608','赵大')
INSERT INTO B (bh,rq,zg) VALUES (8,'19780608','赵大')
INSERT INTO B (bh,rq,zg) VALUES (8,'19780608','赵大')
INSERT INTO B (bh,rq,zg) VALUES (6,'19810201','赵大')
INSERT INTO B (bh,rq,zg) VALUES (7,'19900915','赵大')
INSERT INTO B (bh,rq,zg) VALUES (1,'19970701','赵大')
INSERT INTO B (bh,rq,zg) VALUES (2,'19820303','赵大')
INSERT INTO B (bh,rq,zg) VALUES (10,'19820303','吴晓')
INSERT INTO B (bh,rq,zg) VALUES (10,'19820303','吴晓')
INSERT INTO B (bh,rq,zg) VALUES (5,'19991118','赵大')
GO
--添加两个索引( 如果原本是主键或者有索引就不要加了 )
CREATE INDEX ix_a_bh ON a(bh)
CREATE INDEX ix_b_bh ON b(bh)
--改为 left 连接
--不过,还是要加过滤条件能能用得上索引
--你不过滤,索引也没有用处
SELECT
b.bh AS [编号]
,a.uname AS [姓名]
,rq AS [日期]
,zg AS [主管]
--into C
FROM B
LEFT JOIN A ON a.bh=b.bh