时间戳

首页 > 解决方案 > 具有唯一行的两个表的 CREATE VIEW

sql - 具有唯一行的两个表的 CREATE VIEW

问题描述

我想创建两个表的视图，合并它们，其中 table1 中的列！= table2 中的列，然后插入没有重复列的行。

CREATE TABLE IF NOT EXISTS test(Birds, Animals, Air, Earth);
INSERT INTO test VALUES("Crow", "Dog", "Oxygen", "Not Flat");

CREATE TABLE IF NOT EXISTS test1 (Birds, Animals, Air, Earth, Sky);
INSERT INTO test1 VALUES("Crow", "Dog", "Oxygen", "Not Flat", "Blue");
INSERT INTO test1 VALUES("Eagle", "Dog", "Oxygen", "Not Flat", "Blue");

CREATE VIEW view_name AS SELECT * FROM test, test1 WHERE test.Birds != test1.Birds'

测试表：

Birds|Animals|Air|Earth
Crow|Dog|Oxygen|Not Flat

测试1表：

Birds|Animals|Air|Earth|Sky
Crow|Dog|Oxygen|Not Flat|Blue
Eagle|Dog|Oxygen|Not Flat|Blue

电流输出：

Birds|Animals|Air|Earth|Birds:1|Animals:1|Air:1|Earth:1|Sky
Crow|Dog|Oxygen|Not Flat|Eagle|Dog|Oxygen|Not Flat|Blue

期望的输出：

Birds|Animals|Air|Earth|Sky
Eagle|Dog|Oxygen|Not Flat|Blue

标签： sqlsqlite

解决方案

你可以用不同的方式解决这个问题。不过，我有一个解决方案给你。请检查查询并告诉我=>

DECLARE @test TABLE(Birds VARCHAR(50), Animals VARCHAR(50), Air VARCHAR(50), Earth VARCHAR(50)) ;
INSERT INTO @test VALUES('Crow', 'Dog', 'Oxygen', 'Not Flat');

DECLARE @test1 TABLE (Birds VARCHAR(50), Animals VARCHAR(50), Air VARCHAR(50), Earth VARCHAR(50),Sky VARCHAR(50));
INSERT INTO @test1 VALUES('Crow', 'Dog', 'Oxygen', 'Not Flat', 'Blue');
INSERT INTO @test1 VALUES('Eagle', 'Dog', 'Oxygen', 'Not Flat', 'Blue');

CREATE VIEW view_name AS
SELECT t1.* FROM @test t 
INNER JOIN @test1 t1 ON t.Birds<>t1.Birds

注意：此代码是用 SQL Server 编写的。

推荐阅读