常用SQL SERVER 編程 技巧

有個表估計是預存程序插進去的吧! 所以想找到這個過程名.該如何找呢?
select object_Name(ID) from syscomments where text like '%表名%'

 

 

重複資料刪除的ID留下時間最後的一條記錄
ID  Name  LastLoginDate
001  A    2008-12-01
002  B    2008-12-03
001  A    2008-12-04
留下 001  A    2008-12-04
刪除 001  A    2008-12-01

也就是ID 重複的 把最後的LastLoginDate 留下 其他的給刪除

SQL code delete tb from tb t where LastLoginDate not in (select max(LastLoginDate) from tb where id = t.id)

 

DECLARE @vendor_id int, @vendor_name nvarchar(50), --嵌套雙層遊標迴圈
@message varchar(80), @product nvarchar(50)

PRINT '-------- Vendor Products Report --------'

DECLARE vendor_cursorCURSOR FOR  －－定義遊標
   SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID

OPEN vendor_cursor －－開啟遊標

FETCH NEXT FROM vendor_cursor  INTO @vendor_id, @vendor_name 提取當前行的值到變數中

WHILE @@FETCH_STATUS = 0  －－如果有記錄行則迴圈
BEGIN
   PRINT ' '
   SELECT @message = '----- Products From Vendor: ' + @vendor_name
   PRINT @message

     -- 要在內部定義內迴圈遊標

  DECLARE product_cursorCURSOR FOR
  SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v
  WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id  －－遊標帶參數的是從外部擷取值

  OPEN product_cursor

  FETCH NEXT FROM product_cursor INTO @product
  IF @@FETCH_STATUS <> 0
    PRINT '         <<None>>'    
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @message = '         ' + @product
    PRINT @message
    FETCH NEXT FROM product_cursor INTO @product
  END
CLOSE product_cursor
DEALLOCATE product_cursor
FETCH NEXT FROM vendor_cursor  INTO @vendor_id, @vendor_name -- 得到下個供應資料
END
CLOSE vendor_cursor