[条件描述]
表结构:
Id ParentId Rank StartDate
1 0 0 2006-5-1
2 1 1 2006-5-2
3 1 1 2006-5-3
4 2 2 2006-5-4
5 4 3 2006-5-5
6 3 2 2006-5-4其中Id为编号,ParentId为Id的父节点,Rank为级别,StartDate为开始日期[问题描述]
根据查询条件StartDate(例如StartDate>'2006-5-2')获得的记录,需要能根据Tree的结构显示出来,如果中间出现断层的情况,则需要补充进去(即子节点符合条件,父节点不符合,父节点也要能显示)。
eg1.
StartDate>'2006-5-2'
期待结果为:
Id ParentId Rank StartDate
1 0 0 2006-5-1
2 1 1 2006-5-2
4 2 2 2006-5-4
5 4 3 2006-5-5
3 1 1 2006-5-3
6 3 2 2006-5-4
eg2.
StartDate>'2006-5-4'
期待结果为:
Id ParentId Rank StartDate
1 0 0 2006-5-1
2 1 1 2006-5-2
4 2 2 2006-5-4
5 4 3 2006-5-5
eg3.
StartDate='2006-5-4'
期待结果为:
Id ParentId Rank StartDate
1 0 0 2006-5-1
2 1 1 2006-5-2
4 2 2 2006-5-4
3 1 1 2006-5-3
6 3 2 2006-5-4在线Waiting...
表结构:
Id ParentId Rank StartDate
1 0 0 2006-5-1
2 1 1 2006-5-2
3 1 1 2006-5-3
4 2 2 2006-5-4
5 4 3 2006-5-5
6 3 2 2006-5-4其中Id为编号,ParentId为Id的父节点,Rank为级别,StartDate为开始日期[问题描述]
根据查询条件StartDate(例如StartDate>'2006-5-2')获得的记录,需要能根据Tree的结构显示出来,如果中间出现断层的情况,则需要补充进去(即子节点符合条件,父节点不符合,父节点也要能显示)。
eg1.
StartDate>'2006-5-2'
期待结果为:
Id ParentId Rank StartDate
1 0 0 2006-5-1
2 1 1 2006-5-2
4 2 2 2006-5-4
5 4 3 2006-5-5
3 1 1 2006-5-3
6 3 2 2006-5-4
eg2.
StartDate>'2006-5-4'
期待结果为:
Id ParentId Rank StartDate
1 0 0 2006-5-1
2 1 1 2006-5-2
4 2 2 2006-5-4
5 4 3 2006-5-5
eg3.
StartDate='2006-5-4'
期待结果为:
Id ParentId Rank StartDate
1 0 0 2006-5-1
2 1 1 2006-5-2
4 2 2 2006-5-4
3 1 1 2006-5-3
6 3 2 2006-5-4在线Waiting...
eg1.
StartDate>'2006-5-2'
期待结果为:
Id ParentId Rank StartDate Tag
1 0 0 2006-5-1 0
2 1 1 2006-5-2 0
4 2 2 2006-5-4 1
5 4 3 2006-5-5 1
3 1 1 2006-5-3 1
6 3 2 2006-5-4 1
eg2.
StartDate>'2006-5-4'
期待结果为:
Id ParentId Rank StartDate
1 0 0 2006-5-1 0
2 1 1 2006-5-2 0
4 2 2 2006-5-4 0
5 4 3 2006-5-5 1
eg3.
StartDate='2006-5-4'
期待结果为:
Id ParentId Rank StartDate
1 0 0 2006-5-1 0
2 1 1 2006-5-2 0
4 2 2 2006-5-4 1
3 1 1 2006-5-3 0
6 3 2 2006-5-4 1
INSERT INTO @T SELECT 1,0,0,'2006-5-1'
UNION ALL SELECT 2,1,1,'2006-5-2'
UNION ALL SELECT 3,1,1,'2006-5-3'
UNION ALL SELECT 4,2,2,'2006-5-4'
UNION ALL SELECT 5,4,3,'2006-5-5'
UNION ALL SELECT 6,3,2,'2006-5-4';
DECLARE @TIME DATETIME
SET @TIME='2006-5-4';
WITH CTE_T(Id,ParentId,Rank,StartDate)
AS
(
SELECT Id,ParentId,Rank,StartDate
FROM @T
WHERE StartDate=@TIME
UNION ALL
SELECT A.Id,A.ParentId,A.Rank,A.StartDate
FROM @T A, CTE_T B
WHERE A.ID=B.ParentId
)
SELECT DISTINCT *,Tag=CASE WHEN StartDate>=@TIME THEN 1 ELSE 0 END
FROM CTE_T/*
Id ParentId Rank StartDate Tag
----------- ----------- ----------- ----------------------- -----------
1 0 0 2006-05-01 00:00:00.000 0
2 1 1 2006-05-02 00:00:00.000 0
3 1 1 2006-05-03 00:00:00.000 0
4 2 2 2006-05-04 00:00:00.000 1
6 3 2 2006-05-04 00:00:00.000 1(5 行受影响)*/
INSERT INTO A SELECT 1,0,0,'2006-5-1'
UNION ALL SELECT 2,1,1,'2006-5-2'
UNION ALL SELECT 3,1,1,'2006-5-3'
UNION ALL SELECT 4,2,2,'2006-5-4'
UNION ALL SELECT 5,4,3,'2006-5-5'
UNION ALL SELECT 6,3,2,'2006-5-4';create proc TT(@StartDate datetime)
as
declare @lev int
DECLARE @T TABLE(ID INT,ParentId INT,Rank INT,StartDate DATETIME,lev int,Flag bit)
set @lev=1
insert @T select ID,ParentId,Rank,StartDate, @lev as lev,case when datediff(dd,StartDate,@StartDate)<0 then 1 else 0 end as Flag from A where datediff(dd,StartDate,@StartDate)<0
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select ID,ParentId,Rank,StartDate, @lev as lev,case when datediff(dd,StartDate,@StartDate)<0 then 1 else 0 end as Flag from A where id in (select ParentId from @T where lev=@lev-1)
end
select Distinct ID,ParentId,Rank,StartDate,Flag from @T
你的方法似乎不对,如果
DECLARE @StartDate AS DATETIME
SET @StartDate = '2006-5-4'
你得到的结果是
ID ParentId Rank StartDate Flag
----------- ----------- -----------
1 0 0 2006-05-01 0
2 1 1 2006-05-02 0
4 2 2 2006-05-04 0
5 4 3 2006-05-05 1而事实上应该是
Id ParentId Rank StartDate Tag
1 0 0 2006-5-1 0
2 1 1 2006-5-2 0
4 2 2 2006-5-4 1
3 1 1 2006-5-3 0
6 3 2 2006-5-4 1
请你仔细看我的问题描述,[子节点符合条件,父节点不符合,父节点也要能显示]
create table AA (ID INT,ParentId INT,Rank INT,StartDate DATETIME)
INSERT INTO AA SELECT 1,0,0,'2006-5-1'
UNION ALL SELECT 2,1,1,'2006-5-2'
UNION ALL SELECT 3,1,1,'2006-5-3'
UNION ALL SELECT 4,2,2,'2006-5-4'
UNION ALL SELECT 5,4,3,'2006-5-5'
UNION ALL SELECT 6,3,2,'2006-5-4';
--还是用存储过程吧
create proc proc_TT(@StartDate varchar(100))
as
create TABLE #t(ID INT,ParentId INT,Rank INT,StartDate DATETIME,tempId int,orders varchar(100))
exec('
insert #t select ID,ParentId,Rank,StartDate, ParentId,Cast(id as varchar(10))
from AA where '+@StartDate)while exists (select 1 from AA where id not in (select id from #t)
and id in (select parentid from #t)
)
insert #t select ID,ParentId,Rank,StartDate, ParentId,Cast(id as varchar(10))
from AA where id not in (select id from #t)
and id in (select parentid from #t)while exists (select 1
from #t t,aa a
where t.tempid=a.id
)
update #t
set tempid=a.parentid,orders=Cast(a.id as varchar(10))+orders
from #t t,aa a
where t.tempid=a.idselect * from #t
order by ordersgo--调用
exec proc_TT 'StartDate>''2006-5-2'''--结果
ID ParentId Rank StartDate tempId orders
----------- ----------- ----------- ------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
1 0 0 2006-05-01 00:00:00.000 0 1
2 1 1 2006-05-02 00:00:00.000 0 12
4 2 2 2006-05-04 00:00:00.000 0 124
5 4 3 2006-05-05 00:00:00.000 0 1245
3 1 1 2006-05-03 00:00:00.000 0 13
6 3 2 2006-05-04 00:00:00.000 0 136(所影响的行数为 6 行)
结果:ID ParentId Rank StartDate tempId orders
----------- ----------- ----------- ------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
1 0 0 2006-05-01 00:00:00.000 0 1
2 1 1 2006-05-02 00:00:00.000 0 12
4 2 2 2006-05-04 00:00:00.000 0 124
3 1 1 2006-05-03 00:00:00.000 0 13
6 3 2 2006-05-04 00:00:00.000 0 136(所影响的行数为 5 行)
order by orders
--〉
select ID,ParentId,Rank,StartDate from #t
order by orders最后加
drop table #t
和你要实现的功能相同!!
private void ShowTree()
{
tblBom.Clear();
string strconn= ConfigurationSettings.AppSettings["dsn"];
SqlConnection myconnection= new SqlConnection();
myconnection.ConnectionString=strconn;
string strSql="select * from bom order by 父项编号 asc";
SqlDataAdapter da=new SqlDataAdapter(strSql,myconnection);
da.Fill(tblBom);
DataView viewBom=new DataView(tblBom);
viewBom.RowFilter="低层码=0";
if(viewBom.Count>0)
{
int i=0;
foreach(DataRowView myRow in viewBom)
{
string strName=myRow["物料编号"].ToString().Trim()+SetNodeName(myRow["物料编号"].ToString().Trim());
string strid=myRow["物料编号"].ToString();
treeFirstLevel=new TreeNode();
treeFirstLevel.Text=strName;
treeFirstLevel.NavigateUrl="bom.aspx?id="+strid+"";
TreeView1.Nodes.Add(treeFirstLevel);
PopulateTreeView(strName,TreeView1.Nodes[i],myRow);
i++;
}
}
}