SQL存储及查询无限级分类的数据库设计

admin

select t1.name as lev1,t2.name as lev2,t3.name as lev3
from goods as t1
left join goods as t2 on t2.parent_id=t1.ID
left join goods as t3 on t3.parent_id=t2.ID
where t3.ID=16

admin

USE [lj_tc]
GO

/****** Object:  Table [dbo].[goods]    Script Date: 12/12/2023 14:48:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[goods](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [name] [varchar](50) NOT NULL,
        [parent_id] [int] NOT NULL,
CONSTRAINT [PK_directory] PRIMARY KEY CLUSTERED
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

admin

select a.id,a.name,b.name,c.name,d.name from
(select id,name,parent_id from goods as t1 where parent_id = 0) a left JOIN
(select id,name,parent_id from goods as t1 where qx = 1  ) b on b.parent_id = a.id  left JOIN
(select id,name,parent_id from goods as t1 where qx = 2  ) c on c.parent_id = a.id  left JOIN
(select id,name,parent_id from goods as t1 where qx = 3  ) d on d.parent_id = a.id

admin