dongliang2058
2014-08-14 18:10
浏览 140
已采纳

如果一列有多个关键字,如何从数据库中获取结果?

Well, I've a table called keywords and there are 2 columns.

1) kid
2) keywords (keyword value)

and I've another table called contact_details where there a column called keyword. In this keyword column I'm inserting many keyword from keywordstable. So 2 tables is look like this...

Keywords table:

kid    keyword
1      php     
1      mysql     
1      html     
1      css     
1      css3     
1      wp     
1      photoshop     
1      3d

contact_details table:

cid    name    phone   keyword
1      alex    123     php, mysql, hmtl
2      alex1   124     php, html, css3
3      alex2   125     wp, html, css
4      alex3   126     photoshop, 3d
5      alex4   127     html, 3d, php
6      alex5   128     mysql, wp, html

Now I've a search box which is searching people (name From contact_details table) by using keyword value. In search box, Search value could be few keywords. I mean it's could be php, mysql, html or could be php, 3d, photoshop.

So My question is : how can I write a Sql query to get the result ? I want to get all name which match the search keyword/s from contact_details table ?

Is there any field need to add in keywords table ? Can not get an IDEA :(

图片转代码服务由CSDN问答提供 功能建议

好吧,我有一个名为 keywords 的表,有2列。

1)kid
2)关键字(关键字值)

我还有另一个名为 contact_details的表其中有一个名为 keyword 的列。 在 keyword 列中,我从 keywords 表中插入了许多 keyword 。 所以2个表看起来像这样......

关键词表:

  kid keyword 
1 php  
1 mysql 
1 html 
1 css 
1 css3 
1 wp 
1 photoshop 
1 3d 
   
 
 

contact_details表:

  cid名称电话关键字
1 alex 123 php,mysql,hmtl 
2 alex1 124 php,html,css3 
3 alex2 125 wp,html,css 
4 alex3 126 photoshop,3d  
5 alex4 127 html,3d,php 
6 alex5 128 mysql,wp,html 
   
 
 

现在我有一个搜索框,搜索人( name from contact_details table)使用关键字value。 在搜索框中,搜索值可能很少。 我的意思是它可能是 php,mysql,html 或者可能是 php,3d,photoshop

所以我的问题是:如何 我可以写一个 Sql查询来获得结果吗? 我想从 contact_details 表中获取与 search keyword / s 匹配的所有名称?

是否需要在 keywords 表中添加任何字段? 无法获得IDEA :(

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douba8758 2014-08-14 18:28
    已采纳
    Select name from contact_details where keyword like '%<search keywords>%'
    

    Like search keyword key php then you need pass php in query and will get list of all name which having keywords as php'

    Select name from contact_details where keyword like '%php%'
    

    hope this will resolve your issue.

    Proper approach, Make kid as primary key in Keyword table

    Keywords table:
    kid    keyword
    1      php     
    2      mysql     
    3      html 
    

    Remove keywords column from contact_details table.

    contact_details table:
    cid    name    phone   
    1      alex    123     
    2      alex1   124     
    3      alex2   125 
    

    Make one more table which having many to many relationship and you need insert the relationship here so that no need touch again keywords and contact_details table again.

    keyword_contact_mapping
    kcid   kid_fk   cid_fk
    1      1        1     
    2      1        2
    3      1        3
    4      2        1    
    5      2        1 
    6      2        2
    7      2        3
    

    Sql query (Not tested you can also use alias)

    select name from contact_details join keyword_contact_mapping on kid_fk =(select kid from Keywords where keyword='php')
    
    打赏 评论
  • duanbing8817 2014-08-14 18:16

    Assuming you have the ID when the name is selected, you can use:

        SELECT Keyword from Keywords_Table
        WHERE ID = <ID>
    
    打赏 评论
  • douzhanhui5662 2014-08-14 19:12
    USE AdventureWorksDW2008R2
    GO
    
    IF OBJECT_ID('Keywords') IS NOT NULL
    BEGIN
        DROP TABLE Keywords
    END
    
    IF OBJECT_ID('Contact_Details') IS NOT NULL
    BEGIN
        DROP TABLE Contact_Details
    END
    
    
    IF OBJECT_ID('Keyword_ContactDetails') IS NOT NULL
    BEGIN
        DROP TABLE Keyword_ContactDetails
    END
    
    /* automate id's with identity. Or, do you want to specify the the keyword id manually? 
    */
    CREATE TABLE Keywords(
        Keywords_ID INT IDENTITY(1,1) NOT NULL
        ,Keyword NVARCHAR(100)
    
        CONSTRAINT PK_Keywords PRIMARY KEY
        (
            Keywords_ID
        )   
    )
      /* You must plan each column data type by careful consideration
         I am using the phone example here to demonstrate different business requirements
         the lenghts and datatype may need to change for localization
      */
    CREATE TABLE Contact_Details
    (
         Contact_Details_ID INT IDENTITY(1,1) NOT NULL
        ,First_Name VARCHAR(100)
        ,Last_Name  VARCHAR(100)
        ,Phone      VARCHAR(10)
        ,Phone_EXT  VARCHAR(3)
        ,Phone_International NVARCHAR(15)
    
        CONSTRAINT PK_Contact_Details PRIMARY KEY
        (
            Contact_Details_ID
        )
    )
    
    CREATE TABLE Keyword_ContactDetails
    (
         Keyword_ID INT
        ,Contact_Details_ID INT
        ,DateTime_Created DATETIME
    
        CONSTRAINT PK_KeywordContact PRIMARY KEY
        (
            Keyword_ID
            ,Contact_Details_ID
        )
    
        /*Enforce referential integrity, 
          prevents adding keywords that don't exist
          prevents deleting a keyword if it is referenced   
        */
        FOREIGN KEY (Keyword_ID) REFERENCES Keywords(Keywords_ID),
        FOREIGN KEY (Contact_Details_ID) REFERENCES Contact_Details(Contact_Details_ID)
    
    )
    
    /* Populate keywords
    */
    
    INSERT INTO Keywords(Keyword) VALUES ('PHP')
    INSERT INTO Keywords(Keyword) VALUES ('MYSQL')
    INSERT INTO Keywords(Keyword) VALUES ('HTML')
    INSERT INTO Keywords(Keyword) VALUES ('CSS')
    
    /* Add contact details
    */
    INSERT INTO Contact_Details(
                    First_Name
                    ,Last_Name
                    ,Phone
                    ,Phone_EXT
                    ,Phone_International)
                VALUES(
                    'Abe'
                    ,'Lincoln'
                    ,'2129996677'
                    ,'123'
                    ,'na')
    
    
    /* Assign PHP Keyword to Abe Lincoln
    */
    DECLARE @keywordID int = 0
            ,@contactDetails int = 0
    
    set @keywordID = (select Keywords_ID from Keywords where Keyword = 'PHP')
    set @contactDetails = (select Contact_Details_ID from Contact_Details where Last_Name = 'Lincoln')
    
    INSERT INTO Keyword_ContactDetails(
                Keyword_ID
                ,Contact_Details_ID
                ,DateTime_Created)
                VALUES(
                @keywordID
                ,@contactDetails
                ,CURRENT_TIMESTAMP)
    
    
    
    SELECT * FROM Contact_Details C
    JOIN Keyword_ContactDetails KC
    ON KC.Contact_Details_ID = C.Contact_Details_ID
    JOIN    Keywords K
    ON K.Keywords_ID = KC.Keyword_ID
    
    打赏 评论

相关推荐 更多相似问题