---
title: "常用SQL SERVER 编程 技巧"
author: "winzenghua"
date: "2008-12-25T11:19:00Z"
tags: []
slug: "sql-server"
draft: false
lastmod: "2008-12-25T11:19:00Z"
id: 987912
url: "https://java4gis.iteye.com/blog/987912"
---

有个表估计是存储过程插进去的吧! 所以想找到这个过程名.该如何找呢?  
select object\_Name(ID) from syscomments where text like '%表名%'![](http://imgcache.qq.com/ac/b.gif)

删除重复的ID留下时间最后的一条记录  
IDNameLastLoginDate  
001A2008-12-01  
002B2008-12-03  
001A2008-12-04  
留下 001A2008-12-04  
删除 001A2008-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\_cursor&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;CURSOR&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt; FOR－－定义游标&lt;br&gt; SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID&lt;br&gt;&lt;br&gt;OPEN vendor\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor －－打开游标&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;&lt;br&gt;&lt;br&gt;FETCH NEXT FROM vendor\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;INTO @vendor\_id, @vendor\_name 提取当前行的值到变量中&lt;br&gt;&lt;br&gt;WHILE @@FETCH\_STATUS = 0－－如果有记录行则循环&lt;br&gt;BEGIN&lt;br&gt; PRINT ' '&lt;br&gt; SELECT @message = '----- Products From Vendor: ' + @vendor\_name&lt;br&gt; PRINT @message&lt;br&gt;&lt;br&gt; -- 要在内部定义内循环游标&lt;br&gt;&lt;br&gt;DECLARE product\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;CURSOR&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt; FOR &lt;br&gt;SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v &lt;br&gt;WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor\_id－－游标带参数的是从外部获取值&lt;br&gt;&lt;br&gt;OPEN product\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;&lt;br&gt;&lt;br&gt;FETCH NEXT FROM product\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt; INTO @product&lt;br&gt;IF @@FETCH\_STATUS &lt;&gt; 0 &lt;br&gt;PRINT ' &lt;&lt;None&gt;&gt;' &lt;br&gt;WHILE @@FETCH\_STATUS = 0&lt;br&gt;BEGIN&lt;br&gt;SELECT @message = ' ' + @product&lt;br&gt;PRINT @message&lt;br&gt;FETCH NEXT FROM product\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt; INTO @product&lt;br&gt;END&lt;br&gt;CLOSE product\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;&lt;br&gt;DEALLOCATE product\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;&lt;br&gt;FETCH NEXT FROM vendor\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;INTO @vendor\_id, @vendor\_name -- 得到下个供应数据&lt;br&gt;END &lt;br&gt;CLOSE vendor\_&lt;font style="LINE-HEIGHT: 1.3em" color="#ffffff"&gt;cursor&lt;/font&gt;&lt;wbr style="LINE-HEIGHT: 1.3em"&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;&lt;/wbr&gt;