27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([studentnum] int,[name] nvarchar(26),[Age] int)
Insert #T1
select 20048,N'liming',21 union all
select 90031,N'jojo',20
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([sex] nvarchar(21),[stuAdderss] nvarchar(23),[city] nvarchar(22),[studentnum] INT )
Insert #T2
select N'男',N'天河区',N'广州',20048 union all
select N'女',N'宝安区',N'深圳',90031
Go
--测试数据结束
SELECT #T1.studentnum,
#T1.name,
#T1.Age,
#T2.sex,
#T2.stuAdderss,
#T2.city
FROM #T1
JOIN #T2
ON #T2.studentnum = #T1.studentnum; --通过外键关联
USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
IF OBJECT_ID('t3') IS NOT NULL DROP TABLE t3
GO
CREATE TABLE t1(
studentNum BIGINT,
[name] NVARCHAR(20),
Age INT
)
GO
CREATE TABLE t2(
studentNum BIGINT,
sex NCHAR(1),
stuAddress NVARCHAR(30),
city NVARCHAR(20)
)
GO
CREATE TABLE t3(
studentNum BIGINT,
[name] NVARCHAR(20),
Age INT,
sex NCHAR(1),
stuAddress NVARCHAR(20),
city NVARCHAR(20)
)
GO
SET NOCOUNT ON
INSERT INTO t1 VALUES (15209020048,'liming',21)
INSERT INTO t1 VALUES (14609090031,'jojo',20)
INSERT INTO t2 VALUES (15209020048,'男','天河区','广州')
INSERT INTO t2 VALUES (14609090031,'女','宝安区','深圳')
--插入
INSERT INTO [dbo].[t3]
([studentNum]
,[name]
,[Age]
,[sex]
,[stuAddress]
,[city])
SELECT
t1.studentNum
,t1.[name]
,t1.Age
,t2.sex
,t2.stuAddress
,t2.city
FROM t1
INNER JOIN t2
ON t1.studentNum=t2.studentNum
--查询
SELECT * FROM t3
/*
studentNum name Age sex stuAddress city
-------------------- -------------------- ----------- ---- -------------------- --------------------
15209020048 liming 21 男 天河区 广州
14609090031 jojo 20 女 宝安区 深圳
*/
USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
IF OBJECT_ID('t3') IS NOT NULL DROP TABLE t3
GO
CREATE TABLE t1(
studentNum BIGINT,
[name] NVARCHAR(20),
Age INT
)
GO
CREATE TABLE t2(
sex NCHAR(1),
stuAddress NVARCHAR(30),
city NVARCHAR(20)
)
GO
CREATE TABLE t3(
studentNum BIGINT,
[name] NVARCHAR(20),
Age INT,
sex NCHAR(1),
stuAddress NVARCHAR(20),
city NVARCHAR(20)
)
GO
SET NOCOUNT ON
INSERT INTO t1 VALUES (15209020048,'liming',21)
INSERT INTO t1 VALUES (14609090031,'jojo',20)
INSERT INTO t2 VALUES ('男','天河区','广州')
INSERT INTO t2 VALUES ('女','宝安区','深圳')
--插入
INSERT INTO [dbo].[t3]
([studentNum]
,[name]
,[Age]
,[sex]
,[stuAddress]
,[city])
SELECT
t11.studentNum
,t11.[name]
,t11.Age
,t22.sex
,t22.stuAddress
,t22.city
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS rid, * FROM t1
) AS t11
INNER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS rid, * FROM t2
) AS t22
ON t11.rid=t22.rid
--查询
SELECT * FROM t3
/*
studentNum name Age sex stuAddress city
-------------------- -------------------- ----------- ---- -------------------- --------------------
15209020048 liming 21 男 天河区 广州
14609090031 jojo 20 女 宝安区 深圳
*/
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([studentnum] int,[name] nvarchar(26),[Age] int)
Insert #T1
select 20048,N'liming',21 union all
select 90031,N'jojo',20
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([sex] nvarchar(21),[stuAdderss] nvarchar(23),[city] nvarchar(22))
Insert #T2
select N'男',N'天河区',N'广州' union all
select N'女',N'宝安区',N'深圳'
Go
--测试数据结束
SELECT t1.studentnum,t1.name,t1.Age,t2.sex,t2.stuAdderss,t2.city FROM (
Select *,ROW_NUMBER()OVER(ORDER BY GETDATE()) rn from #T1)t1
JOIN (SELECT *,ROW_NUMBER()OVER(ORDER BY GETDATE()) rn FROM #T2 )t2
ON t2.rn = t1.rn