Question

因此,我有两个表格。两者共有五个栏目(单位名称、用户数、用户界限、专题、额外),同时各栏也有其他栏目。

我想要的是使用特别选举和检索表A+表组合。因此,房名价值必须具有独特性。

表3 A中载有一个房间名称=1室,表B载有一个房间名称=1室,优于表A,不从表B中增列该项目。考虑到只有房间名称价值不变。用户数、用户界限、对象、外差数与表A不同。 B 即使房间名称价值相同。

Answer 1

SELECT roomname, usercount, userlimit, topic, extra
FROM TableA

UNION ALL

SELECT roomname, usercount, userlimit, topic, extra
FROM TableB
WHERE roomname NOT IN
    ( SELECT roomname FROM TableA )

Answer 2

你们试图做的比照更为完善。前者只是给你一个综合成果,而后者则发挥JOQIN的作用,改变结果。

To combine the result of two queries, UNION the SELECT queries, such as

(SELECT roomname, usercount, userlimit, topic, extra
    FROM TableA)
UNION DISTINCT
(SELECT roomname, usercount, userlimit, topic, extra
    FROM TableB);

<编码> UNION DISTINCT 消除了重复。我没有一台自动服务器来测试yn子,但是,在manual上,应当正确。

Answer 3

SELECT roomname, usercount, userlimit, topic, extra
FROM
     (
(SELECT roomname, usercount, userlimit, topic, extra
    FROM TableA)
UNION
(SELECT roomname, usercount, userlimit, topic, extra
    FROM TableB)
) AAA
GROUP BY roomname

通知我不使用任何合并的职能,这只会使质询只反映每个群体的第一个结果(在这种情况下,这些结果可能来自联合国决定中的第一个选举。

Also @ypercube solution s good (and simpler to understand than mine). But due to a bug in MySQl, I think my solution is more efficient (you need to check this).

Answer 4

SELECT roomname, usercount, userlimit, topic, extra
FROM TableA

UNION

SELECT roomname, usercount, userlimit, topic, extra
FROM TableA

否则,MySQL的工会问询是不同的,消除了重复浏览(aka UNION DISTINCT)。如果您希望获得重复浏览量,则请填写UNION ALL。

ok,因为你现在说,只有房间名称在桌子之间是常见的/复制的,所以如何做这样的事情:

select tableA.*, tableB.*
from tableA
join tableB on tableA.roomname = tableB.roomname
where (tableA.usercount <> tableB.usercount) or (tableA.userlimit <> tableB.userlimit)
   or (tableA.topic <> tableB.topic) or (tableA.extra <> tableB.extra)

这使两个表格中所有各行各有相同的房间名称,但在另一个或多个领域有差异。如果你只想显示这些领域的确切重复,则将<代码><>改为=。

第2部分

select tableB.*
from tableB
left join tableA on tableA.roomname = tableB.roomname
where tableA.roomname is null

这将使所有记录从表B中收回,在表A中,房名外地有对应关系。

在文件后解释:

SELECT roomname, usercount, userlimit, topic, extra FROM TableA UNION ALL SELECT roomname, usercount, userlimit, topic, extra FROM TableB WHERE roomname NOT IN ( SELECT roomname FROM TableA )

友情链接