性感美女福利视频,韩国美女大胸视频,韩国高清美女视频

如何比較兩個(gè)數(shù)據(jù)表

2009年5月27日

有些時(shí)候，我們可能想要比較一下兩個(gè)數(shù)據(jù)表，以找到其中不同的數(shù)據(jù)。比如，在進(jìn)行數(shù)據(jù)移植的時(shí)候，或是在合并數(shù)據(jù)的時(shí)候，或是在比對(duì)驗(yàn)證數(shù)據(jù)的時(shí)候。當(dāng)然比較兩個(gè)表，需要這兩個(gè)表結(jié)構(gòu)是一樣的。

我們先假設(shè)一下有如下表結(jié)構(gòu)：

CREATE TABLE jajal  
(  
    user_id integer NOT NULL,  
    first_name character varying(255),  
    last_name character varying(255),  
    grade character(1),  
    CONSTRAINT jajal_pkey PRIMARY KEY (user_id)  
) 

然后，我們有兩張表——jajal和jajal_copy，其內(nèi)容如下：

jajal

user_id	first_name	last_name	grade
1	Some	Dude	A
2	Other	Guy	B
3	You are	Welcome	B
4	What	Other	A
5	INeed	You	C
6	Mixed	Nuts	Z
7	Kirk	Land	B
8	Bit	Shooter	A
9	Sun	Microsystem	C
10	Extra	Fancy	B

jajal_copy

user_id	first_name	last_name	grade
1	Some	Dude	A
2	Other	Guy	B
3	You are	Welcome	B
4	What	Other	A
5	INeed	You	C
6	Mixed	Nuts	C
7	Kirk	Land	B
8	Bit	Shooter	A
9	Sun	Microsystem	C
10	Extra	Fancy	B

要比較這兩張表的數(shù)據(jù)，找出不一樣的數(shù)據(jù)行。我們可以使用outer join 技術(shù)。我給outer join做了一個(gè)鏈接，是Wikipedia的，如果你對(duì)這個(gè)技術(shù)不是很清楚，還請(qǐng)你行看看其技術(shù)細(xì)節(jié)。

下面是具體的SQL語句：

使用FULL OUTER JOIN

SELECT 
     *  
FROM 
     jajal j  
     FULL OUTER JOIN jajal_copy jc ON jc.first_name = j.first_name  
     AND jc.last_name = j.last_name  
     AND jc.grade = j.grade  
     AND jc.user_id = j.user_id  
WHERE 
     j.user_id IS NULL 
     OR jc.user_id IS NULL 

運(yùn)行結(jié)果如下：

user_id	first_name	last_name	grade	user_id	first_name	last_name	grade
[NULL]	[NULL]	[NULL]	[NULL]	6	Mixed	Nuts	C
6	Mixed	Nuts	Z	[NULL]	[NULL]	[NULL]	[NULL]

使用NATURAL FULL OUTER JOIN

關(guān)于natural join，你可以看看Wikipedia是怎么說的。

SELECT 
       *  
FROM 
       jajal j  
       NATURAL FULL OUTER JOIN jajal_copy jc  
WHERE 
       j.user_id IS NULL 
       OR jc.user_id IS NULL 

運(yùn)行結(jié)果如下：

user_id	first_name	last_name	grade
6	Mixed	Nuts	C
6	Mixed	Nuts	Z

MySQL SQL 代碼

MySQL 并不支持 FULL OUTER JOIN，但是我們可以使用LEFT JOIN 和 RIGHT JOIN 來實(shí)現(xiàn)這一功能。如下所示。

SELECT 
*  
FROM 
jajal j  
LEFT JOIN jajal_copy jc ON jc.first_name = j.first_name  
AND jc.last_name = j.last_name  
AND jc.grade = j.grade  
AND jc.user_id = j.user_id  
WHERE 
jc.user_id IS NULL 
UNION ALL 
SELECT 
*  
FROM 
jajal j  
RIGHT JOIN jajal_copy jc ON jc.first_name = j.first_name  
AND jc.last_name = j.last_name  
AND jc.grade = j.grade  
AND jc.user_id = j.user_id  
WHERE 
j.user_id IS NULL 

或者你更喜歡NATURAL JOIN 版本

SELECT 
*  
FROM 
jajal j  
NATURAL LEFT JOIN jajal_copy jc  
WHERE 
jc.user_id IS NULL 
UNION ALL 
SELECT 
*  
FROM 
jajal j  
NATURAL RIGHT JOIN jajal_copy jc  
WHERE 
j.user_id IS NULL 

當(dāng)然，如果你需要一個(gè)MySQL的存儲(chǔ)過程的話，下面是一個(gè)示例：

DELIMITER $$  
   
CREATE PROCEDURE `db_schema`.`tablediff`  
    (schema_name VARCHAR(64), table1 VARCHAR(64), table2 VARCHAR(64))  
