As shown in the above table, the student and the project two lines can be null, also can have value
1, first check and return priority when students are not empty and project rows of data,
Such as incoming data: xiao li and rope skipping, returns the ID will be the priority for 2
2, when the incoming students name, but the empty project, returning students will be the priority list include the name of the student ID,
Such as incoming data: xiao li, but the empty project, meet the condition is that ID=5
If the incoming data: student is empty, but the project has a value, such as the incoming running, then the return ID=3
3, when the student and the project is empty, all matching of said the two columns, the ID=4
Whether a SQL implementation, data is found to priority to return to comply with two columns, column has a value, check if there is no first students project is empty, if not, check project has value, students is empty;
Finally don't exist, then the two columns for the empty records
CodePudding user response:
The up and down you describe some contradictions, if the incoming students and the project has not empty values, but not completely match the data, at the moment, there is no record found out directly, or take the student field is not empty and record to match search project is empty, if there is still no match data, is that with further students field is empty but the project has the value of the data to match,CodePudding user response:
Priority query two fields have contain data, check a field contains the last check both field is empty, if the two fields is empty does not exist, is returned,CodePudding user response:
SELECT the TOP 1 *The FROM
(SELECT *, 1 AS SEQ FROM TABLE WHERE ', '+ + students',' LIKE '%, A + + @' % 'AND', '+ + project', 'LIKE' %, '+ +', '% @ B
UNION ALL
SELECT *, 2 FROM the TABLE WHERE the ', '+ + students',' LIKE '%, A + + @' % 'AND ISNULL (project,' ')='
UNION ALL
SELECT *, 3 FROM the TABLE WHERE ISNULL (students')='AND', '+ + project', 'LIKE' %, '+ +', '% @ B
UNION ALL
SELECT *, 4 FROM the TABLE WHERE ISNULL (students')='AND ISNULL (project, ")=") AS A
The ORDER BY SEQ