mysql分组时的子选择

wgx48brx  于 2023-03-07  发布在  Mysql
关注(0)|答案(2)|浏览(121)

我有一张地址表

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
bjg7j2ky

bjg7j2ky1#

编辑编辑:解决方案(至少看起来是这样):

SELECT
  locations.*,
  (SELECT SUM(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

看起来在子选择中使用带有Groupby的SUM就可以了;)

xe55xuns

xe55xuns2#

这是使用inner join的另一种方法,如下所示:
子查询用于获取location的totalUnits,然后通过连接locations表来获取location行

select l.*, s.totalUnits
from locations l
inner join (
  SELECT l.location_id, sum(alr.units) as totalUnits
  from locations l
  INNER JOIN asset_location_rel alr ON alr.location_id = l.location_id
  WHERE l.location_deleted_datetime = '0000-00-00 00:00:00'
  GROUP BY l.location_id
) as s on s.location_id = l.location_id

相关问题