sql database table structure comparison how to do

How to compare groups of data in two tables in SQLServer

Started a while ago, in the project, encountered such a SQL query requirements, there are two tables with the same structure (table_lefttable_right), as follows: figure 1. check the table table_left of the groups (groupId), whether the table table_right there exists a group (groupId) data (data) and its data (data) is exactly equal. As shown in Figure 1. you can see that table table_lef

Beginning

A while back, I came across this SQL query requirement for a project with two tables (table_left&table_right) of the same structure as follows:

Figure 1. Check the groups (groupId) of table table_left, whether there exists a group (groupId) data (data) in table table_right which is exactly equal to its data (data).

As shown in Figure 1. it can be seen that the table table_left and table_right there are two sets of data complete equality:

Figure 2.

Analysis

From the above two tables, it can be known that they store a group of a group of data; then, the next thing I analyze with the help of enumeration of mathematical sets and operations.

First, the groups of data in the two tables are described through the enumeration of sets:

Figure 3.

There are only two cases here, equal and unequal. For inequality, it can be subdivided into partial equality, inclusion, and complete inequality. Using set description, you can use intersection, Hong Kong web hosting, subset, and concatenation. As in Figure 4. below, Hong Kong web hosting, I list these common cases:

Figure 4.

Realization

In a database, there are many ways to find out the existence of table table_left and table table_right the same data in the group, here I list the two common methods.

(The following SQL script, is based on the data in Figure 4. as a reference)

Method 1:

Through the “Select…From…Orderby…xmlforpath(”)” the groups of the data column data concatenated (e.g., Figure 4. table_left group #11 column data concatenated into “data1-data2-data3”), the other subgroups (including the table table_right) to achieve this method of data column data concatenated together; and then through the comparison of the two tables concatenated after the existence of equal fields, if it is equal to the This means that more than two groups of data are equal, which can determine which table table_left data in the table table_right there is a table table_right data with its data is completely equal to the group.

For method 1, the U.S. space, you need to add a field dataPath to the original table, used to store the data column data concatenation results, such as:

200)

200)

How to compare the difference between table structure and table content of mysql database

First export the table structure of each library to a file and then compare the two files.

mysqlmp-skip-comments-skip-extended-insert-uroot-pdatabase1>file1.sql

mysqlmp-skip-comments-skip-extended- insert-uroot-pdatabase2>file2.sql

difffile1.sqlfile2.sql

Actually there are some comparison tools, recommend one

mysql-comparison-tools

How to compare a column of data in two tables using sql statement.

Hello!

First get the non-duplicated IDs, i.e., the non-duplicated concatenation of the x, y tables, observe that the x, y tables are similar in structure, and just rank them by union.

Use the data obtained in the first step to left-link the extended tables of x, y tables (step 3).

Separately on the x, y table for grouping and totaling, for the picture in the y table ID for A record appears many times, the last display for ID for A inventory number of 2, such data to deal with, in order to facilitate the final display.

The comparison field value is obtained by (inventory count – inventory count).

SELECT

t.ID,

IFNULL(t.`inventory count`, 0) `inventory quantity`,

IFNULL(t.`inventory count`, 0) `inventory quantity`,

(

IFNULL(t.`inventory count`, 0) – IFNULL( t.`inventory quantity`, 0)

) `comparison`

FROM

(

SELECT

a.ID ID,

p1.`inventory quantity`,

p2.`inventory quantity`

FROM

(

SELECT

ID

FROM

x

UNION

SELECT

ID

FROM

y

) a

LEFT JOIN (

SELECT

ID,

sum(`quantity in stock`) `quantity in stock`

FROM

x

GROUP BY

ID

) p1 ON a.id = p1.id

LEFT JOIN (

SELECT

ID,

sum(`inventory quantity`) `inventory quantity`

FROM

y

GROUP BY

ID

) p2 ON a.id = p2.id

) t

ORDER BY

ID

Hope this helps!

How to compare 2 table structures by sql statement

If it is an Oracle database, you can compare it by the following statement

selectv1.table_name,v2.table_name,v1.column_name,v2.column_name,

v1.data_type,v2.data_type,v1.data_length,v2.data_length,v1.data_precision,v1.data_scale,v2. data_type,v2.data_type,v1.data_length,v2.data_length,v1.data_precision,v2.data_precision,v1.data_scale,v2.data_scale

from

(select*fromuser_tab_columnswheretable_name=’table1′)v1fulljoin

(select*fromuser_tab_columnswheretable_name=’table2′)v2onv1. column_name=v2.column_name

SQL如何对比两张表的数据?

insert

into

tablea

select

*

from

tablea

where

not

exists

(selelct

‘1’

from

tableb

where

tablea.serverid

=

serverid

and

tablea.driverid

=

driverid

and

tablea.driversize

=

driversize)

update

tablea

set

tablea.driversize

=

tablea.driversize

from

tablea

,tableb

where

tablea.serverid

=

serverid

and

tablea.driverid

=

driverid

如果你以tableb为准的话,你最好在tableb上加个触发器

How to compare two tables with SQL

Oracle can be like this:

select*from table1minusselect*from table2

Sqlserver2005 can be like this:

select*from table1exceptselect*from table2

sqlserver2000 can be like this:

select*from table1wherenotexists(select1from table2) from table 2

Sqlserver2000 can be like this:

select*from table1wherenotexists(select1from table2whereC=table1.AandD=table1.B)

This way in sqlserver2000 is not available in Oracle and sqlserver2005 works too