目前分類：MS-SQL (4)

瀏覽方式： 標題列表 簡短摘要

        在網路上看到這個問題，寫起來還挺有趣的，所以記錄一下，避免老了腦袋不靈光，有興趣的朋友不如也來考考自己。

問題如下：

有一個 Table 記載著每位學生每科得的等第，等第由0到5

CREATE TABLE #Test (
    STID INT, Math INT, Social INT, 
    Nature INT, English INT, Chinese INT, 
    Music INT, Physic INT, Work INT,
)
go
INSERT INTO #Test 
VALUES
    (9001, 5, 3, 3, 1, 0, 4, 1, 2),
    (9002, 0, 2, 3, 5, 1, 4, 5, 1),
    (9003, 3, 3, 4, 4, 4, 4, 2, 4),
    (9004, 2, 4, 4, 3, 5, 2, 5, 4),
    (9005, 4, 3, 1, 2, 4, 2, 5, 1),
    (9006, 4, 3, 5, 3, 5, 2, 3, 5),
    (9007, 0, 4, 1, 5, 0, 2, 5, 2),
    (9008, 3, 5, 3, 1, 5, 4, 3, 5),
    (9009, 3, 5, 5, 4, 4, 1, 3, 4),
    (9010, 3, 4, 3, 2, 4, 0, 3, 0),
    (9011, 5, 1, 1, 4, 3, 0, 0, 3),
    (9012, 2, 2, 1, 3, 1, 1, 5, 2),
    (9013, 4, 1, 4, 4, 3, 0, 0, 4),
    (9014, 3, 3, 5, 0, 5, 2, 1, 1),
    (9015, 2, 4, 0, 5, 3, 1, 0, 1)
go


試計算各科每個等第的人數，如下表

Level Math Social Nature English Chinese Music Physic Work
0 2 0 1 1 2 3 3 1
1 0 2 4 1 2 3 1 4
2 3 1 0 2 0 5 1 2
3 4 3 2 3 3 0 4 1
4 3 3 1 3 3 1 0 2
5 1 2 3 2 3 0 2 2

 

 

 

 

 

 

 

 

以下是我的三腳貓解法

  

CREATE TABLE #list(Seq INT)
INSERT INTO #list VALUES(0),(1),(2),(3),(4),(5)
go 
SELECT  Seq AS [Level],
        SUM(CASE WHEN Math = Seq THEN 1 ELSE 0 END) AS Math, 
        SUM(CASE WHEN Social = Seq THEN 1 ELSE 0 END) AS Social,
        SUM(CASE WHEN Nature = Seq THEN 1 ELSE 0 END) AS Nature,
        SUM(CASE WHEN English = Seq THEN 1 ELSE 0 END) AS English,
        SUM(CASE WHEN Chinese = Seq THEN 1 ELSE 0 END) AS Chinese,
        SUM(CASE WHEN Music = Seq THEN 1 ELSE 0 END) AS Music,
        SUM(CASE WHEN Physic = Seq THEN 1 ELSE 0 END) AS Physic,
        SUM(CASE WHEN Work = Seq THEN 1 ELSE 0 END) AS Work
FROM #Test test
CROSS JOIN #list 
GROUP BY Seq

 

 

 

題外話，這個貼程式碼的 plugin 實在太正點了！

 

[2010/4/21] Hunterpo帥氣的露了一手高招，程式碼如下：

CREATE TABLE #t 
(
    STID INT NOT NULL, 
    Math INT NOT NULL, 
    Social INT NOT NULL, 
    Nature INT NOT NULL, 
    English INT NOT NULL, 
    Chinese INT NOT NULL, 
    Music INT NOT NULL, 
    Physic INT NOT NULL, 
    Work INT NOT NULL
)
GO