BEGIN 
    DECLARE done INT DEFAULT 0;  
    DECLARE sql_statement TEXT DEFAULT '';  
    DECLARE sql_statement_where TEXT DEFAULT '';  
    DECLARE sql_statement_pk TEXT DEFAULT '';  
    DECLARE col_name VARCHAR(64);  
    DECLARE col_name_cur CURSOR FOR 
        SELECT 
            COLUMN_NAME  
        FROM 
            information_schema.COLUMNS  
        WHERE 
            TABLE_SCHEMA = schema_name  
            AND TABLE_NAME = table1  
    ;  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
   
    OPEN col_name_cur;  
    traverse_columns: LOOP  
        FETCH col_name_cur INTO col_name;  
   
        IF done THEN 
            CLOSE col_name_cur;  
            LEAVE traverse_columns;  
        END IF;  
   
        SET sql_statement_where = CONCAT(sql_statement_where,  
            ' AND a.', col_name, ' = b.', col_name);  
        SET sql_statement_pk = CONCAT(sql_statement_pk,  
            'AND b.', col_name, ' IS NULL');  
    END LOOP;  
   
    SELECT 
        COLUMN_NAME INTO col_name  
    FROM 
        information_schema.KEY_COLUMN_USAGE  
    WHERE 
        CONSTRAINT_SCHEMA = schema_name  
        AND CONSTRAINT_NAME = 'PRIMARY' 
        AND TABLE_NAME = table1  
    LIMIT 1  
    ;  
    IF col_name IS NOT NULL THEN 
        SET sql_statement_pk = CONCAT('AND b.', col_name, ' IS NULL');  
    END IF;  
   
    SET sql_statement = CONCAT('SELECT * FROM ', schema_name, '.', table1, ' a LEFT JOIN ', schema_name, '.', table2, ' b ON TRUE');  
    SET sql_statement = CONCAT(sql_statement, sql_statement_where, ' WHERE TRUE ', sql_statement_pk);  
    SET sql_statement = CONCAT(sql_statement, ' UNION ALL SELECT * FROM ', schema_name, '.', table1, ' b RIGHT JOIN ', schema_name, '.', table2, ' a ON TRUE');  
    SET sql_statement = CONCAT(sql_statement, sql_statement_where, ' WHERE TRUE ', sql_statement_pk);  
   
    SET @s = sql_statement;  
    PREPARE stmt1 FROM @s;  
    EXECUTE stmt1;  
    DEALLOCATE PREPARE stmt1;  
   
END$$  
DELIMITER ; 

PostgreSQL 下的SQL語句

下面是PostgreSQL的一個(gè)存儲(chǔ)過程：

CREATE OR REPLACE FUNCTION tablediff (  
    IN schema_name VARCHAR(64),  
    IN table1 VARCHAR(64),  
    IN table2 VARCHAR(64)  
) RETURNS BIGINT AS 
$BODY$  
DECLARE 
    the_result BIGINT DEFAULT 0;  
    sql_statement TEXT DEFAULT '';  
    sql_statement_where TEXT DEFAULT '';  
    sql_statement_pk TEXT DEFAULT '';  
    col_name VARCHAR(64);  
    col_name_cur CURSOR FOR 
        SELECT 
            column_name  
        FROM 
            information_schema.columns  
        WHERE 
            table_catalog = schema_name  
            AND table_schema = 'public' 
            AND table_name = table1  
    ;  
BEGIN 
    OPEN col_name_cur;  
   
    LOOP  
        FETCH col_name_cur INTO col_name;  
        IF NOT FOUND THEN 
            EXIT;  
        END IF;  
   
        sql_statement_where := sql_statement_where || ' AND a.' || col_name || ' = b.' || col_name;  
    END LOOP;  
   
    SELECT 
        column_name INTO col_name  
    FROM 
        information_schema.table_constraints tc  
        JOIN information_schema.constraint_column_usage ccu ON 
            ccu.constraint_name = tc.constraint_name  
    WHERE 
        tc.table_catalog = schema_name  
        AND tc.table_schema = 'public' 
        AND tc.table_name = table1  
    LIMIT 1  
    ;  
   
    IF col_name IS NOT NULL THEN 
        sql_statement_pk := ' a.' || col_name || ' IS NULL';  
        sql_statement_pk := sql_statement_pk || ' OR b.' || col_name || ' IS NULL';  
    END IF;  
   
    sql_statement := 'SELECT COUNT(*) FROM ' || schema_name || '.public.' || table1 || ' a FULL OUTER JOIN ' || schema_name || '.public.' || table2 || ' b ON TRUE';  
    sql_statement := sql_statement || sql_statement_where || ' WHERE ' || sql_statement_pk;  
   
    EXECUTE sql_statement INTO the_result;  
   
    RETURN the_result;  
END;$BODY$  
LANGUAGE 'plpgsql' STABLE; 

文章：來源

(3 人打了分，平均分： 5.00 )

Loading ...

陳皓數(shù)據(jù)庫 MySQL, PostgreSQL, SQL

本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊舉報(bào)。

国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看