Oracle SqlServer? 同时支持，挺费劲的，问题（日期与临时表兼容,Tree 型数据所有父节点，子节点处理）

1.??? select
??????? count(*)
??? from
??????? (select
??????????? *
??????? from
??????????? transaction_history
??????? union
??????? select
??????????? *
??????? from
??????????? transaction
??? )? --无别名（Oracle）
where
??? to_char(createdate,'yyyy-MM-dd')<='2012-01-06'
??? and? to_char(createdate,'yyyy-MM-dd')>='2011-12-27'?
??? and stockcode='000016'

但是SqlServer 不行，必须对临时表别名

select
??????? count(*)
??? from
??????? (select
??????????? *
??????? from
??????????? transaction_history
??????? union
??????? select
??????????? *
??????? from
??????????? transaction
??? )? _temp--有别名(SqlServer)
where
??convert(varchar(10), createdate, 20)?<='2012-01-06'
??? and? convert(varchar(10), createdate, 20)?>='2011-12-27'?
??? and stockcode='000016'

2.SQL 获取所有父节点

Sql_server fn:

CREATE FUNCTION f_getAllParent(@SID varchar(200))
RETURNS @t_Level TABLE(SID varchar(200),name varchar(100),Level int)
AS
BEGIN
??? declare @name varchar(100)?
??? DECLARE @Level int
??? SET @Level=1
??? select @name=name from SYS_UNIT where sid=@sid
??? INSERT @t_Level SELECT @SID,@name,@Level
??? WHILE @@ROWCOUNT>0
??? BEGIN
??????? SET @Level=@Level+1
??????? INSERT @t_Level
????SELECT a.parentsid,a.name,@Level
????FROM SYS_UNIT a,@t_Level b
????WHERE a.sid=b.sid
?????AND b.Level=@Level-1
??? END
??? RETURN
END
GO

测试代码：

select a.sid? from SYS_UNIT a , f_getAllParent('dc=com') b where a.sid = b.sid order by b.Level

?

Oracle suppoet key word:

?SELECT SID FROM?SYS_UNIT

?CONNECT BY PRIOR parentsid=sid? --(prior 后跟的是父节点，则以?START WITH SID='dc=com'
指定的特定子节点作为当前子节点往上遍历所有父节点)
?START WITH SID='dc=com'

?

3.获取所有子节点：

Sql_server fn:

create function f_GetAllChild(@sid varchar(200))
returns @re table(sid varchar(200),name varchar(100),level int)?
as?
begin
? declare @name varchar(100)?
? declare @level int?
? set @level=1?
? select @name=name from SYS_UNIT where sid=@sid
? insert @re select @sid,@name,@level?
? while @@rowcount>0?
? begin?
? set @level=@level+1?
? insert @re select a.sid,a.name,@level?
? from SYS_UNIT a,@re b?
? where a.parentsid=b.sid and b.level=@level-1?
? end?
? return?
end
go

select a.sid,b.level? from SYS_UNIT a , f_GetAllChildRole('dc=com') b where a.sid = b.sid order by b.Level

?

Oracle suppoet key word:

?

?SELECT SID FROM?SYS_UNIT

?CONNECT BY PRIOR sid=parentsid? --(prior 后跟的是子节点，则以?START WITH SID='dc=com'
指定的特定子节点作为当前根节点往下遍历所有子节点)
?START WITH SID='dc=com'

?

Oracle注意区分Prior 后遍历的方向

?

?

?