Select dữ liệu từ database ra SiteMapNode

Trong bài viết Xây dựng Menu đa cấp với Multi Sitemap mình có hướng dẫn cách tạo menu đa cấp với MultiSitemap với dữ liệu từ file.sitemap có sẵn. Bài này mình sẽ viết hàm trong SQL để lấy dữ liệu từ database dưới dạng SiteMapNode để sử dụng tạo menu đa cấp

Thường việc này bạn có thể dùng code asp.net (C# hay VB) để thực hiện bằng vòng lặp hoặc sử dụng System.Xml - XmlTextWriter để thực hiện. Nhưng bạn cũng có thể sử dụng vòng lặp trong SQL để thực hiện việc này. Ở đây mình viết hàm trong SQL kết quả trả về là nội dung của file MenuTop.sitemap. Hàm này lấy đến menu cấp 3. nếu dự án của bạn có nhiều hơn 3 cấp bạn hãy sửa lại hàm cho phù hợp

Bạn chạy hàm sau trong SQL

-- =============================================
-- Author:        webmaster@hmweb.com.vn
-- Description:   <Get Sitemap content>
-- =============================================
ALTER   FUNCTION [dbo].[fuGetTreeNodeMap]
(
)
RETURNS NVarChar(4000)
AS 
BEGIN
DECLARE @SQL nvarchar(4000)
SET @SQL=''
-- Khai báo mở sitemap
SET @SQL=@SQL+N'<?xml version="1.0" encoding="utf-8" ?>
<siteMap>'
-- Duyệt cây cấp 1 (Có Decen=0)
DECLARE @CateID int, @CateName nvarchar(200), @Link nvarchar(200)
Declare CursorCategory Cursor For SELECT CateID, CateName, Link FROM TBCategory WHERE Decen=0
Open CursorCategory
Fetch next from CursorCategory INTO @CateID, @CateName , @Link
while @@FetchStatus =0
BEGIN
-- Nếu cây cấp 1 không có con thì đóng luôn TreeNode
IF dbo.fuCountSubMenu(@CateID)=0
BEGIN
      SET @SQL=@SQL+ '
      <siteMapNode url="'+dbo.ufDomainName() + @Link + '" title="' + @CateName + '" />'
END
-- Nếu cây cấp 1 có con thì mở TreeNote và tiếp tục duyệt cây cấp 2
      ELSE
      BEGIN
            SET @SQL=@SQL+ '
            <siteMapNode url="'+dbo.ufDomainName() + @Link + '" title="' + @CateName + '" >'
            -- Duyệt cây cấp 2
                  DECLARE @CateID2 int, @CateName2 nvarchar(200), @Link2 nvarchar(200)
                  Declare CursorCategory2 Cursor For SELECT CateID, CateName, Link FROM TBCategory WHERE Decen=1 AND  ParentID=@CateID
                  Open CursorCategory2
                  Fetch next from CursorCategory2 INTO @CateID2, @CateName2 , @Link2
                  WHILE @@FETCHSTATUS=0
                  BEGIN
                        -- Nếu cây cấp 2 không có con thì đóng luôn TreeNode
                        IF dbo.fuCountSubMenu(@CateID2)=0
                              BEGIN
                                    SET @SQL=@SQL+ '<siteMapNode url="'+dbo.ufDomainName() + @Link2 + '" title="' + @CateName2 + '" />'
                              END
                        -- Nếu cây cấp 2 có con thì mở TreeNode và tiếp tục duyệt cây cấp 3
                        ELSE
                              BEGIN
                              SET @SQL=@SQL+ '
                                    <siteMapNode url="'+dbo.ufDomainName() + @Link2 + '" title="' + @CateName2 + '" >'
                                    -- Duyệt cây cấp 3
                                    DECLARE @CateID3 int, @CateName3 nvarchar(200), @Link3 nvarchar(200)
                                    Declare CursorCategory3 Cursor For SELECT CateID, CateName, Link FROM TBCategory WHERE Decen=2 AND ParentID=@CateID2
                                    Open CursorCategory3
                                    Fetch next from CursorCategory3 INTO @CateID3, @CateName3 , @Link3
                                    WHILE @@FETCHSTATUS=0
                                          BEGIN
                                                SET @SQL=@SQL+ '
                                                <siteMapNode url="'+dbo.ufDomainName() + @Link3 + '" title="' + @CateName3 + '" />'
                                          FETCH NEXT FROM CursorCategory3 INTO @CateID3, @CateName3 , @Link3
                                          END
                                    Close CursorCategory3
                                    DEALLOCATE CursorCategory3
                                    SET @SQL=@SQL+ '</siteMapNode>'
                              END
                        FETCH NEXT FROM CursorCategory2 INTO @CateID2, @CateName2 , @Link2
                  END
                  Close CursorCategory2
                  DEALLOCATE CursorCategory2
                  SET @SQL=@SQL+ '</siteMapNode>'
            END
FETCH NEXT FROM CursorCategory INTO @CateID, @CateName , @Link
END
Close CursorCategory
DEALLOCATE CursorCategory
--Đóng sitemap
SET @SQL=@SQL+'
</siteMap>'
-- Print @SQL
RETURN  @SQL
END

Nếu bảng TBCategory của bạn có nhiều bản ghi thì độ dài chuỗi @SQL có thể quá 4000. Khi đó bạn cần phân tách và ghép chuỗi lại

Kết quả hàm trên (hoặc bạn thử bằng lệnh Print @SQL) như sau (Bạn có thể copy vào visual studio để xem đúng cấu trúc)

<?xml version="1.0" encoding="utf-8" ?>
<siteMap>
  <siteMapNode url="http://www.hmweb.com.vn" title="Thể thao" >
    <siteMapNode url="http://www.hmweb.com.vn" title="Pháp luật" />
    <siteMapNode url="http://www.hmweb.com.vn" title="Môi trường" />
  </siteMapNode>
  <siteMapNode url="http://www.hmweb.com.vn" title="Thể thao" >
    <siteMapNode url="http://www.hmweb.com.vn" title="Bóng đá" >
      <siteMapNode url="http://www.hmweb.com.vn" title="Bóng đá trong nước" />
      <siteMapNode url="http://www.hmweb.com.vn" title="Bóng đá Anh" />
      <siteMapNode url="http://www.hmweb.com.vn" title="Bóng đá TBN" />
      <siteMapNode url="http://www.hmweb.com.vn" title="Bóng đá Ý" />
    </siteMapNode>
    <siteMapNode url="http://www.hmweb.com.vn" title="Quần vợt" />
  </siteMapNode>
  <siteMapNode url="http://www.hmweb.com.vn" title="Sức khỏe" />
  <siteMapNode url="http://www.hmweb.com.vn" title="Kinh doanh" >
    <siteMapNode url="http://www.hmweb.com.vn" title="Chứng khoán" />
    <siteMapNode url="http://www.hmweb.com.vn" title="Thị trường" />
    <siteMapNode url="http://www.hmweb.com.vn" title="Doanh nghiệp" />
  </siteMapNode>
  <siteMapNode url="http://www.hmweb.com.vn" title="Giải trí" />
</siteMap>

Khi đã có dữ liệu hàm trên rồi bạn có thể chạy thử bằng câu lệnh SELECT dbo.fuGetTreeNode_Map() để xem kết quả.
Từ kết quả trên để đưa ra file.sitemap bạn chỉ cần viết 1 hàm (C#) write ra file với phát biểu Select là xong.

Có thời gian mình sẽ hướng dẫn dùng asp.net để bind dữ liệu thàng sitemap

Chúc các bạn thành công

Xây dựng Menu đa cấp với Multi Sitemap

Để tránh việc trình duyệt phải quay lại server và lấy dữ liệu từ database quá nhiều chúng ta có thể xây dựng menu đa cấp bằng cách dùng asp:Menu  sử dụng Site Map file. Bạn hãy xây dựng cho mình 1 control để sử dụng được nhiều Site map trong website của bạn. Mình sẽ hướng dẫn các bạn xây dựng để có thể sử dụng Menu đa cấp với MultiSitemap
1. Xây dựng Control menu
Bạn hãy tạo một thư mục Controls (Bạn có thể không cần tạo thư mục nhưng theo mình thì bạn nên tạo vì trong dự án của mình có thể có nhiều controls) sau đó nhấn phải chuột vào thư mục này, Chon Add New Item… tiếp theo chọn Web User Control đặt tên cho Control của bạn (ucMenuMultiSitemap.ascx)

Nội dung trong file Control (ucMenuMultiSitemap.ascx) như sau:

<asp:Menu 
    id="mnMainMeNu" 
    runat="server" 
    StaticEnableDefaultPopOutImage="False" 
    Orientation="Horizontal" 
    Height="20px"
    EnableTheming="True"
    DynamicVerticalOffset="0" 
    DynamicHorizontalOffset="0">
    <StaticMenuStyle 
        BorderStyle="Outset" 
        BorderWidth="0px" 
        CssClass="IE8Fix" 
        Height="23px"  />
    <StaticMenuItemStyle 
        CssClass="IE8Fix" 
        ForeColor="Black" 
        Height="20px"
        HorizontalPadding="7px"  />
    <DynamicMenuStyle 
        BorderStyle="Outset" 
        BorderWidth="1px" 
        CssClass="IE8Fix"  />
    <DynamicMenuItemStyle 
        CssClass="IE8Fix" 
        ForeColor="Black"
        Height="21px" 
        HorizontalPadding="7px" 
        Width="150px"  />
    <DynamicHoverStyle 
        BackColor="SteelBlue" 
        CssClass="IE8Fix" 
        ForeColor="White" 
        Height="20px"  />
    <StaticHoverStyle 
        BackColor="SteelBlue" 
        CssClass="IE8Fix" 
        ForeColor="White"  />
    <DataBindings> 
        <asp:MenuItemBinding
            DataMember="siteMapNode" 
            TextField="title"  
            NavigateUrlField="url"  /> 
    </DataBindings>
</asp:Menu>

Trong file code C# (ucMenuMultiSitemap.ascx.cs) bạn viết như sau.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Controls_ucMenuMultiSitemap : System.Web.UI.UserControl
{
    /// <summary>
    /// Định nghĩa các loại menu
    /// </summary>
    public enum MenuSitemap
    {
        TopMenu, BottomMenu, NotSet, LeftMenu, RightMenu
    }
    MenuSitemap eMenuToLoad = MenuSitemap.NotSet;
    public MenuSitemap MenuToLoad
    {
        get { return eMenuToLoad; }
        set { eMenuToLoad = value; }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        mnMainMeNu.DataSource = GetMenuDataSource(eMenuToLoad, Server.MapPath("~"));
        mnMainMeNu.DataBind();
    }
    /// <summary>
    /// Lấy menu theo tùy chọn menu và tên file sitemap
    /// </summary>
    XmlDataSource GetMenuDataSource(MenuSitemap menu, string serverMapPath)
    {
        XmlDataSource objData = new XmlDataSource();
        objData.XPath = "siteMap/siteMapNode";
        switch (menu)
        {
            case MenuSitemap.TopMenu:
                objData.DataFile = serverMapPath + @"\App_Data\MenuTop.sitemap";
                break;
            case MenuSitemap.BottomMenu:
                objData.DataFile = serverMapPath + @"\App_Data\MenuBottom.sitemap";
                break;
            case MenuSitemap.LeftMenu:
                objData.DataFile = serverMapPath + @"\App_Data\MenuLeft.sitemap";
                break;
            case MenuSitemap.RightMenu:
                objData.DataFile = serverMapPath + @"\App_Data\MenuRight.sitemap";
                break;
            default:
                break;
        }
        objData.DataBind();
        return objData;
    }
}

Vậy là xong phần xây dựng Control menu

2. Sử dụng Control menu

Để sử dụng Control này trước tiên bạn cần khai báo như sau (Đặt trên thẻ <!DOCTYPE)

<%@ Register TagPrefix="hmwebmenu" TagName="MyMenu" Src="~/Controls/ucMenuMultiSitemap.ascx" %>

Sau đó trong nội dung nơi mà bạn muốn đặt menu của bạn, bạn chỉ cần sử dụng thẻ menu như sau:

<hmwebmenu:MyMenu ID="MyMenu1" runat="server" MenuToLoad="BottomMenu" />
<%--Trong thẻ trên bạn chọn MenuToload=”BottomMenu” là để menu của bạn đọc nội dung từ file MenuBottom.sitemap được khai báo trong hàm GetMenuDataSource, Nếu không muốn khai báo trong trang aspx, bạn có thể dùng codebehind để thực hiện --%>

Kết quả khi chạy như sau:

Bạn cần chú ý trong thẻ <style> bạn cần có IE8Fix để sử dụng trong menu nếu không với trình duyệt IE8 sẽ không chạy được với asp:menu.

<style>
.IE8Fix
{
    z-index: 1000;
}
</style>

Bạn có thể download source về và chạy thử. 

MultiSitemapMenu.rar (4,18 kb)

Bạn có thể thắc mắc khi lấy nội dung từ database để tạo menu đa cấp. Việc đó cũng không khó khăn. ở bài sau mình sẽ hướng dẫn cách bind dữ liệu từ database ra file.sitemap. Bạn xem hàm viết bằng SQL

Mở rộng bài này bạn có thể thực hiện việc phân quền cho ứng dụng. Ví dụ ứng dụng của bạn có 2 mức quyền là admin và User khi đó bạn cần tạo 2 file admin.sitemap và user.sitemap, còn việc phân quyền đề khi nào thì gọi các file tương ứng thì cũng không có gì khó khăn

Chúc các bạn thành công!

Sử dụng và quản lý Database Mail

SQL Server 2005 có một hệ thống mail nhỏ được gọi là Database Mail. Database Mail là một tính năng được cải thiện trong SQL Mail so với các phiên bản trước đó của SQL Server. Với tính năng này bạn có thể thiết lập nhiều tài khoản cũng như hồ sơ để hỗ trợ những vấn đề về email. Database Mail là gì?

Database Mail là một hệ thống hàng đợi của mail. Các email message được lưu trong một hàng đợi bên trong cơ sở dữ liệu để được xử lý. Khi một email message được đưa vào trong hàng đợi, một quá trình mở sẽ được kích hoạt để gửi email messages trong hàng đợi đến mail server thích hợp. Khi email đã được gửi thì một email message có trạng thái của phân phối sẽ được gửi trở lại cho SQL Server.

Kích hoạt Database Mail

Database Mail không ở trạng thái có sẵn khi cài đặt. Từ mô hình bảo mật của SQL Server có một số thứ bị tắt mặc định, bạn cần phải kích hoạt Database Mail nếu muốn sử dụng nó. Bạn có thể sử dụng công cụ cấu hình Surface Area Configuration hoặc T-SQL Server dưới đây để kích hoạt Database Mail:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

 

Thiết lập tài khoản

Một tài khoản Database Mail cho biết SQL Server 2005 phải truyền thông như thế nào với máy chủ SMTP. Tài khoản chỉ rõ email được định dạng và được gửi như thế nào. Một tài khoản sẽ nhận dạng một máy chủ SMTP và phương pháp thẩm định. Tài khoản được sử dụng cho Database Mail không tương ứng với tài khoản đăng nhập của SQL Server.

Khi thiết lập một tài khoản, bạn cần phải chỉ ra đủ thông tin để SQL Server 2005 có thể truyền thông với máy chủ SMTP và thẩm định lại nó nếu cần thiết. Tham khảo các nguồn dữ liệu có online để có danh sách đầy đủ về các tùy chọn trong việc định nghĩa một tài khoản. Bạn có thể thiết lập một tài khoản bằng sử dụng Database Mail Configuration Wizard, chức năng này có thể được tìm thấy ở dưới “Database Mail” trong thư mục “Management” bên trong SQL Server Management Studio, hoặc bằng sử dụng thủ tục được lưu (SP) “sysmail_add_account_sp”. Đây là một đoạn mã sử dụng phương pháp SP ở trên để tạo một tài khoản cho phép truyền thông với một máy chủ SMTP không yêu cầu sự thẩm định:

 

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Database Administration Account',
@description = 'Mail account for used by DBA staff',
@email_address = 'ProdServer01@domainname.com',
@display_name = 'ProdServer01 DBA Mail',
@replyto_address = 'Greg.Larsen@domainname.com',
@mailserver_name = 'mailserver.domainname.com';

Tài khoản này có tên là “Database Administration Account”, và có địa chỉ email là ProdServer01@domainname.com. Một trong những ưu điểm của việc sử dụng Database Mail là địa chỉ email này không nhất thiết phải là một tài khoản email hợp lệ trong hệ thống mail. Thêm vào đó, bạn lại có thể kết hợp một địa chỉ “reply to” với một tài khoản mail cơ sở dữ liệu của bạn. trong ví dụ của tôi ở trên, tôi đã định nghĩa Greg.Larsen@domainname.com là @replyto_address. Vì vậy khi một ai đó nhận đươc một email từ bất kỳ các quá trình thông báo email tự động nào của tôi họ sẽ có thể trả lời nó và email có thể được gửi trực tiếp đến tôi.

Với Database Mail, bạn có thể thiết lập nhiều tài khoản email nếu muốn. Trong phần dưới đây tôi sẽ giới thiệu một số lý do tại sao bạn lại có thể thiết lập được nhiều tài khoản Database Mail như vậy.

Thiết lập hồ sơ và liên kết nó với các tài khoản

Trước khi gửi một Database Mail đến máy chủ SMTP đã được phân biệt trong một tài khoản, bạn cần phải kết hợp tài khoản với một hồ sơ (profile) và cho phép truy cập đến profile. Database Mail profile được sử dụng để cải thiện bảo mật mail. Có hai loại profile: public và private. Public profile luôn được áp dụng cho bất cứ ai được phép truy cập đến cơ sở dữ liệu msdb và là một thành viên của DatabaseMailUserRole trong cơ sở dữ liệu msdb. Trong khi đó private profile chỉ có thể được sử dụng cho những người dùng đặc biệt được phép truy cập vào private profile. Một mail profile có thể được kết hợp với một hay nhiều tài khoản khác. Bạn có thể quản lý các profile bằng Database Mail Configuration Wizard, hoặc sử dụng lệnh T-SQL Server giống như thiết lập cho một mail profile dưới đây:

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Database Administration Profile',
    @description = 'Mail Profile for use by DBA processes';

 

Ở đây tôi đã tạo một profile có tên là “Database Administration Profile”. Có một profile đã tạo không có nghĩa là bạn có thể sử dụng profile này để gửi email. Bạn vẫn cần phải kết hợp nó với ít nhật một tài khoản Database Mail và một người dùng bên trong dữ liệu msdb. Để thực hiện điều đó tôi có thể chạy hai lệnh dưới đây:

 

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Database Administration Profile',
    @account_name = 'Database Administration Account',
    @sequence_number =1 ;
-- Grant access to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Database Administration Profile',
    @principal_name = 'ProdServer01',

Câu lệnh EXECUTE đầu tiên sẽ kết hợp profile của tôi với một tài khoản. Câu lệnh thứ hai sẽ kết hợp profile với cơ sở dữ liệu msdb người dùng ‘ProdServer01’. Những người dùng này cần phải được cho phép là thành viên trong DatabaseMailUserRole trước khi họ có thể gửi mail. Khi tôi kết hợp profile này với một người dùng đặc biệt, thì profile này được hiểu như một private profile. Để tạo một public profile bạn cần phải kết hợp một profile với một “public” database role.

Gửi Database Mail

SQL Server cung cấp SP “sp_send_dbmail” để gửi mail. Cú pháp dưới đây được sử dụng để gọi SP này:

 

 

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
    [ , [ @subject = ] 'subject' ]
    [ , [ @body = ] 'body' ]
    [ , [ @body_format = ] 'body_format' ]
    [ , [ @importance = ] 'importance' ]
    [ , [ @sensitivity = ] 'sensitivity' ]
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
    [ , [ @query = ] 'query' ]
    [ , [ @execute_query_database = ] 'execute_query_database' ]
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
    [ , [ @query_attachment_filename = ] query_attachment_filename ]
    [ , [ @query_result_header = ] query_result_header ]
    [ , [ @query_result_width = ] query_result_width ]
    [ , [ @query_result_separator = ] 'query_result_separator' ]
    [ , [ @exclude_query_output = ] exclude_query_output ]
    [ , [ @append_query_error = ] append_query_error ]
    [ , [ @query_no_truncate = ] query_no_truncate ]
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

Như những gì bạn nhìn thấy, SP này hỗ trợ một số các tham số khác nhau. Thông tin về mỗi tham số này bạn có thể tham khảo trong các sách online.
Dưới đây là một ví dụ nói về tôi sẽ sử dụng private profile đã được tạo ở trên để gửi một email message đơn giản như thế nào không quan tâm đến quá trình đánh chỉ số lại cơ sở dữ liệu tự động.

 

 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Administration Profile',
@recipients = 'greg.larsen@databasejournal.com',
@body = 'Reindex database process completed successfully',
@subject = 'Reindex Database' ;

Database Mail xử lý hết một hàng đợi khi chạy lệnh trên thì tôi sẽ có một thông báo được gửi phản hồi nói rằng mail được đưa vào hàng đợi “Mail queued”. Khi bạn thực thi sp_send_dbmail SP thì mail không được gửi đi ngay lập tức mà thay vì đó nó được lưu trong hàng đợi mail bên trong cơ sở dữ liệu msdb. SP này kích hoạt một quá trình mail mở rộng (DatabaseMail90.exe) để chạy. Thực thi này đọc mail trong hàng đợi và gửi nó đến mail server thích hợp.

Các trường hợp khác nhau trong sử dụng tài khoản và hồ sơ

Có một số cách khác nhau trong việc sử dụng những ưu điểm của đa tài khoản và profile được Database Mail cho phép bạn thực hiện với chúng.

Một trong những ưu điểm quan sát thấy của tính năng đa tài khoản đó là cấu hình profile Database Mail của bạn có thể cho phép khả năng chuyển đổi dự phòng khi một trong những máy chủ SMTP gặp các vấn đề trục trặc. Khi bổ sung vào các tài khoản cho một profile Database Mail bạn có thể cung cấp cho chúng một chuỗi số sequence_number. Khi gửi một email message mới, Database Mail cố gắng gửi nó bằng cách sử dụng số chuỗi thấp nhất trước. Nếu quá trình gửi đó bị hỏng thì Database Mail sẽ sử dụng chuỗi số cao hơn tiếp theo. Database Mail sẽ liên tục thực hiện công việc này cho đến khi mail được gửi thành công hoặc tất cả các tài khoản đã đều được thử.

Một tùy chọn khác trong việc sử dụng này là hỗ trợ cho mail message phân phát đến các địa chỉ email khác nhau. Nếu bạn có nhiều ứng dụng cần phải gửi email, mỗi ứng dụng có một địa chỉ email riêng của nó. Việc làm này sẽ giúp người nhận email tự đông phân biệt được quá trình đã gửi là quá trình gì bằng việc xem địa chỉ email.

Ngoài ra, nếu bạn sử dụng các private profile thì bạn có thể kết hợp các profile này với các chính sách bảo mật khác. Bằng cách đó sẽ cho phép bạn kiểm soát được private profile, người dùng msdb sẽ được phép sử dụng một profile Database Mail riêng biệt.

Kiểm tra Database Mail


SQL Server cung cấp 6 cửa sổ hệ thống khác nhau trong cơ sở dữ liệu msdb cho việc kiểm tra thông tin Database Mail. Các cửa sổ này có thể được sử dụgn để lấy lại thông tin trong cơ sở dữ liệu msdb có cho biết đến cả trạng thái của tất cả Database Mail hoặc chỉ những email message cụ thể. Các cửa sổ này rất hữu dụng trong việc phân biệt mail nào đã được xử lý cũng như lý do tại sao mail message có thể không được phân phối đến mail server yêu cầu. Chúng là các công cụ lý tưởng cho phép bạn có thể kiểm tra và khắc phục một số các vấn đề Database Mail. Để có thêm thông tin chi tiết về các cửa sổ này, bạn có thể tìm hiểu thêm trong các sách online.

sysmail_allitems – cửa sổ này cho phép bạn trả về một tập bản ghi, gồm có một hàng cho mỗi email message được xử lý bằng Database Mail.

sysmail_event_log – Cửa sổ này cho phép bạn trả về một hàng cho mỗi một thông báo lỗi của Windows hoặc SQL Server khi Database Mail xử lý một email message.

sysmail_faileditems – Cửa sổ này trả về một bản ghi cho mỗi một email message có một trạng thái nào đó hoặc bị thất bại.

sysmail_mailattachments – Cửa sổ này gồm một hàng cho mỗi một đính kèm đã gửi

sysmail_sentitems – Cửa sổ này gồm có một bản ghi cho mỗi một email đã được gửi thành công

sysmail_unsentitems – Cửa sổ này gồm có một bản ghi cho mỗi email hiện đang có trong hàng đợi để được gửi hoặc trong quá trình đang gửi.

Duy trì các thông báo trong MSDB Database

Tất cả các email message được lưu trong cơ sở dữ liệu msdb, bạn phải xem xét xem quản lý các thông tin này như thế nào. Phụ thuộc vào các chính sách ghi nhớ email, bạn cần thiết lập một thói quen để làm sạch có chu kỳ đối với các email message không cần thiết nữa. SQL Server 2005 cung cấp hai thủ tục lưu khác nhau để gỡ các bản ghi mail từ cơ sở dữ liệu msdb.

sysmail_delete_mailitems_sp
– SP này xóa thường xuyên các email message có trong msdb trong bảng Database Mail.

sysmail_delete_log_sp – SP này xóa các thông báo bản ghi Database Mail.

Bạn có thể xem sách online để có thêm thông tin chi tiết về SP.

T-SQL Server dưới đây sẽ xóa mail đã được gửi cách đấy một tháng:

DECLARE @delete_date datetime
SET @delete_date = dateadd(MM,-1,getdate())
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before=@delete_date

Kết luận

Database Mail SQL Server 2005 là một sự cải thiện lớn trong SQL Mail so với các phiên bản trước đây của SQL Server. Database Mail cung cấp một hệ thống mail với rất nhiều tính năng để bảo vệ và quản lý tốt hơn các mail đang gửi bằng sử dụng T-SQL. Với Database Mail trong SQL Server 2005, bạn không cần phải xây dựng một lời giải cho một giải pháp sử dụng CDOSYS để gửi email. Nếu bạn đang tìm cách để hỗ trợ email từ T-SQL, hãy xem xét đến những tính năng của Database Mail.

 

Hàm CASE trong SQL Server

Trong SQL Sever, hàm CASE kiểm định giá trị dựa trên danh sách điều kiện đưa ra, sau đó trả về một hoặc nhiều kết quả. Ở bài này chúng tôi sẽ minh hoạ một số cách dùng khác nhau của hàm này trong những trường hợp khác nhau.

Phương thức 1: Cách dùng hàm CASE đơn giản

Đây là cách dùng phổ biến nhất của hàm case, trong đó bạn có thể tạo giá trị vô hướng dựa trên danh sách điều kiện đưa ra.

Giả sử chúng ta có bảng sau với các cột id (mã số nhân viên), [First name] (tên), [Last name] (họ) và gender (giới tính). Bây giờ, chúng ta muốn tạo thêm tiền tố (Mr, Ms) ở phía trước từng tên, dựa trên giá trị của cột Gender, thực hiện như sau:

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[emp]') 
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [emp]
GO
create table Emp (id int, [First name] varchar(50),
 [Last name] varchar(50), gender char(1))
go
insert into Emp (id,[First name],[Last name], gender ) 
 values (1,'John','Smith','m')
insert into Emp (id,[First name],[Last name], gender ) 
 values (2,'James','Bond','m')
insert into Emp (id,[First name],[Last name], gender ) 
 values (3,'Alexa','Mantena','f')
insert into Emp (id,[First name],[Last name], gender ) 
 values (4,'Shui','Qui','f')
insert into Emp (id,[First name],[Last name], gender ) 
 values (5,'William','Hsu','m')
insert into Emp (id,[First name],[Last name], gender ) 
 values (6,'Danielle','Stewart','F')
insert into Emp (id,[First name],[Last name], gender ) 
 values (7,'Martha','Mcgrath','F')
insert into Emp (id,[First name],[Last name], gender ) 
 values (8,'Henry','Fayol','m')
insert into Emp (id,[First name],[Last name], gender ) 
 values (9,'Dick','Watson','m')
insert into Emp (id,[First name],[Last name], gender )
 values (10,'Helen','Foster','F')
go

Bây giờ, tạo cột [Full name] (họ tên đầy đủ) để xác định nên đặt tiền tố là “Mr.” hay “Ms.”, dựa vào giá trị trên cột Gender.

Select [id],[Full Name] = case Gender
 when 'm' then 'Mr. '+[First name]+ ' '+[Last name]
 when 'f' then 'Ms. '+[First name]+ ' '+[Last name] 
 end
from Emp

Thủ tục này trả về kết quả như hình bên dưới:

id          Full Name
----------- ----------------
1           Mr. John Smith
2           Mr. James Bond
3           Ms. Alexa Mantena
4           Ms. Shui Qui
5           Mr. William Hsu
6           Ms. Danielle Stewart
7           Ms. Martha Mcgrath
8           Mr. Henry Fayol
9           Mr. Dick Watson
10          Ms. Helen Foster

Phương thức 2: Sử dụng hàm case đơn giản với mệnh đề ELSE
Nếu thêm một hàng với giá trị NULL vào cột gender, bạn sẽ không thấy có tên nào được trả về trong tập hợp kết quả.
Chèn hàng sau vào bảng emp:

use tempdb
go
insert into Emp (id,[First name],[Last name], gender )
 values (11,'Tom','Gabe',NULL)
go

Bây giờ tạo cột [Full name] để xác định tiền tố đặt trước mỗi tên là “Mr.” hay “Ms.”, dựa trên giá trị lấy ở cột Gender:

Select [id],[Full Name] = case Gender
 when 'm' then 'Mr. '+[First name]+ ' '+[Last name] 
 when 'f' then 'Ms. '+[First name]+ ' '+[Last name] 
 end
from Emp

Kết quả trả về như sau:

id          Full Name
----------- ------------------------
1           Mr. John Smith
2           Mr. James Bond
3           Ms. Alexa Mantena
4           Ms. Shui Qui
5           Mr. William Hsu
6           Ms. Danielle Stewart
7           Ms. Martha Mcgrath
8           Mr. Henry Fayol
9           Mr. Dick Watson
10          Ms. Helen Foster
11          NULL

Phương thức 3: Sử dụng hàm CASE khi có hai hoặc nhiều điều kiện trong danh sách

Trong hai ví dụ trên, bạn thấy rằng các điều kiện được xét đến hoặc là Male, Female, hay None. Tuy nhiên, trong nhiều trường hợp bạn sẽ cần sử dụng nhiều điều kiện, nhiều toán tử cùng lúc để trả về một giá trị.

Thêm cột [Marital Status] (tình trạng hôn nhân) vào bảng và update giá trị như bên dưới:

use tempdb
go
alter table Emp add [Marital Status] char(1)  -- S-Single M-Married 
go
Update Emp set [Marital Status]='S' where id in (1,5,8)
Update Emp set [Marital Status]='M' where [Marital Status] is NULL
Go

Giả sử chúng ta muốn hiển thị tên nhân viên có tiền tố đứng trước giúp dễ dàng xác định tình trạng hôn nhân của họ. Thực hiện truy vấn như sau:

Select [id],[Full Name] = case 
when Gender ='m' and [marital status] ='S' 
    then 'MR. '+[First name]+ ' '+[Last name] 
when Gender ='m' and [marital status] ='M' 
    then 'Mr. '+[First name]+ ' '+[Last name] 
when Gender ='f' and [marital status] ='S' 
    then 'Ms. '+[First name]+ ' '+[Last name] 
when Gender ='f' and [marital status] ='M' 
    then 'Mrs. '+[First name]+ ' '+[Last name] 
else [First name]+ ' '+[Last name] 
 end
from Emp

Kết quả được trả về là:

id          Full Name
----------- --------------------
1           MR. John Smith
2           Mr. James Bond
3           Mrs. Alexa Mantena
4           Mrs. Shui Qui
5           MR. William Hsu
6           Mrs. Danielle Stewart
7           Ms. Martha Mcgrath
8           MR. Henry Fayol
9           Mr. Dick Watson
10          Mrs. Helen Foster
11          Tom Gabe

Phương thức 4: Sử dụng hàm CASE trong tìm kiếm
Giả sử chúng ta có bảng sau

 

use tempdb
go
if exists (select * from dbo.sysobjects
where id=object_id(N'[emp]')and OBJECTPROPERTY(id, N'IsUserTable')=1)
drop table [emp]
GO
create table Emp
(id int,[First name] varchar(50),[Last name] varchar(50),Salary money)
go
insert into Emp (id,[First name],[Last name], salary )
values (1,'John','Smith',120000)
insert into Emp (id,[First name],[Last name], salary )
values (2,'James','Bond',95000)
insert into Emp (id,[First name],[Last name], salary )
values (3,'Alexa','Mantena',200000)
insert into Emp (id,[First name],[Last name], salary )
values (4,'Shui','Qui',36000)
insert into Emp (id,[First name],[Last name], salary )
values (5,'William','Hsu',39000)
insert into Emp (id,[First name],[Last name], salary )
values (6,'Danielle','Stewart',50000)
insert into Emp (id,[First name],[Last name], salary )
values (7,'Martha','Mcgrath',400000)
insert into Emp (id,[First name],[Last name], salary )
values (8,'Henry','Fayol',75000)
insert into Emp (id,[First name],[Last name], salary )
values (9,'Dick','Watson',91000)
insert into Emp (id,[First name],[Last name], salary )
values (10,'Helen','Foster',124000)
go

Và giờ muốn tạo thêm một cột Tax (thuế) dựa trên mức lương như sau

Select [id],[Full Name]=[First name]+ [Last name],Salary,Tax=case
When  salary between 0 and 36000 then Salary*.24
When  salary between 36000 and 450000 then Salary*.28
When  salary between 45000 and 75000 then Salary *.30
When  salary between 75000 and 150000 then Salary *.32
else Salary*.40 end
from Emp

Hàm này sẽ cho kết quả:

id          Full Name          Salary                Tax
----------- -------------------------------- ---------------------
1           JohnSmith         120000.00             33600.000000
2           JamesBond          95000.00             26600.000000
3           AlexaMantena      200000.00             56000.000000
4           ShuiQui            36000.00              8640.000000
5           WilliamHsu         39000.00             10920.000000
6           DanielleStewart    50000.00             14000.000000
7           MarthaMcgrath     400000.00            112000.000000
8           HenryFayol         75000.00             21000.000000
9           DickWatson         91000.00             25480.000000
10          HelenFoster       124000.00             34720.000000

Phương thức 5: Sử dụng hàm CASE trong mệnh đề ORDER BY
Giả sử chúng ta có bảng dưới trong Books:

 

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[Books]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Books]
GO
create table Books
(Bookid int, Title varchar(100),Authorname varchar(100), state char(2))
go
insert into Books (Bookid, Title, Authorname, state)
values (1, 'The Third Eye','Lobsang Rampa','CA')
insert into Books (Bookid, Title, Authorname, state)
values (2, 'Service Oriented Architecture For ','Judith Hurwitz','NJ')
insert into Books (Bookid, Title, Authorname, state)
values (3, 'Business Reference for Students and','Ray Myers','NY')
insert into Books (Bookid, Title, Authorname, state)
values (4, 'More Java Gems','Dwight Deugo', 'FL')
insert into Books (Bookid, Title, Authorname, state)
values (5, 'Six Sigma Workbook For ','Craig Gygi','FL')
insert into Books (Bookid, Title, Authorname, state)
values (6, 'Performance Appraisals: How to Achieve Top Results',
'Priscilla A. Glidden', 'NC' )
insert into Books (Bookid, Title, Authorname, state)
values (7, 'Talent Management: From Competencies ',
'John Smith','FL')
insert into Books (Bookid, Title, Authorname, state)
values (8, 'Using Unix','Howard Johnson','CT')
insert into Books (Bookid, Title, Authorname, state)
values (9, 'Mastering Oracle','Erina Zolotrova','CT')
insert into Books (Bookid, Title, Authorname, state)
values (10, 'How to become CEO','Olga Zohaskov','NY')
go

Để truy vấn tất cả các giá trị trong bảng ta sử dụng hàm truy vấn dưới:

Select * from Books

Hàm này sẽ cho kết quả như hình dưới

Giả sử chúng ta muốn hiển thị toàn bộ số sách theo thứ tự các bang: đầu tiên là NY
sau đó là CA, NJ, CT và FL.

Bạn có thể thực hiện được điều này bằng cách sử dụng hàm CASE như dưới đây:

select Title, Authorname, state from Books order by case
when state ='NY' then 1
when state ='CA' then 2
when state ='NJ' then 3
when state ='CT' then 4
when state ='FL' then 5 else 6 end

Hàm này sẽ cho kết quả như sau:

Title                                   Authorname               state
-------------------------------------- -----------------------  -----
Business Reference for Students and     Ray Myers               NY
How to become CEO                       Olga Zohaskov           NY
The Third Eye                           Lobsang Rampa           CA
Service Oriented Architecture For       Judith Hurwitz          NJ
Using Unix                              Howard Johnson          CT
Mastering Oracle                        Erina Zolotrova         CT
More Java Gems                          Dwight Deugo            FL
Six Sigma Workbook For Dummies          Craig Gygi              FL
Talent Management: From Competenci      Per John Smith          FL
Performance Appraisals: How to Achieve  Priscilla A. Glidden    NC

Phương thức 6: Sử dụng hàm CASE đơn giản trong mệnh đề GROUP BY

Giả sử chúng ta có bảng sau

 

set quoted_identifier off
go
use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[emp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [emp]
GO
create table Emp (id int, [First name] varchar(50),
[Last name] varchar(50), Salary money, state char(2))
go
insert into Emp (id,[First name],[Last name], salary, State )
values (1,'John','Smith',120000,'WA')
insert into Emp (id,[First name],[Last name], salary, State )
values (2,'James','Bond',95000,'OR')
insert into Emp (id,[First name],[Last name], salary , State)
values (3,'Alexa','Mantena',200000,'WY')
insert into Emp (id,[First name],[Last name], salary, State )
values (4,'Shui','Qui',36000,'CO')
insert into Emp (id,[First name],[Last name], salary, State )
values (5,'William','Hsu',39000,'NE')
insert into Emp (id,[First name],[Last name], salary , State)
values (6,'Danielle','Stewart',50000,'TX')
insert into Emp (id,[First name],[Last name],
salary , State) values (7,'Martha','Mcgrath',400000,'PA')
insert into Emp (id,[First name],[Last name],
salary, State ) values (8,'Henry','Fayol',75000,'NJ')
insert into Emp (id,[First name],[Last name],
salary, State ) values (9,'Dick','Watson',91000,'NY')
insert into Emp (id,[First name],[Last name],
salary, State ) values (10,'Helen','Foster',124000,'AK')
go

 

Và giờ muốn tạo thêm cột TimeZone (thời gian theo vị trí địa lý) dựa vào State (bang của Mỹ)

 

select id,[First name],[Last name], salary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in
('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD',
'DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in
('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL',
'TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp

Hàm trên sẽ cho kết quả như sau

 

id First name   Last name     salary      Timezone
-------------------------------------------------------
1  John         Smith       120000.00     Pacific
2  James        Bond         95000.00     Pacific
3  Alea         Mantena     200000.00     Mountain
4  Shui         Qui          36000.00     Pacific
5  William      Hsu          39000.00     Pacific
6  Danielle     Stewart      50000.00     Central
7  Martha       Mcgrath     400000.00     Eastern
8  Henry        Fayol        75000.00     Eastern
9  Dick         Watson       91000.00     Eastern
10 Helen        Foster      124000.00     Alaskan

Giờ chúng ta lại muốn xem tất cả thông tin ở các dòng có Timezone là Eastern và Mountain

 

select * from (
select id,[First name],[Last name], salary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in
('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD',
'DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern'
when state in
('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in
('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA',
'WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Hawaii' end
from emp) as mytype where TimeZone in ('Mountain','eastern')

Kết quả hàm này như sau

id First name Last name       salary       Timezone
3  Alexa      Mantena       200000.00      Mountain
7  Martha     Mcgrath       400000.00      Eastern
8  Henry      Fayol          75000.00      Eastern
9  Dick       Watson         91000.00      Eastern

Giờ chúng ta có bảng trên và bạn lại muốn hiển thị giá trị trung bình của lương dựa trên vùng thời gian (Timezone)

select avg(salary) as AverageSalary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp group by
case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end

Hàm này sẽ cho kết quả như hình dưới

Giờ bạn lại chỉ muốn xem vùng thời gian ở Eastern và Alaskan từ kết quả trên. Chũng ta có thể sử dụng mệnh đề HAVING như dưới đây

 

select avg(salary) as AverageSalary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp group by
case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
having
case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
in ('Eastern','Alaskan')

Kết quả của hàm thể hiện trong hình sau

 

Các hàm Ranking mới trong SQL Server 2005

Cùng với SQL Server 2005, Microsoft đã giới thiệu một số tính năng mới và những tính năng này sẽ giúp cho chuyên viên về DBA hay SQL Server dễ dàng hơn trong việc viết mã và duy trì cơ sở dữ liệu SQL Server. Bài này sẽ thảo luận về các hàm ranking mới được cung cấp trong SQL Server 2005.


Các tính năng mới đó sẽ giúp bạn dễ dàng viết mã T-SQL để kết hợp xếp loại được tập hợp kết quả của bạn. Bài sẽ hướng dẫn từng phần trong các hàm ranking mới và cung cấp một số ví dụ nhằm minh họa hoạt động của hàm.
Các hàm Ranking là gì?
Các hàm Ranking cho phép bạn có thể đánh số liên tục (xếp loại) cho các tập hợp kết quả. Các hàm này có thể được sử dụng để cung cấp số thứ tự trong hệ thống đánh số tuần tự khác nhau. Có thể hiểu đơn giản như sau: bạn có từng con số nằm trên từng dòng liên tục, tại dòng thứ nhất xếp loại số 1, dòng thứ 2 xếp loại số là 2… Bạn có thể sử dụng hàm ranking theo các nhóm số tuần tự, mỗi một nhóm sẽ được đánh số theo lược đồ 1,2,3 và nhóm tiếp theo lại bắt đầu bằng 1,2,3…
Dữ liệu chạy thử cho các ví dụ
Để có một vài ví dụ cho từng hàm ranking, tôi cần thiết lập một số dữ liệu chạy thử. Trong dữ liệu chạy thử, tôi sử dụng một bảng “Person” khá đơn giản. Bảng sẽ bao gồm 3 cột “FirstName”, “Age” và “Gender”. Đoạn mã dưới nhằm tạo ra và ghi lại dữ liệu chạy thử vào file.

 

SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')

Hàm ROW_NUMBER

Hàm đầu tiên tôi muốn nói tới là ROW_NUMBER. Hàm này trả lại một dãy số tuần tự bắt đầu từ 1 cho mỗi dòng hay nhóm trong tập hợp kết quả. Hàm ROW_NUMBER sẽ có cú pháp sau:

ROW_NUMBER ( ) OVER ( [ ] )

Trong đó:

là cột hay tập hợp các cột được sử dụng để quyết định việc gộp nhóm cho hàm ROW_NUMBER áp dụng cho việc đánh số tuần tự.

là một cột hay tập hợp các cột được sử dụng để sắp xếp tập hợp kết quả trong nhóm (partition)

Để hiểu thêm về cách sử dụng hàm ROW_NUMBER, ví dụ dưới sẽ đánh số liên tục cho tất cả các dòng trong bảng Person và sắp xếp chúng theo trường Age

SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age], 
       FirstName,
       Age 
  FROM Person

Và đây là tập hợp kết quả mã T-SQL trên:

Row Number by Age    FirstName   Age
-------------------- ---------- ------
1                    Larry        5
2                    Doris        6
3                    George       6
4                    Mary         11
5                    Sherry       11
6                    Sam          17
7                    Ted          23
8                    Marty        23
9                    Sue          29
10                   Frank        38
11                   John         40

Bạn có thể thấy tôi đã đánh số liên tục cho toàn bộ các dòng trong bảng Person bắt đầu từ số 1, và tập hợp kết quả được sắp xếp theo cột Age. Sự sắp xếp này được hoàn thiện là do tiêu chuẩn “ORDER BY Age” trong mệnh đề ORDER BY của hàm ROW_NUMBER.

Giả sử bạn không muốn tập hợp kết quả của bạn được sắp xếp mà muốn đưa bảng trở lại sắp xếp theo số bản ghi của từng dòng. Hàm ROW_NUMBER lại luôn yêu cầu phải có mệnh đề ORDER BY, vậy bạn cần phải đưa một giá trị nào đó vào trong mệnh đề này. Trong hàm truy vấn bên dưới tôi đã chỉ định “SELECT 1” vào trong mệnh đề ORDER BY, điều này sẽ chỉ trả lại kết quả là bảng như đã lưu trữ ban đầu và tất nhiên cách đánh số tuần tự vẫn bắt đầu từ 1:

 

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],
       FirstName, 
       Age 
  FROM Person

Đây là tập hợp kết quả khi chạy hàm truy vấn trên:

Row Number by Record FirstName   Age
-------------------- ---------- ------
1                    Ted          23
2                    John         40
3                    George       6
4                    Mary         11
5                    Sam          17
6                    Doris        6
7                    Frank        38
8                    Larry        5
9                    Sue          29
10                   Sherry       11
11                   Marty        23

Hàm ROW_NUMBER không chỉ cho phép bạn sắp xếp toàn bộ tập hợp dòng mà còn có thể sử dụng mệnh đề PARTITION để lọc ra nhóm dòng cần đánh số. Các dòng sẽ được đánh số tuần tự trong từng giá trị PARTITION độc nhất. Các dãy số được đánh sẽ luôn bắt đầu từ 1 cho từng giá trị PARTITION mới trong tập hợp bản ghi của bạn. Hãy xem hàm truy vấn dưới đây

 

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
       FirstName, 
       Age,
       Gender 
  FROM Person

Khi chạy truy vấn trên, tập hợp kết quả sẽ ra như sau:

 

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
3                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
5                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

Trong ví dụ này tôi đã phân vùng bởi Gender và sắp xếp theo Age. Thực hành theo ví dụ này sẽ cho phép tôi đánh số tuần tự các bản ghi là Female trong bảng Person theo độ tuổi, và sau đó việc đánh số sẽ bắt đầu lại với nhóm là Male.

Hàm RANK

Đôi khi bạn muốn một dòng có cùng sắp xếp giá trị cột như các dòng khác có cùng một xếp loại. Nếu thế thì hàm RANK () có thể giúp bạn. Hàm RANK có cú pháp như sau:

 

RANK ( ) OVER ( [] )

Trong đó:

 

  • là một cột hay tập hợp các cột được sử dụng để quyết đinh việc đánh số liên tục trong hàm RANK
  • là một cột hay tập hợp các cột được sử dụng để sắp xếp tập hợp kết quả trong nhóm (partition)

Hàm RANK sẽ đánh số liên tục một tập hợp bản ghi nhưng khi có 2 dòng có cùng giá trị sắp xếp thì hàm sẽ đánh giá là cùng bậc giá trị. Giá trị xếp loại vẫn sẽ tăng kể cả khi có 2 dòng cùng giá trị, vì vậy khi đánh giá một giá trị sắp xếp tiếp theo thì số thứ tự vẫn tiếp tục được đánh nhưng sẽ tăng thêm 1 giá trị vào các dòng tiếp theo trong tập hợp.

Đây là ví dụ của hàm rank trong tập hợp bản ghi sắp xếp theo Age:

 

SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age], 
           FirstName, 
           Age
      FROM Person

 

Và kết quả trả về:

Rank by Age          FirstName  Age
-------------------- ---------- ------
1                    Larry      5
2                    Doris      6
2                    George     6
4                    Mary       11
4                    Sherry     11
6                    Sam        17
7                    Ted        23
7                    Marty      23
9                    Sue        29
10                   Frank      38
11                   John       40

Như bạn thấy, với các dòng trùng giá trị Age thì ở phần Rank by Age cũng có cùng giá trị. Bạn có thể thấy Doris và George, Mary và Sherry, cũng tương tự là Ted và Marty, từng cặp một đều có cùng giá trị Rank by Age. Lưu ý rằng Doris và George cùng có xếp loại là 2 nhưng xếp loại của Mary (có giá trị Age tiếp theo) lại không phải 3 mà là 4. Nguyên nhân ở đây là Mary được trả về bản ghi thứ 4 trong tập hợp bản ghi, và hàm RANK() đã lấy số liệu đó khi thiết lập giá trị xếp loại tiếp theo trong Rank by Age

Nếu bạn muốn có một nhiều xếp loại trong tập hợp bản ghi của mình thì với từng xếp loại bạn cần đặt một nhóm cụ thể bằng cách sử dụng mệnh đề PARTITION BY trong hàm RANK. Ví dụ dưới sẽ cho thấy tác dụng khi tôi nhóm xếp loại theo Gender và sắp xếp theo Age

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
           FirstName, 
           Age,
           Gender
      FROM Person

Đây là kết quả khi chạy các hàm truy vấn trên:

 

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
2                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
4                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

Bạn có thể thấy là Gioitinh là “F” được bắt đầu xếp loại từ 1 cho đến 4, sau đó bắt đầu đánh số lại từ 1 cho Gioitinh là “M”

Hàm DENSE_RANK

Hàm DENSE_RANK cũng giống như hàm RANK, tuy vậy, hàm này không cung cấp khoảng cách giữa các số xếp loại. Thay vào đó, hàm này sẽ xếp loại liên tục cho từng giá trị ORDER BY cụ thể. Với hàm DENSE_RANK, kể cả khi có hai dòng có cùng giá trị xếp loại thì dòng tiếp theo vẫn chỉ tăng thêm một giá trị so với dòng trên. Hàm DENSE_RANK có cú pháp như hàm RANK.

Đây là hàm DENSE_RANK được tôi sử dụng để xếp loại cho toàn bộ các bản ghi trong bảng Person theo trường Age

 

SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age], 
       FirstName, 
       Age
  FROM Person

 

Đoạn mã trên sẽ xuất ra như sau:

Dense Rank by Age    FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
2                    George     6
3                    Mary       11
3                    Sherry     11
4                    Sam        17
5                    Ted        23
5                    Marty      23
6                    Sue        29
7                    Frank      38
8                    John       40

Như bạn thấy các số trong cột “Dense Rank By Age” vẫn đảm bảo tính liên tục, không hề bị ngắt quãng kể cả khi có hai dòng cùng giá trị ORDER BY và giá trị xếp loại như Ted và Marty.

Hàm NTILE

Hàm cuối cùng là hàm NTILE. Đây là hàm được sử dụng để phá vỡ tập hợp bản ghi trong một số cụ thể của các nhóm. Hàm NTILE cũng sử dụng cú pháp như các hàm ranking khác.
Trong ví dụ đầu của hàm này, tôi sẽ nhóm các bản ghi trong bảng Person thành 3 nhóm khác nhau. Tôi muốn các nhóm này dựa trên cột Age. Để làm được điều này, tôi sẽ chạy T-SQL sau:

 

SELECT FirstName, 
       Age, 
       NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
  FROM Person

Đây là tập hợp kết quả của tôi từ câu lệnh T-SQL trên:

 

FirstName  Age         Age Groups
---------- ----------- --------------------
Larry      5           1
Doris      6           1
George     6           1
Mary       11          1
Sherry     11          2
Sam        17          2
Ted        23          2
Marty      23          2
Sue        29          3
Frank      38          3
John       40          3

Trong tập hợp kết quả đã có ở trên với 3 nhóm Age khác nhau. Nhóm đầu tiên bắt đầu từ 5 đến 11 tuổi, nhóm thứ 2 bắt đầu từ 11 đến 23 và nhóm cuối cùng là từ 29 đến 40. Hàm NTILE chỉ có tác dụng chia đều số lượng các bản ghi và đưa vào từng nhóm số. Sử dụng hàm NTILE cho từng bản ghi trong một nhóm sẽ đưa gia các xếp loại giống nhau.

Hàm NTILE là một hàm rất có ích nếu bạn chỉ muốn trả lại một nhóm cụ thể trong các bản ghi. Dưới đây là một ví dụ khi tôi muốn trả lại chỉ nhóm người có độ tuổi chung bình (Nhóm Age 2) từ ví dụ trên.

 

SELECT FirstName,
       Age, 
       Age AS [Age Group]
FROM ( SELECT FirstName, 
              Age, 
              NTILE(3) OVER (ORDER BY Age) AS AgeGroup
        FROM Person) A
WHERE AgeGroup = 2

Kết quả của câu lệnh trên:

FirstName  Age         Age Group
---------- ----------- -----------
Sherry     11          11
Sam        17          17
Ted        23          23
Marty      23          23

 

Kết luận

Mã hóa một quy trình sắp xếp các số tuần tự trong tập hợp bản ghi được sử dụng để lấy một số trong các dòng của mã. SQL Server 2005 đã đưa ra một vài hàm ranking mới. Hy vong trong thời gian tới bạn sẽ cần xếp loại cho các tập hợp bản ghi của mình và một trong các hàm đã được giới thiệu trong bài sẽ giúp bạn hoàn thành công việc đó, nó là một việc hoàn toàn đơn giản.