Outils pour utilisateurs
tips_informatiques:bases_de_donnees:ms-sql:t-sql:t-sql
Différences
Ci-dessous, les différences entre deux révisions de la page.
| Prochaine révision | Révision précédente | ||
|
tips_informatiques:bases_de_donnees:ms-sql:t-sql:t-sql [2009/06/22 14:51] nico créée |
tips_informatiques:bases_de_donnees:ms-sql:t-sql:t-sql [2009/07/07 00:00] (Version actuelle) |
||
|---|---|---|---|
| Ligne 154: | Ligne 154: | ||
| ====== SELECT avec récursion ====== | ====== SELECT avec récursion ====== | ||
| - | FIXME | ||
| - | Having seen the basic syntax of a CTE and some its use cases in my last article, let us turn to the more interesting use: Recursive Queries. A recursive query is one in which a CTE references itself. What is the use of a recursive query? Well, all of us SQL Server 2000 users have always wanted to build hierarchical data. The most classical example is when you are given an organization structure and you wanted to represent the same in a relational database and run queries against it. | + | Having seen the basic syntax of a CTE (Common Table Expressions) and some its use cases in my last article, let us turn to the more interesting use: Recursive Queries. A recursive query is one in which a CTE references itself. What is the use of a recursive query? Well, all of us SQL Server 2000 users have always wanted to build hierarchical data. The most classical example is when you are given an organization structure and you wanted to represent the same in a relational database and run queries against it. |
| In SQL Server 2000, there was no inherent way to perform hierarchical queries and people had different implementations. You can get dozens of them if you search GOOGLE. The SQL Server 2000 Books Online also had a topic called Expanding Hierarchies where a sample implementation was provided. With the advent of recursive queries however, we now have a standard implementation for working with hierarchical data and querying against it. | In SQL Server 2000, there was no inherent way to perform hierarchical queries and people had different implementations. You can get dozens of them if you search GOOGLE. The SQL Server 2000 Books Online also had a topic called Expanding Hierarchies where a sample implementation was provided. With the advent of recursive queries however, we now have a standard implementation for working with hierarchical data and querying against it. | ||
| Ligne 162: | Ligne 161: | ||
| When you are using a recursive CTE, the syntax of the CTE changes slightly as shown: | When you are using a recursive CTE, the syntax of the CTE changes slightly as shown: | ||
| + | <code sql> | ||
| WITH cte_name (optional column list) AS | WITH cte_name (optional column list) AS | ||
| ( | ( | ||
| Ligne 170: | Ligne 170: | ||
| Statement that uses the above CTE | Statement that uses the above CTE | ||
| OPTION (MAXRECURSION n) | OPTION (MAXRECURSION n) | ||
| + | </code> | ||
| First of all, note that a recursive query is made up of 2 queries joined by a UNION ALL. The first query is called the anchor member and it forms the basic result set. The second query is called the recursive member and this is where the CTE references itself. The recursive member forms the next part of the result set using the anchor member as input. The recursive member is called repeatedly until no more rows are returned (called the exit condition). Here are some quick rules about the recursive member. | First of all, note that a recursive query is made up of 2 queries joined by a UNION ALL. The first query is called the anchor member and it forms the basic result set. The second query is called the recursive member and this is where the CTE references itself. The recursive member forms the next part of the result set using the anchor member as input. The recursive member is called repeatedly until no more rows are returned (called the exit condition). Here are some quick rules about the recursive member. | ||
| - | 1. Only UNION ALL is allowed between the anchor and the recursive member | + | - Only UNION ALL is allowed between the anchor and the recursive member |
| - | 2. Only 1 CTE reference per recursive member is allowed | + | - Only 1 CTE reference per recursive member is allowed |
| When I first read about CTEs, my thought was: Hey, this is similar to how I would write a recursive factorial program! You could not be far away from the truth and in fact, it makes a lot of sense to understand CTEs from the point of how we write recursive functions in programming languages. Here is an implementation of a recursive factorial program using VB.NET. | When I first read about CTEs, my thought was: Hey, this is similar to how I would write a recursive factorial program! You could not be far away from the truth and in fact, it makes a lot of sense to understand CTEs from the point of how we write recursive functions in programming languages. Here is an implementation of a recursive factorial program using VB.NET. | ||
| + | <code vb> | ||
| Module Module1 | Module Module1 | ||
| Sub Main() | Sub Main() | ||
| Ligne 205: | Ligne 207: | ||
| End Module | End Module | ||
| + | |||
| + | </code> | ||
| Let’s dissect the above program and equate it to terms that we introduced in the recursive CTE definition. | Let’s dissect the above program and equate it to terms that we introduced in the recursive CTE definition. | ||
| Ligne 217: | Ligne 221: | ||
| Here is the sample organizational chart. | Here is the sample organizational chart. | ||
| - | * Chief Executive Officer | + | <code> |
| - | o Senior Director – Product Development | + | * Chief Executive Officer |
| - | + Product Development Manager | + | o Senior Director – Product Development |
| - | # Project Lead | + | + Product Development Manager |
| - | * Developers | + | # Project Lead |
| - | # QA Lead | + | * Developers |
| - | * Testing Team | + | # QA Lead |
| - | # Documentation Lead | + | * Testing Team |
| - | * Technical Writers | + | # Documentation Lead |
| - | o Senior Director – Finance | + | * Technical Writers |
| - | + Accountants | + | o Senior Director – Finance |
| - | o Senior Director – Human Resources | + | + Accountants |
| - | + HR Professionals | + | o Senior Director – Human Resources |
| + | + HR Professionals | ||
| + | </code> | ||
| Note that we are representing only the structure and not the people playing the roles. For example, there may be many project leads under a single product development manager, but we are interested in only the structure of the organization and not how many people of each type are present. | Note that we are representing only the structure and not the people playing the roles. For example, there may be many project leads under a single product development manager, but we are interested in only the structure of the organization and not how many people of each type are present. | ||
| Ligne 235: | Ligne 241: | ||
| Let us now create the table to hold this structure and then populate it with data. Here are the sample scripts that I’ve used. | Let us now create the table to hold this structure and then populate it with data. Here are the sample scripts that I’ve used. | ||
| + | <code sql> | ||
| IF (OBJECT_ID ('dbo.SampleOrg', 'U') IS NOT NULL) | IF (OBJECT_ID ('dbo.SampleOrg', 'U') IS NOT NULL) | ||
| DROP TABLE dbo.SampleOrg | DROP TABLE dbo.SampleOrg | ||
| Ligne 261: | Ligne 268: | ||
| INSERT INTO dbo.SampleOrg SELECT 13, 'HR Professionals', 4 | INSERT INTO dbo.SampleOrg SELECT 13, 'HR Professionals', 4 | ||
| GO | GO | ||
| + | </code> | ||
| Ok. Now that we have the sample data in place, let us try some recursive queries to see how they work. | Ok. Now that we have the sample data in place, let us try some recursive queries to see how they work. | ||
| Ligne 268: | Ligne 276: | ||
| Before we dissect the logic used to achieve this requirement, let us see the query itself. Here is the snippet that produces the above requirement based on the organization chart table created earlier. | Before we dissect the logic used to achieve this requirement, let us see the query itself. Here is the snippet that produces the above requirement based on the organization chart table created earlier. | ||
| + | <code sql> | ||
| WITH SampleOrgChart (Level, Position, ReportingLevel, OrgLevel, SortKey) AS | WITH SampleOrgChart (Level, Position, ReportingLevel, OrgLevel, SortKey) AS | ||
| ( | ( | ||
| Ligne 288: | Ligne 297: | ||
| ) | ) | ||
| SELECT * FROM SampleOrgChart ORDER BY SortKey | SELECT * FROM SampleOrgChart ORDER BY SortKey | ||
| + | </code> | ||
| Let’s understand what this query does: | Let’s understand what this query does: | ||
| - | 1. First, we create the anchor member as the record which is for the Product Development Manager. As part of this query, we create two pseudo columns. One for indicating the level (called OrgLevel) and for sorting the records in the right fashion (called SortKey). The sort key for us is the primary key of the table converted to a binary column. | + | - First, we create the anchor member as the record which is for the Product Development Manager. As part of this query, we create two pseudo columns. One for indicating the level (called OrgLevel) and for sorting the records in the right fashion (called SortKey). The sort key for us is the primary key of the table converted to a binary column. |
| - | 2. After the anchor query, we now use this as the input and form the recursive query. Note that the recursive query increments the OrgLevel column and also builds the SortKey column. | + | - After the anchor query, we now use this as the input and form the recursive query. Note that the recursive query increments the OrgLevel column and also builds the SortKey column. |
| - | 3. Since we want only the people who directly report to the product development manager, we specify the condition OrgLevel < 1. What happens if we omit this condition? That is the next sample… | + | - Since we want only the people who directly report to the product development manager, we specify the condition OrgLevel < 1. What happens if we omit this condition? That is the next sample… |
| Sample 2: Show all the levels that report to the Product Development Manager | Sample 2: Show all the levels that report to the Product Development Manager | ||
| Ligne 299: | Ligne 309: | ||
| Here is the query that provides all the levels that reports to the product development manager. | Here is the query that provides all the levels that reports to the product development manager. | ||
| + | <code sql> | ||
| WITH SampleOrgChart (Level, Position, ReportingLevel, OrgLevel, SortKey) AS | WITH SampleOrgChart (Level, Position, ReportingLevel, OrgLevel, SortKey) AS | ||
| ( | ( | ||
| Ligne 318: | Ligne 329: | ||
| ) | ) | ||
| SELECT * FROM SampleOrgChart ORDER BY SortKey | SELECT * FROM SampleOrgChart ORDER BY SortKey | ||
| + | </code> | ||
| Note that by removing the WHERE clause in the recursive query, we keep going down the hierarchy. | Note that by removing the WHERE clause in the recursive query, we keep going down the hierarchy. | ||
tips_informatiques/bases_de_donnees/ms-sql/t-sql/t-sql.1245675108.txt.gz · Dernière modification: 2009/06/22 00:00 (modification externe)
Outils de la page
