我有一张地址表
CREATE TABLE `asset_location_rel` (
`a_id` int(11) NOT NULL,
`asset_id` int(11) NOT NULL,
`location_id` int(11) NOT NULL,
`deleted` int(11) NOT NULL DEFAULT 0,
`units` int(11) NOT NULL DEFAULT 1,
`unit_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `locations` (`location_id`, `location_row`, `location_column`, `location_name`, `location_row_col`, `location_created_datetime`, `location_created_by_userid`, `location_deleted_datetime`, `location_deleted_by_userid`, `location_comment`) VALUES
(557, 'K', '41', 'F3', '', '2022-11-09 14:43:43', 1, '0000-00-00 00:00:00', 0, '');
COMMIT;
其中多个资产可以位于同一位置
INSERT INTO `asset_location_rel` (`a_id`, `asset_id`, `location_id`, `deleted`, `units`, `unit_type`) VALUES
(1643, 2042, 31, 0, 1, 0),
(1645, 34, 0, 0, 0, 0),
(1646, 34, 58, 0, 1, 0),
(1657, 519, 557, 0, 1, 0),
(1658, 477, 557, 0, 2, 0);
COMMIT;
现在,列出我想按location_id分组的位置,这样就不会显示与该位置的所有关系。但我还想总结该位置上的项目数量。
使用位置557进行测试
怎么做?
尝试
SELECT
locations.*,
(SELECT asset_location_rel.units
FROM asset_location_rel
WHERE
asset_location_rel.location_id = locations.location_id)
AS total_asset_units
FROM
locations
LEFT JOIN asset_location_rel ON asset_location_rel.location_id = locations.location_id
WHERE
locations.location_deleted_datetime = '0000-00-00 00:00:00'
GROUP BY
locations.location_id
ORDER BY
locations.location_id
DESC
但运气不佳#1242 -子查询返回1行以上
编辑:预期结果:
ID Lokation Row/Column NumItems
557 F3 K/41 3
2条答案
按热度按时间bjg7j2ky1#
编辑编辑:解决方案(至少看起来是这样):
看起来在子选择中使用带有Groupby的SUM就可以了;)
xe55xuns2#
这是使用
inner join
的另一种方法,如下所示:子查询用于获取location的totalUnits,然后通过连接locations表来获取location行