INSERT INTO #t VALUES(9001, 5, 3, 3, 1, 0, 4, 1, 2);
INSERT INTO #t VALUES(9002, 0, 2, 3, 5, 1, 4, 5, 1);
INSERT INTO #t VALUES(9003, 3, 3, 4, 4, 4, 4, 2, 4);
INSERT INTO #t VALUES(9004, 2, 4, 4, 3, 5, 2, 5, 4);
INSERT INTO #t VALUES(9005, 4, 3, 1, 2, 4, 2, 5, 1);
INSERT INTO #t VALUES(9006, 4, 3, 5, 3, 5, 2, 3, 5);
INSERT INTO #t VALUES(9007, 0, 4, 1, 5, 0, 2, 5, 2);
INSERT INTO #t VALUES(9008, 3, 5, 3, 1, 5, 4, 3, 5);
INSERT INTO #t VALUES(9009, 3, 5, 5, 4, 4, 1, 3, 4);
INSERT INTO #t VALUES(9010, 3, 4, 3, 2, 4, 0, 3, 0);
INSERT INTO #t VALUES(9011, 5, 1, 1, 4, 3, 0, 0, 3);
INSERT INTO #t VALUES(9012, 2, 2, 1, 3, 1, 1, 5, 2);
INSERT INTO #t VALUES(9013, 4, 1, 4, 4, 3, 0, 0, 4);
INSERT INTO #t VALUES(9014, 3, 3, 5, 0, 5, 2, 1, 1);
INSERT INTO #t VALUES(9015, 2, 4, 0, 5, 3, 1, 0, 1);
GO

-- 利用 CTE
WITH unpvt AS
(
    -- 反轉置
    SELECT STID, [Subject], [Level]
    FROM (
        SELECT STID, Math, Social, Nature, English, Chinese, Music, Physic, Work
        FROM #t
    ) AS t
    UNPIVOT (
        [Level] FOR [Subject] IN 
        (Math, Social, Nature, English, Chinese, Music, Physic, Work)
    ) AS unpvt
), pvt AS (
    -- 轉置
    SELECT [Level], Math, Social, Nature, English, Chinese, Music, Physic, Work
    FROM (
        -- 彙總(計算人數)
        SELECT [Subject], [Level], COUNT(STID) AS Counter
        FROM unpvt
        GROUP BY [Subject], [Level]
    ) AS src
    PIVOT
    (
        SUM(Counter) FOR [Subject] IN
        (Math, Social, Nature, English, Chinese, Music, Physic, Work)
    ) AS pvt
)
SELECT *
FROM pvt;
GO

DROP TABLE #t;
GO

AnferneeHardaway 發表在 痞客邦 留言(0) 人氣()

▲top

    這一段 SQL Statment 是為了套用javascript實做出 Menu 的巡覽列。之前都是在 Code Behind 的地方用遞迴去組 html，這次突然想用 SQL 的 FOR XML 來做看看，於是寫了這段出來。

    本來是想用 CTE 做看看，但仔細想想，CTE 似乎做不到這種感覺，所以最後還是用 FUNCTION 去做遞迴的效果。

    直接執行就可以看到效果了，有興趣的朋友不妨複製下去跑看看！

  

CREATE TABLE [dbo].[Pages](
    PageID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    PageName NVARCHAR(50) NOT NULL DEFAULT '',
    [Action] VARCHAR(50) NOT NULL DEFAULT '',
    Controller VARCHAR(50) NOT NULL DEFAULT '',
    ParentID INT NOT NULL DEFAULT 0,
    Sort TINYINT NOT NULL DEFAULT 1
)
GO 
INSERT INTO dbo.Pages VALUES
('檔案','','',0,1),
('連接物件總管','Connect','File',1,1),
('中斷與物件總管的連接','Disconnect','File',1,2),
('新增','Create','File',1,3),
('專案','Project','File',4,1),
('使用目前的連接查詢','Search','File',4,2),
('編輯','','',0,2),
('復原','Undo','Edit',7,1),
('取消復原','Restore','Edit',7,2),
('檢視','','',0,3),
('物件總管','Explorer','View',10,1),
('查詢','Search','Query',0,4)
GO
CREATE FUNCTION [dbo].[f_GetMenuRecursive]
(
    @parentID INT 
)
RETURNS XML
AS
BEGIN 
    DECLARE @value XML 
    
    IF EXISTS(
        SELECT * 
        FROM dbo.Pages
        WHERE ParentID = @parentID
    )
    BEGIN 
        SELECT @value = (
            SELECT  'nfSubC nfSubS' AS '@class',
                    CONVERT(XML,(
                        SELECT  'nfItem' AS '@class',
                                ISNULL('/' + NULLIF(Controller,'') 
                                + '/' + 
                                NULLIF([Action],''),
                                'javascript:void(0)') 
                                AS 'a/@href',
                                'nfLink' AS 'a/@class',
                                PageName AS a,
                                dbo.f_GetMenuRecursive(PageID)
                        FROM dbo.Pages
                        WHERE ParentID = @parentID
                        ORDER BY Sort
                        FOR xml PATH('li')
                    ) )
            FOR XML PATH('ul')
        )
    END
    
    RETURN (@value)
