时间戳

首页 > 解决方案 > 不同值时的 SQL 选择

php - 不同值时的 SQL 选择

问题描述

我有两个表，我想从 table_2 中选择具有不同值的行，从 table_1 中的行使用来显示一个带有注册投诉但没有雇佣编号的表，我已经尝试了这个 sql 语句：

SELECT * FROM krita_db, sjofor_db WHERE employing_nr != nr ORDER BY id DESC

但如果

+----+--------------+-------------------------------------+
| id | employing_nr |              complaint              |
+----+--------------+-------------------------------------+
|  1 |          123 | something bad                       |
|  2 |          333 | you have to do something with this  |
+----+--------------+-------------------------------------+

+----+-----+------+---------+----------+
| id | nr  | navn | adresse |   tlf    |
+----+-----+------+---------+----------+
|  1 | 123 | ola  | ---     | 12345678 |
|  2 | 321 | kari | ---     | 98765432 |
|  3 | 222 | gerd | ---     | 12344321 |
+----+-----+------+---------+----------+

我只想显示一个相同的投诉而不是 3 次，我该如何做到这一点？

现在使用我的代码，我得到了这张表：

+----+--------------+--------------------------------------+
| id | employing_nr |              complaint               |
+----+--------------+--------------------------------------+
|  1 |          123 | something bad                        |
|  1 |          123 | something bad                        |
|  2 |          333 | you have to do something with this   |
|  2 |          333 | you have to do something with this   |
|  2 |          333 | you have to do something with this   |
+----+--------------+--------------------------------------+

我想显示这个，即未注册的employing_nr 投诉：

+----+--------------+--------------------------------------+
| id | employing_nr |              complaint               |
+----+--------------+--------------------------------------+
|  2 |          333 | you have to do something with this   |
+----+--------------+--------------------------------------+

标签： phpsql

解决方案

尽量避免查询中的隐式连接。

您可以尝试如下EXIST运算符

SELECT  * 
FROM krita_db 
WHERE NOT EXISTS (
  SELECT 1 FROM sjofor_db
  WHERE krita_db.employing_nr = sjofor_db.nr)

小提琴

此外，您可以使用 Hoàng Đăng 的答案 ( LEFT JOIN+ NULLcheck)获得相同的结果

推荐阅读