SQL Server 2005: Recursive Hierarchies to XML
Suppose we have a recursive hierarchy stored in a relational database and we want to write it to XML. This might be for a variety of reasons – e.g. as a pre-cached input to a UI control, to export to another system using a pre-defined format, etc.
In SQL Server 2000, in order to get it straight to XML using FOR XML EXPLICIT, we would have to know the depth of the lowest node beforehand (without doing some very ugly dynamic SQL), so this does not help us.
It would be useful to access the data in the same order that it will appear in the XML. I.e.
Node1
Node2
Node3
Node4
Node5
Getting at the data in this order will allow us to iterate through the nodes in sequential order. This avoids using the DOM and is significantly quicker and more efficient as it avoids loading the whole structure into memory.
We could achieve this in SQL Server 2000 using a recursive table-valued UDF. In SQL Server 2005, we also have the option of using a recursive Common Table Expression (CTE) to achieve the same functional result. Let’s compare the two ways of doing it.
A CTE is a temporary named resultset referenced by a subsequent “outer query”. They can provide similar functionality to views and derived tables, but their real value is in recursive queries. Recursive CTE’s contain an “anchor member” and a “recursive member”, which are connected by a UNION ALL operator. They can be encapsulated by UDFs for reusability.
Data Preparation
Let’s create a table and insert hierarchical values.
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT F
相关文档:
MySQL:
SELECT column from table
ORDER BY RAND()
LIMIT 1
PostgreSQL:
SELECT column from table
ORDER BY RANDOM()
LIMIT 1
Microsoft SQL Server:
SELECT TOP 1 column from table
ORDER BY NEWID()
IBM DB2
SELECT column, RAND() as IDX
from table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Thanks Ti ......
#Region " 命名空间 "
Imports System.Data
Imports System.Data.SqlClient
#End Region
Public Class DBCommon
Implements IDisposable
#Region " 成员变量 "
Private conn As SqlConnection
Private cmd As SqlCommand
Private trans As SqlTransaction
#End Region
#Region " 构造函数 "
......
应一个朋友的要求,贴上收藏的SQL常用分页的办法~~
表中主键必须为标识列,[ID] int IDENTITY (1,1)
1.分页方案一:(利用Not In和SELECT TOP分页)
语句形式:
SELECT TOP 页记录数量 *
from 表名
WHERE (ID NOT IN
(SELECT TOP (每页行数*(页数-1)) ID
from 表名
ORDER BY ......
--显示版本号,当前日期
SELECT VERSION(),CURRENT_DATE(),NOW();
--免费的计算器
SELECT (20+5)*4 AS RESULT,SIN(PI()/3);
--创建数据库
CREATE DATABASE databasename;
--删除数据库
DROP DATABASE databasename;
--显示当前存在的数据库
SHOW DATABASES;
--选择数据库
USE ......