END 
GO 
CREATE PROCEDURE [dbo].[s_GetMenu] 
 
AS
BEGIN
    SET NOCOUNT ON 
    
    SELECT  'nfItem' AS '@class',
            ISNULL('/' + NULLIF(Controller,'') + '/' + 
            NULLIF([Action],''),'javascript:void(0)') AS 'a/@href',
            'nfLink' AS 'a/@class',
            PageName AS a,
            dbo.f_GetMenuRecursive(PageID)
    FROM dbo.Pages
    WHERE ParentID = 0
    ORDER BY Sort
    FOR xml PATH('li'),ROOT('ul')
END 
GO 
 
/*
    測試 sp 的執行結果
    EXEC [s_GetMenu]
    
    刪除剛建立的資料庫物件
    DROP FUNCTION dbo.[f_GetMenuRecursive]
    DROP PROCEDURE [dbo].[s_GetMenu]
    DROP TABLE [dbo].[Pages]
*/

AnferneeHardaway 發表在 痞客邦 留言(0) 人氣()

▲top

    最近在設定 SQL 2008 的 Linked Server ，設定起來還蠻方便的，三兩下就可以用了，但是如果在 Trigger 中使用就會跳出錯誤：協力電腦異動管理員已經停用了對遠端/網路異動的支援。試了好久，終於搞定了這個問題，只要設定好 DTC 就行了，以下是這十幾個小時以來的心得：

1.開始 -- 控制台 -- 系統管理工具 -- 服務，啟動 Distributed Transaction Coordinator 服務。
2.開始 -- 控制台 -- 系統管理工具 -- 元件服務，展開元件服務 -- 電腦，在「我的電腦」上按右鍵 -- 內容，切到 MSDTC 標籤，按下「安全性設定」，依下圖設定

local-dtc

3.開啟 Windows防火牆，切到例外標籤 -- 新增程式，加入C:\WINNT\system32\msdtc.exe，接著再新增連接埠，開啟 Port 135 。
4.編輯regedt32.exe，我直接把需要編輯的部份寫下來，有需要的朋友可以直接複製以下文字，另存成 reg 檔案即可使用。

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Rpc\Internet]
"Ports"=hex(7):35,00,30,00,30,00,30,00,2d,00,35,00,30,00,32,00,30,00,00,00,00,\
  00
"PortsInternetAvailable"="Y"
"UseInternetPorts"="Y"

設定大致上這樣就完成了，呼叫端跟被呼叫端都必須開啟 DTC 才行。

 

[參考資料]

．http://dotnetmis91.blogspot.com/2008/09/windows-server-2008msdtc.html

．http://support.microsoft.com/?kbid=873160

．http://chattingprogram.blogspot.com/2008/04/sql-2005.html

．http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx

．http://technet.microsoft.com/zh-tw/magazine/2007.07.howitworks.aspx

．http://support.microsoft.com/kb/250367/

．http://itknowledgeexchange.techtarget.com/sql-server/how-to-configure-dtc-on-windows-2008/

AnferneeHardaway 發表在 痞客邦 留言(0) 人氣()

▲top

    最近才發現LIKE語法原來可以用中括號來表示任一種可能性，寫了近三年的T-SQL，到現在才知道有這種好方法。例如下面這張表：

CREATE TABLE #tmp(Names NVARCHAR(10))
GO
INSERT INTO #tmp VALUES(N'阿尼')
INSERT INTO #tmp VALUES(N'路飛')
INSERT INTO #tmp VALUES(N'喬巴')
INSERT INTO #tmp VALUES(N'索隆')
INSERT INTO #tmp VALUES(N'娜美')
INSERT INTO #tmp VALUES(N'香吉士')
INSERT INTO #tmp VALUES(N'烏索普')
INSERT INTO #tmp VALUES(N'羅賓')
INSERT INTO #tmp VALUES(N'佛朗基')
INSERT INTO #tmp VALUES(N'忘記名字的骨頭人')

   

如果我想找出第一個字是阿或者路的話，我之前會這樣寫 


SELECT * FROM #tmp WHERE Names LIKE N'阿%' OR Names LIKE N'路%'

  

但後來發現可以這樣寫 


SELECT * FROM #tmp WHERE Names LIKE N'[阿路]%'

  

語法看起來更簡潔有力，帥！！

AnferneeHardaway 發表在 痞客邦 留言(0) 人氣()

▲top