包含在SQL查询中的空值
问题描述:
,我有以下四个表在我的数据库:包含在SQL查询中的空值
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| complex_id | int(11) | NO | PRI | NULL | auto_increment |
| complex_name | varchar(45) | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| machine_id | int(11) | NO | PRI | NULL | auto_increment |
| complex_id | int(11) | NO | MUL | NULL | |
| machine_name | varchar(45) | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| devices_id | int(11) | NO | PRI | NULL | auto_increment |
| machine_id | int(11) | NO | MUL | NULL | |
| description | varchar(255) | NO | | NULL | |
| location | varchar(255) | YES | | NULL | |
| verification | varchar(255) | YES | | NULL | |
| rack_num | varchar(8) | YES | | NULL | |
| section_num | varchar(8) | YES | | NULL | |
| color_or_number | varchar(16) | YES | | NULL | |
| normal_position | varchar(16) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| pnp_id | int(11) | NO | PRI | NULL | auto_increment |
| devices_id | int(11) | NO | MUL | NULL | |
| pnp_num | varchar(45) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
我试图让格式如下结果(NULL值显示为空白):
+------------+-------------+-----------------------+
| devices_id | description | pnpnum |
+------------+-------------+-----------------------+
| 1 | ex | 1234 |
| 2 | ex2 | 2345 |
| 3 | ex3 | |
| 4 | ex4 | 3456, 4567, 5678, 6879|
+------------+-------------+-----------------------+
使用以下SQL查询,
SELECT *, GROUP_CONCAT(pnp.pnp_num separator ', ') pnpnum
FROM devices
JOIN pnp ON devices.devices_id = pnp.devices_id
WHERE devices.machine_id = 1
GROUP BY devices.devices_ID
ORDER BY devices.description;
我的结果比较接近,但是,我无法包含设备if它有一个空pnpnum。
+------------+-------------+-----------------------+
| devices_id | description | pnpnum |
+------------+-------------+-----------------------+
| 1 | ex | 1234 |
| 2 | ex2 | 2345 |
| 4 | ex4 | 3456, 4567, 5678, 6879|
+------------+-------------+-----------------------+
什么是我从我的SQL语句,将允许我包含空值缺少?
您的表格是否设置为默认值NULL?像:'pnpnum' varchar(25)DEFAULT NULL –
我相信你应该使用'LEFT JOIN'而不是普通的'JOIN' – Sumurai8