sql - 对值进行分组以便按值的最小值排序，然后按该值本身对其他字段进行排序

Question

我想按聚合值对我的数据进行排序，然后按其他字段排序，然后按未聚合值排序。

我有以下架构:

CREATE TABLE priority_t (
  id          NUMERIC(10),
  priority    NUMERIC(10),
  CONSTRAINT priority_t_pk PRIMARY KEY (id)
);

CREATE TABLE value_t (
  id          NUMERIC(10),
  value       NUMERIC(10),
  CONSTRAINT value_t_pk PRIMARY KEY (id)
);

CREATE TABLE file_t (
  id          NUMERIC(10),
  CONSTRAINT file_t_pk PRIMARY KEY (id)
);

CREATE TABLE main_t (
  id          NUMERIC(10),
  priority_id NUMERIC(10),
  value_id    NUMERIC(10),
  file_id     NUMERIC(10),
  CONSTRAINT main_t_pk PRIMARY KEY (id),
  CONSTRAINT priority_t_fk FOREIGN KEY (priority_id) REFERENCES priority_t(id),
  CONSTRAINT value_t_fk FOREIGN KEY (value_id) REFERENCES value_t(id),
  CONSTRAINT file_t_fk FOREIGN KEY (file_id) REFERENCES file_t(id)
);

然后我插入以下数据:

INSERT INTO priority_t (id, priority) VALUES (1, 10);
INSERT INTO priority_t (id, priority) VALUES (2, 20);

INSERT INTO value_t (id, value)       VALUES (1, 987);
INSERT INTO value_t (id, value)       VALUES (2, 876);
INSERT INTO value_t (id, value)       VALUES (3, 765);
INSERT INTO value_t (id, value)       VALUES (4, 654);

INSERT INTO file_t (id)               VALUES (111);
INSERT INTO file_t (id)               VALUES (222);
INSERT INTO file_t (id)               VALUES (333);
INSERT INTO file_t (id)               VALUES (444);

INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (1, 1, 1, 111);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (2, 2, 1, 111);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (3, 2, 2, 222);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (4, 1, 2, 333);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (5, 2, 3, 111);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (6, 1, 4, 444);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (7, 2, 4, 444);

COMMIT;

我想得到以下结果:

 min_priority | priority | value | value_id | file_id
   (hidden)   |          |       | (hidden) |        
--------------+----------+-------+----------+---------
           10 |       10 |   654 |        4 |     444 
           10 |       20 |   654 |        4 |     444
           10 |       10 |   876 |        2 |     333
           10 |       10 |   987 |        1 |     111
           10 |       20 |   987 |        1 |     111
           20 |       20 |   765 |        3 |     111
           20 |       20 |   876 |        2 |     222

我知道如何对它们进行排序:

ORDER BY min_value ASC, value ASC, value_id ASC, priority ASC

但我的问题是我不知道如何对值本身进行分组:我的行中不断出现重复值和/或不正确的值。

我最接近的尝试如下:

WITH listing AS (
  SELECT m.id             AS main_id,
         p.id             AS priority_id,
         p.priority       AS priority,
         v.id             AS value_id,
         v.value          AS value,
         f.id             AS file_id
    FROM main_t m
           INNER JOIN priority_t p ON m.priority_id = p.id
           INNER JOIN value_t v    ON m.value_id = v.id
           INNER JOIN file_t f     ON m.file_id = f.id
)
SELECT min_p.min_priority AS min_priority,
       listing.priority   AS priority,
       listing.value      AS value,
       listing.file_id    AS file_id
  FROM listing,
       (
         SELECT min(min_p_value.min_priority) AS min_priority,
                min_p_value.value_id          AS min_value_id,
                listing.file_id               AS file_id
           FROM listing,
                (
                  SELECT min(listing.priority) AS min_priority,
                         listing.value         AS value,
                         listing.value_id      AS value_id
                    FROM listing
                   GROUP BY listing.value, listing.value_id
                ) min_p_value
          WHERE listing.value = min_p_value.value
            AND listing.value_id = min_p_value.value_id
            AND min_p_value.min_priority = min_priority
          GROUP BY min_p_value.value_id, listing.file_id
       ) min_p
 WHERE min_p.min_value_id = listing.value_id
   AND min_p.file_id = listing.file_id
 ORDER BY min_p.min_priority ASC,
          listing.value ASC,
          listing.value_id ASC,
          listing.priority;

这会返回以下不正确的结果:

 MIN_PRIORITY   PRIORITY      VALUE    FILE_ID
------------- ---------- ---------- ----------
           10         10        654        444
           10         20        654        444
           10         10        876        333
           10         20        876        222 <-- incorrect, should have a min_priority of 20, and therefore be the last
           10         10        987        111
           10         20        987        111
           20         20        765        111

我怎样才能达到我的期望？

Answer 1

这应该有效:

select (select min(priority)
          from main_t mm
          join priority_t tt
            on tt.id = mm.priority_id
         where mm.value_id = m.value_id) as min_priority,
       p.priority as priority,
       v.value as value,
       m.value_id,
       m.file_id
  from main_t m
  join priority_t p
    on p.id = m.priority_id
  join value_t v
    on v.id = m.value_id
 order by 1, 3, 4, 2;

它通过value_id来决定最小的优先级。您可以按列号对结果进行排序，如图所示。