Início / dba / Perguntas / 341093
Accepted
TheLovelySausage
Asked: 2024-07-23 00:13:24 +0800 CST

Classificar por tabela filha, mas manter a ordem da tabela pai

  • 772

Atualmente, estou tentando classificar os resultados de duas tabelas de forma que o campo de classificação primário seja um na tabela filho, mas gostaria de evitar que os resultados da tabela pai fossem movidos fora da sequência.

Um exemplo seriam as seguintes tabelas (usando o Ms SQL Server):

CREATE TABLE tier_a(
   idno INTEGER NOT NULL,
   name VARCHAR(10) NOT NULL
);

CREATE TABLE tier_b(
   idno INTEGER NOT NULL,
   tier_a_idno INTEGER NOT NULL,
   name VARCHAR(10) NOT NULL
);

INSERT INTO tier_a VALUES(3, 'C');
INSERT INTO tier_a VALUES(2, 'B');
INSERT INTO tier_a VALUES(1, 'A');

INSERT INTO tier_b VALUES(1, 1, 'A');
INSERT INTO tier_b VALUES(4, 2, 'C');
INSERT INTO tier_b VALUES(2, 1, 'B');
INSERT INTO tier_b VALUES(5, 3, 'A');
INSERT INTO tier_b VALUES(3, 1, 'C');

Se eu classificar apenas pela tabela filho:

SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY tier_b_name ASC

Eu consigo o que espero:

Ascending by child

tier_a_name | tier_b_name
C           | A
A           | A
A           | B
A           | C
B           | C

Descending by child

tier_a_name | tier_b_name
B           | C
A           | C
A           | B
C           | A
A           | A

Mas o que estou tentando alcançar é manter a ordem da tabela pai:

Ascending by child

tier_a_name | tier_b_name
A           | A
A           | B
A           | C
C           | A
B           | C

Descending by child

tier_a_name | tier_b_name
A           | C
A           | B
A           | A
B           | C
C           | A

Posso simular isso com um manual terrível ORDER BY:

Ascending by child

SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY
   CASE
      -- Parent of the Lowest tier_b name (A)
      WHEN tier_a.idno = 1 THEN 1
      -- Parent of the Lowest tier_b name (A) but Higher tier_a idno
      WHEN tier_a.idno = 3 THEN 2
      -- Parent of the Highest tier_b name (C)
      WHEN tier_a.idno = 2 THEN 3
   END,
   tier_b_name ASC

Descending by child

SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY
   CASE
      -- Parent of the Highest tier_b name (C)
      WHEN tier_a.idno = 1 THEN 1
      -- Parent of the Highest tier_b name (C) but Higher tier_a idno
      WHEN tier_a.idno = 2 THEN 2
      -- Parent of the Lowestest tier_b name (A)
      WHEN tier_a.idno = 3 THEN 3
   END,
   tier_b_name DESC
sql-server

2 respostas

  1. Best Answer

    Então, você quer ORDER BY (tier A in order of the largest/smallest tier B), (tier B)?

    Para subir, seria:

    SELECT 
  tier_a.name AS tier_a_name, 
  tier_b.name AS tier_b_name,
  /*SMALLEST tier_b for each tier_a*/
  tier_a_ordinal = MIN(tier_b.name) OVER (PARTITION BY tier_a.name),
  /*Order of the tier_b value in the tier_a block*/
  tier_b_ordinal = ROW_NUMBER() OVER (PARTITION BY tier_a.name ORDER BY tier_b.name)
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY
  /*First order tier_a by the largest tier_b they have in.*/
  tier_a_ordinal ASC,
  /*If multiple tier_a's have the same largest tier_b, order then _somehow_.*/
  tier_a_name ASC,   
  /*Inside the tier_a's, order them by the tier_b*/
  tier_b_ordinal ASC;

    Para descer:

    SELECT 
  tier_a.name AS tier_a_name, 
  tier_b.name AS tier_b_name,
  /*LARGEST tier_b for each tier_a*/
  tier_a_ordinal = MAX(tier_b.name) OVER (PARTITION BY tier_a.name),
  tier_b_ordinal = ROW_NUMBER() OVER (PARTITION BY tier_a.name ORDER BY tier_b.name)
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY
  /*First, order by tier_a's by the largest tier_b they have*/
  tier_a_ordinal DESC,  
  /*If multiple have the same largest tier_b, order them _somehow_ */
  tier_a_name DESC,  
  /*Last, order the tier_b's in that tier_a */
  tier_b_ordinal DESC;

    Violino

    • 4

  2. É isso que você está procurando?

    Ascending by child

    SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY tier_a.name, tier_b_name ASC

    Descending by child

    SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY tier_a.name, tier_b_name DESC
    • -2

relate perguntas