SELECT F.ID,
F.PARENTID,
LPAD(' ', 5 * (LEVEL - 1)) || F.PRODUCTNAME PRODUCTNAME,
LTRIM(SYS_CONNECT_BY_PATH(F.ID, '->'), '->') ORDERID
FROM F0307 F
START WITH F.PARENTID IS NULL
CONNECT BY PRIOR F.ID = F.PARENTID;
----ORACLE递归
WITH CTE(CID,
CPID,
CNAME,
ORDERID,
LV) AS
(SELECT T.ID, T.PARENTID, T.PRODUCTNAME, TO_CHAR(T.ID) AS ORDERID, 1
FROM F0307 T
WHERE T.PARENTID IS NULL
UNION ALL
SELECT B.ID,
B.PARENTID,
LPAD(' ', 5 * A.LV) || B.PRODUCTNAME,
A.ORDERID || '->' || B.ID ORDERID,
LV + 1 LV
FROM CTE A
JOIN F0307 B
ON A.CID = B.PARENTID)
SELECT * FROM CTE ORDER BY ORDERID;
-- mysql
select 品名,规格,颜色,sum(数量) 数量,group_concat(id) idsfrom F0304GROUP BY 品名,规格,颜色
----ORACLE
SELECT F.品名,F.规格,F.颜色,SUM(F.数量) 数量,LISTAGG(F.ID,',') WITHIN GROUP(ORDER BY F.ID) IDS FROM F0304 F GROUP BY F.品名,F.规格,F.颜色;
或者
select a.品名,a.规格,sum(a.数量) 数量,wm_concat(a.id) id
from f0304 a
group by a.品名,a.规格;