sql 查询两张表两个字段不相同的数据

首先你两张表有什么关联么？

如果没有关联并且你只是想要相同字段的数据的话，下面的sql可以满足：

a).

--自动去掉a，b表中相同的数据

select same_col from a

union

select same_col from b

b).

--去a，b表的最大并集

select same_col from a

union all

select same_col from b

CREATE

TABLE

message

(

id

int,

name1

varchar(2),

data1

int,

name2

varchar(2),

data2

int

)

INSERT

INTO

message

SELECT

1,

'A',

4,

'A',

1

UNION

ALL

SELECT

2,

'B',

3,

'C',

2

UNION

ALL

SELECT

3,

'A',

3,

'B',

4

UNION

ALL

SELECT

4,

'B',

2,

'D',

6

UNION

ALL

SELECT

5,

'C',

5,

'A',

4

UNION

ALL

SELECT

6,

'A',

4,

'C',

7

UNION

ALL

SELECT

7,

'A',

5,

'A',

8

UNION

ALL

SELECT

8,

'B',

9,

'A',

3

UNION

ALL

SELECT

9,

'B',

7,

'B',

4

UNION

ALL

SELECT

10,

'C',

8,

'C',

5

SELECT

my_data,

CONCAT('(',

GROUP_CONCAT(my_name),

')')

AS

names

FROM

(

SELECT

'name1'

as

my_name,

data1

as

my_data

FROM

message

WHERE

name1

=

'A'

UNION

SELECT

'name2'

as

my_name,

data2

as

my_data

FROM

message

WHERE

name2

=

'A'

)

subQuery

GROUP

BY

my_data

ORDER

BY

my_data

DESC

+---------+---------------+

|

my_data

|

names

|

+---------+---------------+

|

8

|

(name2)

|

|

5

|

(name1)

|

|

4

|

(name1,name2)

|

|

3

|

(name1,name2)

|

|

1

|

(name2)

|

+---------+---------------+

5

rows

in

set

(0.00

sec)

sql 查询不重复的记录 可以使用 having count group by 组合

先把不重复数据的id查询出来 通过count(）计算 只有数目为1的才是不重复的数据

然后通过in选择出不重复记录的数据

例子：

统计出a表中name不重复的所有记录

select b.* from table b where b.id in(select a.id from table a group by a.name having count(a.id) <2)

//其中 name重复的数据的列名

---