如何显示匹配和不匹配sql？

cu6pst1q 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(285)

我试图从两个表中获取所有记录，并添加状态匹配或不匹配。我在mysql中尝试了这段代码，但是这段代码只显示一条记录而不是所有记录

SELECT 
  id, source, destination, amount, CASE 
    WHEN COUNT(*) > 1 THEN "MATCH" ELSE "MISMATCH" END AS "status" 
FROM 
  (
   SELECT 
    temporary1.id, temporary1.source, temporary1.destination, temporary1.amount
      FROM temporary1
   UNION ALL 
   SELECT temporary2.id, temporary2.source, temporary2.destination, temporary2.amount 
      FROM temporary2
   ) compare  ORDER BY `id` ASC

你能指出我的错误在哪里吗？
@对不起，我没有详细解释。我有两个列相同的表。列的名称是 id , source ，和 destination .
例如，记录在 temporary1 table

+----+--------+-------------+--------+
| id | source | destination | amount |
+----+--------+-------------+--------+
|  1 | Adam   | Helen       |    100 |
|  2 | Mai    | Dan         |    200 |
+----+--------+-------------+--------+

从temporary1表

+----+--------+-------------+--------+
| id | source | destination | amount |
+----+--------+-------------+--------+
|  1 | Adam   | Helen       |    100 |
|  2 | Marina | Daniel      |    400 |
+----+--------+-------------+--------+

查询的结果应该是这样的

+----+--------+-------------+--------+----------+
| id | source | destination | amount |  status  |
+----+--------+-------------+--------+----------+
|  1 | Adam   | Helen       |    100 | MATCH    |
|  2 | Mai    | Dan         |    200 | MISMATCH |
|  2 | Marina | Daniel      |    400 | MISMATCH |
+----+--------+-------------+--------+----------+
``` `status` 列的值将为 `MATCH` 表中的if记录 `temporary1` 以及 `temporary2` 在两个表中都存在，否则结果将为空 `MISMATCH` . 当我运行上一个查询时，它给出了我想要的相同结构。但只显示一个记录。
数据库是mysql 10.4.12版。我对数据库还是新手。我不知道sql和数据库有什么区别。所以我认为所有的sql数据库都可以运行相同的查询。

sql mysql

来源：https://stackoverflow.com/questions/62987691/how-to-show-match-and-unmatch-sql

2条答案

按热度按时间

ndasle7k1#

你似乎只是需要 GROUP BY :

SELECT id, source, destination, amount, 
       (CASE WHEN COUNT(*) > 1 THEN "MATCH" ELSE "MISMATCH" END) AS "status" 
FROM ((SELECT temporary1.id, temporary1.source, temporary1.destination, temporary1.amount
       FROM temporary1
      ) UNION ALL 
      (SELECT temporary2.id, temporary2.source, temporary2.destination, temporary2.amount 
       FROM temporary2
      )
     ) compare 
GROUP BY id, source, destination, amount
ORDER BY `id` ASC

赞(0）回复(0）举报 2021-07-24

qvsjd97n2#

您需要在sql中添加GROUPBY子句，因为您正在计算count（1）。
示例sql-

select id, source, destination, amount, CASE WHEN ccount > 1 THEN "MATCH" ELSE "MISMATCH" END AS "status" 
from (SELECT id, source, destination, amount, COUNT(*) as ccount 
FROM (SELECT temporary1.id, temporary1.source, temporary1.destination, temporary1.amount FROM temporary1
UNION ALL SELECT temporary2.id, temporary2.source, temporary2.destination, temporary2.amount 
FROM temporary2) 
group by id, source, destination, amount)

不过，我建议您使用intersect、minus和union来获取匹配的记录和不匹配的记录。
下面的sql将返回匹配的记录-

SELECT temporary1.id, temporary1.source, temporary1.destination, temporary1.amount, 'MATCHED' FROM temporary1
INTERSECT
SELECT temporary2.id, temporary2.source, temporary2.destination, temporary2.amount, 'MATCHED'
FROM temporary2;

赞(0）回复(0）举报 2021-07-24

我来回答

如何显示匹配和不匹配sql？

2条答案

相关问题

热门标签

最新问答