Common Table Expression 神奇的WITH()
在語法中可以用 WITH() 模擬成一個TABLE給另一段語法查詢
---------------------------------------------------------------------------------------------------
WITH TopSales (SalesID,numsales) AS
( select persionid,count(*) from salses group by persionID)
select * from TopSales where SalesID is not null
order by numsales
WITH TopSales (SalesID,numsales) AS
( select persionid,count(*) from salses group by persionID)
select * from TopSales where SalesID is not null
order by numsales
---------------------------------------------------------------------------------------------------
如果只是這樣會覺這有什麼好神奇的
但是如果撘配其他新的語法可以做到遞迴、分頁查詢等許多之前做不到的功能
遞迴範例
假設我有一個TABLE 是GROUUP 裡面有3個欄位
GroupID,ParentGroupID,groupName
0 null Root
1 0 sub1
2 1 sub11
3 0 sub2
4 2 sub21
語法如下:
---------------------------------------------------------------------------------------------------
With org AS
(select groupName,groupID ,1 as Level from group
where ParentGroupID is null
UNION ALL
select groupName,groupID , org.Level +1 as Level
from group
INNER JOIN org ON group.ParentGroupID= org .groupID
)
seelct * from org order by Level
---------------------------------------------------------------------------------------------------
上面的語法可以一次把所有GROUP以樹狀的方式一次列出來
結果如下
GroupID,groupName,Level
0 Root 1
1 sub1 2
3 sub2 2
2 sub11 3
4 sub21 3
下一集[如何用 WITH 來達成資料分頁的效果]
----------------------- To be continue -------------------------------
全站熱搜
留言列表
