mysql - 多次按同一分组列排序

Question

我必须通过许多标签来订购一些商品，提取不同的商品 ID，数据是这样的:

示例数据

Item:
 id    name    creation
 1     item1    2017-06-26 16:48:54
 2     item2    2017-06-23 16:48:54
 3     item3    2017-06-29 16:49:42
 4     item4    2017-06-15 16:50:31
 5     item5    2017-06-09 16:50:44
 6     item6    2017-06-12 16:50:57

 ItemTag:
 id    priority
 1     10
 2     999
 3     15
 4     8

ItemHasItemTag:
itemId   itemTagId
1        1
1        2
2        1
2        3
3        4
4        1
4        4
5        1
5        2
5        3
6        2

我想提取按 ItemTag 优先级 ASC 排序的 Item id，使用类似于此错误的查询:

SELECT i.id,
  group_concat(it.priority order by priority asc) as tagPriorities
FROM Item i
  JOIN ItemHasItemTag ihit on i.id = ihit.itemId
  JOIN ItemTag it on it.id = ihit.tagId
GROUP BY i.id
ORDER BY it.priority asc;

结果:

itemId   (tagPriorities)
3        8
1        10,999
2        10,15
4        8,10
5        10,15,999
6        999

我想要的结果应该是这样的:

itemId    (tagPriorities)
3         8
4         8,10
2         10,15
5         10,15,999
1         10,999
6         999

当然，由于group by 在 order by 之前执行，它打乱了顺序

有人可以帮助我吗？

我附上创建模式:

CREATE TABLE IF NOT EXISTS `Item` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
 CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ItemTag` (
  `id` int(11) NOT NULL,
  `priority` int(10) unsigned NOT NULL DEFAULT '10',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ItemHasItemTag` (
  `itemId` int(10) unsigned NOT NULL,
  `tagId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`itemId`,`tagId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Item` (`id`, `name`, `creation`) VALUES
  (1, 'item1', '2017-06-26 16:48:54'),(2, 'item2', '2017-06-23 16:48:54'),(3, 'item3', '2017-06-29 16:49:42'),(4, 'item4', '2017-06-15 16:50:31'),(5, 'item5', '2017-06-09 16:50:44'),(6, 'item6', '2017-06-12 16:50:57');

INSERT INTO `ItemTag` (`id`, `priority`) VALUES
  (1, 10),(2, 999),(3, 15),(4, 8);

INSERT INTO `ItemHasItemTag` (`itemId`, `tagId`) VALUES
  (1, 1),(1, 2),(2, 1),(2, 3),(3, 4),(4, 1),(4, 4),(5, 1),(5, 2),(5, 3),(6, 2);

Answer 1

这是否能满足您的需求？

select
    itempri.itemid
from (
    select
        ihit.itemid,
        min(it.priority) as pri
    from
        itemhasitemtag as ihit
        inner join itemtag as it on it.id=ihit.itemid
    group by
        ihit.itemid
    ) as itempri
order by
    pri;

编辑:

要按所有优先级升序排列，您可能需要这样的东西:

select
    ihit.itemid,
    group_concat(lpad(cast(it.priority as character(8), 8, '0')), order by it.priority separator ', ') as pristr
from
    itemhasitemtag as ihit
    inner join itemtag as it on it.id=ihit.itemid
group by
    ihit.itemid
order by
    pristr;

(未经测试)