熱門文章

  • SQL 宣告變數 設定變數
    DECLARE @ID AS INT DECLARE @LastCust AS VARCHAR(50) DECLARE @LastReceiveTime AS DATETIME SET @ThisCust = ''
  • SQL 分組 分類 計算個數
    SELECT DISTINCT GroupField1, COUNT(*) AS CountField FROM SourceTableName GROUP BY GroupField1 HAVING COUNT(*) > 1 ORDER BY CountField
  • SQL 分組排名
    ----以相同GroupField為組，依據SortField排序，並給予排名RANK SELECT RANK() OVER (PARTITION BY SourceTableName.GroupField ORDER BY SourceTableName1.SortField1...
  • DataTable DataView DataGrid中一些容易混淆的概念
    DataTable,DataView和DataGrid中一些容易混淆的概念 一、DataTable DataTable表示內存中數據的一個表，它完全是在內存中的一個獨立存在，包含了這張表的全部信息。DataTable可以是從通過連接從數據庫中讀取出來形成的一個表，一旦將內容讀到D...
  • SQL 字串相加
    StrA + StrB
  • DataTable DataView Print 列印
    vb.net Private Sub PrintTableOrView(ByVal dv As DataView, ByVal label As String) Dim sw As System.IO.StringWriter Dim output As Stri...
  • SQL 迴圈
    --宣告Cursor的資料來源Table DECLARE vendor_cursor CURSOR FOR SELECT Field1, Field2, Field3 FROM SourceTableName WHERE Field4 IN ( SELECT DISTINCT ...
  • SQL 字串index
    IF PATINDEX('%' + @pattern + '%', @Expression) = 0 找不到 IF PATINDEX('%' + @pattern + '%', @Expression) = 1 字串...
  • 網路簡介與指令
    網路簡介與指令
  • IIS Recycle AppPool List AppPool
    IIS 6 iisapp /a DefaultAppPool /r c:\windows\system32\iisapp.vbs /a YourPoolName /r cscript.exe c:\windows\system32\iisapp.vbs /a YourPoolNa...

星期三, 3月 23, 2011

SQL 分組排名

----以相同GroupField為組，依據SortField排序，並給予排名RANK
SELECT RANK() OVER (PARTITION BY SourceTableName.GroupField ORDER BY SourceTableName1.SortField1, SourceTableName1.SortField2 ) AS 'RANK', *
FROM SourceTableName1
WHERE ConditionField1 IN
(

SELECT DISTINCT ConditionField1
FROM SourceTableName2
GROUP BY ConditionField1
HAVING COUNT(*) > 1
)
ORDER BY SourceTableName1.SortField1 ASC
張貼者： 於
標籤：