两个常用的SQL语句(查询最大值、取得子结点)

查询一组数据中的最大值

declare @T table(so_no nvarchar(10), so_date datetime, curr_code nvarchar(5), cust_code nvarchar(10), item_no nvarchar(20), qty numeric(9), unit_price numeric(9), uom nvarchar(4), ftd datetime)

insert into @T select a.so_no , a.curr_code,a.so_date, a.cust_code ,b.item_no,b.qty,b.unit_price,b.uom,b.ftd from SOHEAD a, SODETAIL b where a.so_no = b.so_no

select * from @T c where
not exists(select 1 from @T d where c.so_no = d.so_no and Datediff(dd,c.ftd,d.ftd)>0)

取得某一结点下的所有子结点的记录。

--根据图书名称和种类得到图书信息
CREATE PROCEDURE GetBookByNameAndCategory
(
 @FullName NVARCHAR(200),
 @Category INT       
)
AS
BEGIN
 IF @Category <> -1
 BEGIN
  create table #T(
   [ID] int,
   ParentID int,
   [Level] INT
    )
  
  declare @strCategory nvarchar(10)
  set @strCategory='' + @category

  declare @s nvarchar(4000)
     
  set @s = N'declare @i int  set @i = 1
    insert into #T select pkid,parentid,@i from tBookCategory where PKID='+ @strCategory +N'
    while @@rowcount<>0
    begin
    set @i = @i + 1
  
    insert into #t
    select
    a.pkid,a.parentid,@i
    from  tBookCategory a,#t b
    where a.parentid=b.id and b.Level = @i-1
    end'
  exec(@s)
    
  select tBook.*
  from #T a,tBook
  where a.[id]=tBook.Category
  AND (@FullName is null or @FullName='' or tBook.FullName like '%'+@FullName+'%')
 END
 ELSE
 BEGIN
  SELECT * FROM tBook where @FullName is null or @FullName='' or tBook.FullName like '%'+@FullName+'%'
 END
END