将菜单和子菜单导入数据库

Question

我下面有个问题

SELECT DISTINCT a.mkey, 0 child_menu_mkey, UPPER(a.menu_name) parent_menu, '' child_menu 
FROM wms_menu_hdr a,
     wms_menu_hdr b 
WHERE a.mkey = b.parent_mkey 
UNION 
SELECT DISTINCT b.mkey, b.mkey child_menu_mkey, '' parent_menu, b.menu_name child_menu 
FROM wms_menu_hdr a,
     wms_menu_hdr b 
WHERE a.mkey = b.parent_mkey 
ORDER BY 1,2 

将我的结果显示为

我正在使用SQL-server-2005

Answer 1

我以前的回答是另一个问题，但我们澄清了这个问题，所以试试这个：

SELECT parent_key, child_key, parent_menu, child_menu
FROM
(SELECT DISTINCT a.mkey AS mkey, 0 child_menu_mkey, UPPER(a.menu_name) parent_menu, '' child_menu, mkey AS parent_key 
        FROM wms_menu_hdr a
        WHERE a.mkey IN (SELECT parent_mkey FROM wms_menu_hdr)
        UNION ALL
        SELECT DISTINCT b.mkey, b.mkey child_menu_mkey, '' parent_menu, b.menu_name child_menu, a.mkey AS parent_key
        FROM wms_menu_hdr a,
             wms_menu_hdr b 
        WHERE a.mkey = b.parent_mkey)
ORDER BY parent_key, child_menu_key;

Answer 2

在没有窗口函数的Server 2005中，需要某种子查询，例如：

WITH t AS
(
        SELECT DISTINCT a.mkey, 0 child_menu_mkey, UPPER(a.menu_name) parent_menu, '' child_menu 
        FROM wms_menu_hdr a,
             wms_menu_hdr b 
        WHERE a.mkey = b.parent_mkey 
        UNION 
        SELECT DISTINCT b.mkey, b.mkey child_menu_mkey, '' parent_menu, b.menu_name child_menu 
        FROM wms_menu_hdr a,
             wms_menu_hdr b 
        WHERE a.mkey = b.parent_mkey) 
SELECT t.mkey, t.child_menu_mkey, t.parent_menu, t.child_menu
FROM t JOIN (SELECT child_menu_mkey, min(mkey) AS min_mkey 
         FROM t 
         GROUP BY child_menu_mkey) AS g 
        ON t.child_menu_mkey = g.child_menu_mkey AND t.mkey = g.min_mkey;