Student: table ID, author name, class
Thesis table: ID, author name, paper title, name of magazine, corresponding author (there is more than one corresponding author write together, is for the students in the table)


How to use the SQL query (EDIT1. TEXT) a class all of the author's name and corresponding author all paper data

CodePudding user response:

Select paper table. * from the student table, paper table
Where students table. The class=EDIT1. TEXT and students table. The author table name=paper. Author's last name
Don't know your condition "all the names of the authors, and corresponding author of all paper data" is the above conditions? If you must, and corresponding author is consistent, plus a "and the student table. Author name=paper table. Corresponding author", "
Something like this, look at the right

CodePudding user response:

Not like that, many in the corresponding author is several names together, such as "king zhang four Li Er", so the corresponding author of this fuzzy query, too, the class there are fuzzy query

CodePudding user response:

Conditions:
Charindex (students table. The author's last name, paper table. The author's last name, 0) & gt; 0

CodePudding user response:

Select paper table. * from the student table, paper table
Where Charindex (EDIT1. The TEXT, the student table. Class, 0) & gt; 0 and students table. The author table name=paper. The author's name and Charindex (students table. The author's last name, paper table. The corresponding author, 0) & gt; 0
If you try this

CodePudding user response:

Oh, my database is SQl Server 2008 R2, different database can judge the substring function, only for reference

CodePudding user response:

It is recommended to use like statements,

The first step, table by student, get "author's last name," such as "king zhang four Li Er
"Every two steps, "zhang SAN king four Li Er" into a single name (if you save the data, after the name of each individual with a ", "characters, better points)
For example: S1, s2, s3 respectively save decomposition name
Step 3: use the fuzzy search Link:
'select * from paper table where (author name like' + # 39 + '%' + S1 + '%' + # 39 +) or (corresponding author like '+ # 39 +' % '+ S1 +' % '+ # 39 +') or '+
'(author name like' + # 39 + '%' + S2 + '%' + # 39 +) or (corresponding author like '+ # 39 +' % '+ S2 +' % '+ # 39 +') or '+
'(author name like' + # 39 + '%' + S3 + '%' + # 39 +) or (corresponding author like '+ # 39 +' % '+ S3 +' % '+ # 39 +') '.

CodePudding user response:

In SQL query (EDIT1. TEXT) a class all of the author's name and corresponding author all paper data, on the premise of not willing to reorganize the data, can only use a cursor, according to the class information, the specified class name information to make a cursor, one by one to paper sheets, use SQL statements into a temporary table upstairs (remove the S1 substitute swim values drop, S2, S3, best in a temporary table to add a query name field, according to the late for filtering), should be able to solve.

CodePudding user response:

The original poster is the corresponding author is accumulative string, multiple name is a string of fuzzy matching problem, the author name should be a person, it is necessary to so complicated?
Looks like amateurs and professionals train of thought is not the same ah, also don't know why to do so, ha ha

CodePudding user response:

to use the likeSelect a. *, b. * from inner paper list a joing student table b on a. author name=b. author name
Where a. Corresponding author like '% % edit. The text'