在 SQL Server 中创建层次结构

问题描述

限时删除！！

我有以下格式的数据.

表一

e_id   e_name  e_type
-------------------------
1      CBC     2
2      ABC     3
3      N2      1
4      CBC1    3
5      ABC1    3
6      N1      1

表 2

N_ID   N_Name
---------------
3      N2
6      N1

表 3

N_ID  E_ID
------------
3     1
3     2
3     3
6     4
6     5
6     6

我想建立一个层次结构如下.

And I want to build a hierarchy as follows.

e_id    e_name     e_type   n_id
------------------------------------
6       N1           1        6
4        - ABC1      3        6
5        - CBC1      3        6
3       N2           1        3
4        - ABC       3        3
5        - CBC       2        3

按节点排序(升序)，子节点(升序).

With Order by Nodes (ascending), child nodes (ascending).

我尝试做类似的事情，

SELECT u.e_id,
CASE WHEN e_TYPE_ID = 1 THEN  u.e_name ELSE ' - ' + u.e_name END e_name,
e_TYPE_ID, su.n_ID
FROM table1 u
INNER JOIN table3 su on u.e_id = su.e_id
WHERE EXISTS (SELECT N_ID FROM table2 WHERE N_ID = CASE WHEN u.e_TYPE_ID = 1 THEN u.e_id ELSE n_ID END)
ORDER BY  e_TYPE_ID, u.e_name,n_id

但是我无法获得正确的订单，有没有更好的方法来做到这一点?

But I am not able to get the correct order, is there a better way to do this?

推荐答案

使用:

WITH summary AS (
  SELECT a.e_id,
         a.e_name,
         a.e_type,
         b.n_id,
         CASE WHEN e_type = 1 THEN e_type ELSE 2 END AS rank
    FROM TABLE1 a
    JOIN TABLE3 b ON b.e_id = a.e_id)
   SELECT s.e_id,
          CASE
            WHEN s.rank > 1 THEN ' - '+ s.e_name
            ELSE s.e_name
          END AS e_name,
          s.e_type,
          s.n_id
     FROM summary s
 ORDER BY s.n_id DESC, s.rank, s.e_name

我测试使用:

WITH table2 AS (
   SELECT 3 AS n_id, 'N2' AS n_name
   UNION ALL
   SELECT 6, 'N1'),
     table1 AS (
   SELECT 1 AS e_id, 'CBC' AS e_name, 2 AS e_type
   UNION ALL
   SELECT 2, 'ABC', 3
   UNION ALL
   SELECT 3, 'N2', 1
   UNION ALL
   SELECT 4, 'CBC1', 3
   UNION ALL
   SELECT 5, 'ABC1', 3
   UNION ALL
   SELECT 6, 'N1', 1),
     table3 AS (
   SELECT 3 AS n_id, 1 AS e_id
   UNION ALL
   SELECT 3, 2
   UNION ALL
   SELECT 3, 3
   UNION ALL
   SELECT 6, 4
   UNION ALL
   SELECT 6, 5
   UNION ALL
   SELECT 6, 6),
     summary AS (
   SELECT a.e_id,
          a.e_name,
          a.e_type,
          b.n_id,
          CASE WHEN e_type = 1 THEN e_type ELSE 2 END AS rank
     FROM TABLE1 a
     JOIN TABLE3 b ON b.e_id = a.e_id)
  SELECT s.e_id,
         CASE
            WHEN s.rank > 1 THEN ' - '+ s.e_name
            ELSE s.e_name
         END AS e_name,
         s.e_type,
         s.n_id
    FROM summary s
ORDER BY s.n_id DESC, s.rank, s.e_name

这篇关于在 SQL Server 中创建层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..