sql递归求和 treeview树形递归_sql树形结构求和-优快云博客

本文链接：https://blog.youkuaiyun.com/lcyeliang/article/details/2063034

本文介绍了一种使用TreeView控件结合SQL递归查询的方法来展示层级数据的技术方案。通过具体的C#代码示例，详细解释了如何从数据库中获取数据并构建树形结构，同时展示了SQL递归存储过程的设计思路。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

treeview递归部分

public void AddTree(int ParentID, TreeNode pNode)
        {
            DataView dvTree = new DataView(ds2.Tables["stkd_jj_gx"]);
            dvTree.RowFilter = "[fparentid]   =   " + ParentID;
            foreach (DataRowView Row in dvTree)
            {
                TreeNode Node = new TreeNode();
                if (pNode == null)
                {
                    if (Row["fname"].ToString().Trim() != "工序")
                    {
                        Node.Text = Row["fname"].ToString() + "(" + Row["fnumber"] + ")";
                    }
                    else
                    {
                        Node.Text = Row["fname"].ToString().Trim();
                    }
                    treeView1.Nodes.Add(Node);
                    AddTree(Int32.Parse(Row["fid"].ToString()), Node);
                }
                else
                {
                    Node.Text = Row["fname"].ToString() + "(" + Row["fnumber"] + ")";
                    pNode.Nodes.Add(Node);
                    AddTree(Int32.Parse(Row["fid"].ToString()), Node);
                }
            }
        }

sql递归部分

建表

CREATE TABLE [dbo].[STKD_Jj_Gx] (
[FID] [int] IDENTITY (1, 1) NOT NULL ,
[FDetail] [bit] NULL ,
[FParentID] [int] NULL ,
[FNumber] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[FName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[FCountPrice] [decimal](18, 2) NULL ,
[FUnitName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[FItemID] [int] NULL ,
[FExplation] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[FGxKinds] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[FItemNumber] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[FItemName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[STKD_Jj_Gx] ADD
CONSTRAINT [DF_STKD_Jj_Gx_FCountPrice] DEFAULT (0) FOR [FCountPrice],
CONSTRAINT [PK_STKD_Jj_Gx] PRIMARY KEY CLUSTERED
(
[FID]
) ON [PRIMARY]
GO

建立存储过程

create   proc   STKD_Jj_DG
as
update   a   set
FCountPrice=case
when   exists(select   *   from   STKD_Jj_Gx   where   FParentID=a.FID)   then   null
else   isnull(FCountPrice,0)   end
from   STKD_Jj_Gx   a

while   @@rowcount>0
update   a   set   FCountPrice=b.FCountPrice
from   STKD_Jj_Gx   a,
(select   FParentID,FCountPrice=sum(FCountPrice)
from   STKD_Jj_Gx a
group   by   FParentID
having   sum(case   when   FCountPrice   is   null   then   1   else   0   end)=0) b
where   a.FID=b.FParentID
and(a.FCountPrice   is   null)
go