1. 树(sql2005以上版本)

--通过子节点查询父节点
WITH 
TREE AS( 
    SELECT * FROM tstructure 
    WHERE id = 6  -- 要查询的子 id 
    UNION ALL 
    SELECT tstructure.* FROM tstructure, TREE 
    WHERE TREE.parent = tstructure.id
) 
SELECT * FROM TREE

--通过父节点查询子节点
WITH 
TREE AS( 
    SELECT * FROM tstructure 
    WHERE parent = 2  -- 要查询的父 id 
    UNION ALL 
    SELECT tstructure.* FROM tstructure, TREE 
    WHERE tstructure.parent = TREE.id
) 
SELECT * FROM TREE

?

2. 从结果集更新表?

update t_health_gps 
set latitude = b.lat, longitude = b.lng 
from t_health_gps a , 
(select x.aged_id, 
y.community, y.detailed, 
z.area_name,z.position, 
u.gps_id, u.latitude,u.longitude,
SUBSTRING(z.position, 0, CHARINDEX(',', z.position)) as lng,
SUBSTRING(z.position, CHARINDEX(',', z.position) + 1, LEN(z.position)) as lat
from t_aged x, t_aged_address y, t_area_dict z, t_health_gps u
where x.aged_id = y.aged_id  and y.community = z.area_id and u.aged_id = x.aged_id 
) as b
where a.gps_id = b.gps_id

?

3. 注释

EXECUTE sp_addextendedproperty N'MS_Description', '服务商信息表',
	 N'user', N'dbo', N'table', N't_sp_company', NULL, NULL;
EXECUTE sp_addextendedproperty N'MS_Description', '服务商ID',
	 N'user', N'dbo', N'table', N't_sp_company', N'COLUMN', N'sp_id';

?

4. 修改字段

alter table t_member alter column face_url varchar(500);

?