星空网 > 软件开发 > 数据库

数据库拼接表

这是一道面试题,觉得有点意思就发出来了。

数据库拼接表

1。如图两个表,上为A,下为R

一、效果一数据库拼接表

二、效果二数据库拼接表

三、效果三数据库拼接表

 

CREATE DATABASE AR GO USE AR go CREATE TABLE A (  code INT PRIMARY KEY NOT NULL,  Area VARCHAR(20) ) INSERT INTO a VALUES(1,'A1') INSERT INTO a VALUES(2,'A2') INSERT INTO a VALUES(3,'A3') INSERT INTO a VALUES(5,'') INSERT INTO a VALUES(7,'') CREATE TABLE R (  code INT PRIMARY KEY NOT NULL,  Area VARCHAR(20) ) INSERT INTO r VALUES(1,'R1') INSERT INTO r VALUES(3,'R3') INSERT INTO r VALUES(6,'') INSERT INTO r VALUES(8,'')

 

--1 select * from A union select * from R order by code

--2 select identity(int,1,1) as guid,code,Area into #1 from A select identity(int,1,1) as guid,code,Area into #2 from R select a.code as code1,a.Area as Area1,b.code as code2,b.Area as Area2 into #3 from #1 a inner join #2 b on a.guid = b.guid select * from #3

--3 select identity(int,1,1) as guid,code,Area into #1 from A select identity(int,1,1) as guid,code,Area into #2 from R select a.code as code1,a.Area as Area1,b.code as code2,b.Area as Area2 into #3 from #1 a right join #2 b on a.guid = b.guid select * from #3

--1 SELECT * FROM a UNION SELECT * FROM r

--2 SELECT m.code AS code1,m.Area AS area1,n.code AS code2,n.Area AS area2 FROM  (SELECT * ,(SELECT COUNT(*)+1 FROM a a1 WHERE a1.code<a2.code) AS id FROM a a2 ) m  join  (SELECT * ,(SELECT COUNT(*)+1 FROM r r1 WHERE r1.code<r2.code) AS id FROM R r2) n  on m.id=n.id

 --3  SELECT m.code AS code1,m.Area AS area1,n.code AS code2,n.Area AS area2 FROM  (SELECT * ,(SELECT COUNT(*)+1 FROM a a1 WHERE a1.code<a2.code) AS id FROM a a2 ) m  LEFT join  (SELECT * ,(SELECT COUNT(*)+1 FROM r r1 WHERE r1.code<r2.code) AS id FROM R r2) n  on m.id=n.id

 




原标题:数据库拼接表

关键词:数据库

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

日本海外仓贴标:https://www.goluckyvip.com/tag/39994.html
日本海外仓推荐:https://www.goluckyvip.com/tag/39995.html
日本海外仓物流:https://www.goluckyvip.com/tag/39996.html
日本海外仓系统:https://www.goluckyvip.com/tag/39997.html
日本海外仓一件代发:https://www.goluckyvip.com/tag/39998.html
日本海外仓转运:https://www.goluckyvip.com/tag/39999.html
活动回顾 | 千亚麦佳携手MoonSees呈现精彩亚马逊运营管理课程:https://www.kjdsnews.com/a/1836386.html
生日景点免费 上海景点生日免费景点:https://www.vstour.cn/a/363173.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流