There is a field keywords in the database table TBL1. It is nVARCHAR type, the length is 1000, the content of this field is the keyword ID of the papers to be analyzed ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????? keywords -------------------------------------------- --------------- 1 ???????????????????????????? kW1; kW2; kW32 ???? ?????????????????????? kW2; kW33 ????????????????? kW3 KW1; KW4
Question 1. For any of all keywords in the KeyWords field (in the top, the keyword set is {KW1, KW2, KW3, KW4}), the number of times it appears (that is, the keyword is included) The number of records of records is then written to another table. The final effect is the keywords ?????? count ------------------------ KW1 ???????????? ? 2kw2 ????????????? 2kw3 ????????????????????? 1
Question 2. On this basis, a combined query is made. That is to say, two keywords are randed in the entire keyword collection, and they count the number of times in the database table record. For the top question, the last effect is: keywords ???????????? count ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------ kW1; kW2 ???????????????????????????????????? 2KW1; kW4 ????? ??????????? 1kw2; kW3 ????????????????????????????????????? 0kw3; kW4? ?????????????? 1
-------------------------------------------------- ------------------------------------
- Statistical example
- For statistical processing specifically made sequence table Select Top 1000 ID = Identity (int, 1, 1) INTO sequence table from syscolumns a, syscolumns baling TABLE Table Add constraint PK_ID_ 数 Table Primary Key (ID) GO
- Example Data Create Table TBL1 (ID INT, Keywords NVARCHAR (1000)) Insert TBL1 SELECT 1, 'KW1; KW2; KW3'Union? All? SELECT 2,' KW2; KW3'Union? All? Select 3, 'KW3 KW1; KW4'go
- The first statistics (count) SELECT keyword = substring (A.KeyWords, B.ID, Charindex (';', A.Keywords ';', B.ID) -b.id)?, [Count] = Count (Distinct A.ID) from TBL1 A, Sequence Table Bwhere B.ID <= LEN (A.KEYWORDS)? And Substring (';' A.Keywords, B.ID, 1) = ';' Group by Substring (A.KeyWords, B.ID, Charindex (';', A.Keywords ';', B.ID) -b.id) GO
- Second statistics (combination statistics) Select keyword = Substring (A.Keywords, B.ID, Charindex (';', A.Keywords ';', B.ID) -b.id)?, [Count] = Count (Distinct A.ID), A.IDINTO #TFROM TBL1 A, Sequence Table Bwhere B.ID <= LEN (A.KEYWORDS)? And Substring (';' A.Keywords, B.ID, 1) = ';' Group by Substring (A.Keywords, B.ID, Charindex (';', A.Keywords ';', B.ID) -b.id), A. IDSELECT Keyword = A.Keyword ';' B.keyword, [count] = sum (case a.id when b. 1 else 0 end) from # ta, # t bwhere a.keywordgroup by A.Keyword, B.KeywordOrder by keyword
Drop Table #TGO
- Delete Test Environment DROP TBL1, Sequence Table
/*--Test Results
- Statistics 1keyword ??? count ?? ---------- -------- KW1 ??????? 2kw2 ????? ?? 3kw4 ??????? 1
(The number of rows affects is 4 lines)
- Statistics 2Keyword ???????????????? count ????? ---------------------------------------------------------------------------------------------------------------------------- ---------- KW1; KW2 ??????????????????????????????????????? 2KW1; KW4 ???????????????? 1kw2; kW3 ??????????????????????????? ????? 0kw3; kW4 ???????????????? 1
(The number of rows affects is 6 lines) - * /
