postgreSQL Group By按组忽略共享字段

Question

我有一些产品变体，我想作为一个组获得，并忽略为返回的一个聚合记录共享的任何字段。目前尚不清楚各种变体将共享多少或哪些字段。这是的示例表

CREATE TABLE IF NOT EXISTS `products` (
`id` int(6) unsigned NOT NULL,
`name` varchar(200),
`variantId` int(3),
`size` varchar(200),
`color` varchar(200),
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `products` (`id`, `name`, `variantId`, `size`, `color`) VALUES
('1', 'SoccerBalls', '1', '3', 'Black-white'),
('2', 'SoccerBalls', '1', '4', 'Black-white'),
('3', 'SoccerBalls', '1', '5', 'Black-white'),
('4', 'RefereeCard', '2', null, 'yellow'),
('5', 'RefereeCard', '2', null', 'red'),
('6', 'Jersey', '3', 'L', 'Tottenham'),
('7', 'Jersey', '3', 'M', 'Chelsea'),
('8', 'Jersey', '3', 'S', 'Arsenal');

作为一个输出，我想要的是这样的东西：

null, SoccerBalls, null, null, Black-white
null, RefereeCard, null, null, null,
null, Jersey, null, null, null

Answer 1

我想你想要：

select (case when min(id) = max(id) then min(id) end) as id,
name, variantid,
(case when min(size) = max(size) then min(size) end) as size,
(case when min(color) = max(color) then min(color) end) as color
from products
group by variantid, name;

如果值可以是NULL，那么您需要一个附加条件——如果您想将NULL视为另一个值：

select (case when min(id) = max(id) then min(id) end) as id,
name, variantid,
(case when min(size) = max(size) and count(size) = count(*)
then min(size)
end) as size,
(case when min(color) = max(color) and count(color) = count(*)
then min(color)
end) as color
from products
group by variantid, name;

显然，您不需要对id进行此检查，因为它是主键。