问使用sql搜索关键字的最佳实践

EN

[USER] --Master user table 
[KEYWORD] --Master keyword table  
[USER_KEYWORD] --[USER]-[KEYWORD] mapping table 

GO
--master user table
CREATE TABLE [USER]
(
    [USERID] INT IDENTITY,
    [NAME] VARCHAR(50)
)
GO

--master keyword table
CREATE TABLE [KEYWORD]
(
    [KEYWORDID] INT IDENTITY,
    [KEYWORD] VARCHAR(50)
)

GO
--x table user_keyword
CREATE TABLE [USER_KEYWORD]
(
    [USERID] INT ,
    [KEYWORDID] INT
)

GO
--Insert data in master user table
INSERT INTO [USER]
SELECT 'TESTUSER1'
UNION ALL
SELECT 'TESTUSER2'
UNION ALL
SELECT 'TESTUSER3'
UNION ALL
SELECT 'TESTUSER4'

GO
--Insert data in master keyword table
INSERT INTO [KEYWORD]
SELECT 'ASP'
UNION ALL
SELECT 'ASP.NET 3.5'
UNION ALL
SELECT 'C#'
UNION ALL
SELECT 'JAVA'
UNION ALL
SELECT 'ASP.NET'
UNION ALL
SELECT 'SQL'
UNION ALL
SELECT 'SQL SERVER'
UNION ALL
SELECT 'SQL SERVER 2005'
UNION ALL
SELECT 'SQL SERVER 2008'

GO

--Insert data in user keyword table

INSERT INTO [USER_KEYWORD]
SELECT 1,1
UNION ALL
SELECT 2,2
UNION ALL
SELECT 3,3
UNION ALL
SELECT 4,4
UNION ALL
SELECT 1,2
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 4,1
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 4,6
UNION ALL
SELECT 3,6
UNION ALL
SELECT 4,6
UNION ALL
SELECT 2,7
UNION ALL
SELECT 3,8
UNION ALL
SELECT 4,9

GO


CREATE PROC TEST_SEARCH_KEYWORDS
@SEARCHKEYWORD VARCHAR(50)
AS
BEGIN

    SELECT K.[KEYWORD],COUNT(UK.[KEYWORDID]) AS [KEWWORDCOUNT] FROM [KEYWORD] K 
    INNER JOIN [USER_KEYWORD] UK
    ON K.[KEYWORDID]=UK.[KEYWORDID]
    WHERE K.[KEYWORD] LIKE (@SEARCHKEYWORD+ '%')
    GROUP BY K.[KEYWORD]

END


--TEST EXAMPLES

EXEC  TEST_SEARCH_KEYWORDS 'ASP'
--0UTPUT
KEYWORD                                            KEWWORDCOUNT
-------------------------------------------------- ------------
ASP                                                2
ASP.NET 3.5                                        2


--TEST EXAMPLES

EXEC  TEST_SEARCH_KEYWORDS 'SQL'
--0UTPUT
KEYWORD                                            KEWWORDCOUNT
-------------------------------------------------- ------------
SQL                                                3
SQL SERVER                                         1
SQL SERVER 2005                                    1
SQL SERVER 2008                                    1