abel在S-Plus & R版提了一下这本书,在网上搜一搜就找到了,干脆贴在这里好了,有需要的可以下载下来看看,写得挺简单的:
下载:
SQL Cookbook
Table of Contents | Index
Copyright
Dedication
Preface
Why I Wrote This Book
Objectives of This Book
Audience for This Book
How to Use This Book
What's Missing from This Book
Structure of This Book
Platform and Version
Tables Used in This Book
Conventions Used in This Book
Using Code Examples
Comments and Questions
Safari® Enabled
Acknowledgments
Chapter 1. Retrieving Records
Recipe 1.1. Retrieving All Rows and Columns from a Table
Recipe 1.2. Retrieving a Subset of Rows from a Table
Recipe 1.3. Finding Rows That Satisfy Multiple Conditions
Recipe 1.4. Retrieving a Subset of Columns from a Table
Recipe 1.5. Providing Meaningful Names for Columns
Recipe 1.6. Referencing an Aliased Column in the WHERE Clause
Recipe 1.7. Concatenating Column Values
Recipe 1.8. Using Conditional Logic in a SELECT Statement
Recipe 1.9. Limiting the Number of Rows Returned
Recipe 1.10. Returning n Random Records from a Table
Recipe 1.11. Finding Null Values
Recipe 1.12. Transforming Nulls into Real Values
Recipe 1.13. Searching for Patterns
Chapter 2. Sorting Query Results
Recipe 2.1. Returning Query Results in a Specified Order
Recipe 2.2. Sorting by Multiple Fields
Recipe 2.3. Sorting by Substrings
Recipe 2.4. Sorting Mixed Alphanumeric Data
Recipe 2.5. Dealing with Nulls when Sorting
Recipe 2.6. Sorting on a Data Dependent Key
Chapter 3. Working with Multiple Tables
Recipe 3.1. Stacking One Rowset atop Another
Recipe 3.2. Combining Related Rows
Recipe 3.3. Finding Rows in Common Between Two Tables
Recipe 3.4. Retrieving Values from One Table That Do Not Exist in Another
Recipe 3.5. Retrieving Rows from One Table That Do Not Correspond to Rows in Another
Recipe 3.6. Adding Joins to a Query Without Interfering with Other Joins
Recipe 3.7. Determining Whether Two Tables Have the Same Data
Recipe 3.8. Identifying and Avoiding Cartesian Products
Recipe 3.9. Performing Joins when Using Aggregates
Recipe 3.10. Performing Outer Joins when Using Aggregates
Recipe 3.11. Returning Missing Data from Multiple Tables
Recipe 3.12. Using NULLs in Operations and Comparisons
Chapter 4. Inserting, Updating, Deleting
Recipe 4.1. Inserting a New Record
Recipe 4.2. Inserting Default Values
Recipe 4.3. Overriding a Default Value with NULL
Recipe 4.4. Copying Rows from One Table into Another
Recipe 4.5. Copying a Table Definition
Recipe 4.6. Inserting into Multiple Tables at Once
Recipe 4.7. Blocking Inserts to Certain Columns
Recipe 4.8. Modifying Records in a Table
Recipe 4.9. Updating when Corresponding Rows Exist
Recipe 4.10. Updating with Values from Another Table
Recipe 4.11. Merging Records
Recipe 4.12. Deleting All Records from a Table
Recipe 4.13. Deleting Specific Records
Recipe 4.14. Deleting a Single Record
Recipe 4.15. Deleting Referential Integrity Violations
Recipe 4.16. Deleting Duplicate Records
Recipe 4.17. Deleting Records Referenced from Another Table
Chapter 5. Metadata Queries
Recipe 5.1. Listing Tables in a Schema
Recipe 5.2. Listing a Table's Columns
Recipe 5.3. Listing Indexed Columns for a Table
Recipe 5.4. Listing Constraints on a Table
Recipe 5.5. Listing Foreign Keys Without Corresponding Indexes
Recipe 5.6. Using SQL to Generate SQL
Recipe 5.7. Describing the Data Dictionary Views in an Oracle Database
Chapter 6. Working with Strings
Recipe 6.1. Walking a String
Recipe 6.2. Embedding Quotes Within String Literals
Recipe 6.3. Counting the Occurrences of a Character in a String
Recipe 6.4. Removing Unwanted Characters from a String
Recipe 6.5. Separating Numeric and Character Data
Recipe 6.6. Determining Whether a String Is Alphanumeric
Recipe 6.7. Extracting Initials from a Name
Recipe 6.8. Ordering by Parts of a String
Recipe 6.9. Ordering by a Number in a String
Recipe 6.10. Creating a Delimited List from Table Rows
Recipe 6.11. Converting Delimited Data into a Multi-Valued IN-List
Recipe 6.12. Alphabetizing a String
Recipe 6.13. Identifying Strings That Can Be Treated as Numbers
Recipe 6.14. Extracting the nth Delimited Substring
Recipe 6.15. Parsing an IP Address
Chapter 7. Working with Numbers
Recipe 7.1. Computing an Average
Recipe 7.2. Finding the Min/Max Value in a Column
Recipe 7.3. Summing the Values in a Column
Recipe 7.4. Counting Rows in a Table
Recipe 7.5. Counting Values in a Column
Recipe 7.6. Generating a Running Total
Recipe 7.7. Generating a Running Product
Recipe 7.8. Calculating a Running Difference
Recipe 7.9. Calculating a Mode
Recipe 7.10. Calculating a Median
Recipe 7.11. Determining the Percentage of a Total
Recipe 7.12. Aggregating Nullable Columns
Recipe 7.13. Computing Averages Without High and Low Values
Recipe 7.14. Converting Alphanumeric Strings into Numbers
Recipe 7.15. Changing Values in a Running Total
Chapter 8. Date Arithmetic
Recipe 8.1. Adding and Subtracting Days, Months, and Years
Recipe 8.2. Determining the Number of Days Between Two Dates
Recipe 8.3. Determining the Number of Business Days Between Two Dates
Recipe 8.4. Determining the Number of Months or Years Between Two Dates
Recipe 8.5. Determining the Number of Seconds, Minutes, or Hours Between Two Dates
Recipe 8.6. Counting the Occurrences of Weekdays in a Year
Recipe 8.7. Determining the Date Difference Between the Current Record and the Next Record
Chapter 9. Date Manipulation
Recipe 9.1. Determining if a Year Is a Leap Year
Recipe 9.2. Determining the Number of Days in a Year
Recipe 9.3. Extracting Units of Time from a Date
Recipe 9.4. Determining the First and Last Day of a Month
Recipe 9.5. Determining All Dates for a Particular Weekday Throughout a Year
Recipe 9.6. Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month
Recipe 9.7. Creating a Calendar
Recipe 9.8. Listing Quarter Start and End Dates for the Year
Recipe 9.9. Determining Quarter Start and End Dates for a Given Quarter
Recipe 9.10. Filling in Missing Dates
Recipe 9.11. Searching on Specific Units of Time
Recipe 9.12. Comparing Records Using Specific Parts of a Date
Recipe 9.13. Identifying Overlapping Date Ranges
Chapter 10. Working with Ranges
Recipe 10.1. Locating a Range of Consecutive Values
Recipe 10.2. Finding Differences Between Rows in the Same Group or Partition
Recipe 10.3. Locating the Beginning and End of a Range of Consecutive Values
Recipe 10.4. Filling in Missing Values in a Range of Values
Recipe 10.5. Generating Consecutive Numeric Values
Chapter 11. Advanced Searching
Recipe 11.1. Paginating Through a Result Set
Recipe 11.2. Skipping n Rows from a Table
Recipe 11.3. Incorporating OR Logic when Using Outer Joins
Recipe 11.4. Determining Which Rows Are Reciprocals
Recipe 11.5. Selecting the Top n Records
Recipe 11.6. Finding Records with the Highest and Lowest Values
Recipe 11.7. Investigating Future Rows
Recipe 11.8. Shifting Row Values
Recipe 11.9. Ranking Results
Recipe 11.10. Suppressing Duplicates
Recipe 11.11. Finding Knight Values
Recipe 11.12. Generating Simple Forecasts
Chapter 12. Reporting and Warehousing
Recipe 12.1. Pivoting a Result Set into One Row
Recipe 12.2. Pivoting a Result Set into Multiple Rows
Recipe 12.3. Reverse Pivoting a Result Set
Recipe 12.4. Reverse Pivoting a Result Set into One Column
Recipe 12.5. Suppressing Repeating Values from a Result Set
Recipe 12.6. Pivoting a Result Set to Facilitate Inter-Row Calculations
Recipe 12.7. Creating Buckets of Data, of a Fixed Size
Recipe 12.8. Creating a Predefined Number of Buckets
Recipe 12.9. Creating Horizontal Histograms
Recipe 12.10. Creating Vertical Histograms
Recipe 12.11. Returning Non-GROUP BY Columns
Recipe 12.12. Calculating Simple Subtotals
Recipe 12.13. Calculating Subtotals for All Possible Expression Combinations
Recipe 12.14. Identifying Rows That Are Not Subtotals
Recipe 12.15. Using Case Expressions to Flag Rows
Recipe 12.16. Creating a Sparse Matrix
Recipe 12.17. Grouping Rows by Units of Time
Recipe 12.18. Performing Aggregations over Different Groups/Partitions Simultaneously
Recipe 12.19. Performing Aggregations over a Moving Range of Values
Recipe 12.20. Pivoting a Result Set with Subtotals
Chapter 13. Hierarchical Queries
Recipe 13.1. Expressing a Parent-Child Relationship
Recipe 13.2. Expressing a Child-Parent-Grandparent Relationship
Recipe 13.3. Creating a Hierarchical View of a Table
Recipe 13.4. Finding All Child Rows for a Given Parent Row
Recipe 13.5. Determining Which Rows Are Leaf, Branch, or Root Nodes
Chapter 14. Odds 'n' Ends
Recipe 14.1. Creating Cross-Tab Reports Using SQL Server's PIVOT Operator
Recipe 14.2. Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator
Recipe 14.3. Transposing a Result Set Using Oracle's MODEL Clause
Recipe 14.4. Extracting Elements of a String from Unfixed Locations
Recipe 14.5. Finding the Number of Days in a Year (an Alternate Solution for Oracle)
Recipe 14.6. Searching for Mixed Alphanumeric Strings
Recipe 14.7. Converting Whole Numbers to Binary Using Oracle
Recipe 14.8. Pivoting a Ranked Result Set
Recipe 14.9. Adding a Column Header into a Double Pivoted Result Set
Recipe 14.10. Converting a Scalar Subquery to a Composite Subquery in Oracle
Recipe 14.11. Parsing Serialized Data into Rows
Recipe 14.12. Calculating Percent Relative to Total
Recipe 14.13. Creating CSV Output from Oracle
Recipe 14.14. Finding Text Not Matching a Pattern (Oracle)
Recipe 14.15. Transforming Data with an Inline View
Recipe 14.16. Testing for Existence of a Value Within a Group
Appendix A. Window Function Refresher
Recipe A.1. Grouping
Recipe A.2. Windowing
Appendix B. Rozenshtein Revisited
Recipe B.1. Rozenshtein's Example Tables
Recipe B.2. Answering Questions Involving Negation
Recipe B.3. Answering Questions Involving "at Most"
Recipe B.4. Answering Questions Involving "at Least"
Recipe B.5. Answering Questions Involving "Exactly"
Recipe B.6. Answering Questions Involving "Any" or "All"
About the Author
Colophon
Index