SQL query result itself to be used two times

Source: Internet
Author: User

First, the question

Query the user all the wrong number to the front-end display, the need to display the wrong number of questions, starting from 1 increments. If you delete the 5th question, move the following question number forward.

Second, analysis

The wrong problem is in the user every time the problem is inserted into the wrong table, or the topic pushes the table "Wrong identification field" Set 1, we do not need to maintain a number in the table, because the number in addition to the display and no other purpose, if maintained in the table will certainly cause a lot of problems.

Query information content is queried and staged using with AS, which is used multiple times in subsequent queries.

Reference is made to the article that with as can be staged with the information indicated below, and then referenced in other locations in the SQL as a result set, so that only one query is made.

 withB as(    SELECT(SELECTD. Content_html fromMic_paper_problems DWHERED. tm_id=pm.tm_id) Questiontitle, (SELECTP. Answer_html fromMic_paper_problems PWHEREP. tm_id=pm.tm_id) Rightans, PM. ID pushid, pm.tm_id QuestionID, PM. User_answer Youranswer frommic_td_exercise_push_problem pm, mic_td_exercise_user_push phWHEREpm.push_id=Ph. ID andPh.user_id =? andPh. subject_id=? andPm.is_correct= 2 Order  byPH. PUSH_TIME,PM. Create_time)SelectRowNum seq, B.*  frombwhereB.pushidinch(Select min(B.pushid) fromBGroup  byB.questionid)

In this way, after Oracle queries the contents of the AS, it is named B, and then executes the bottom select query, where the irrelevant subquery in the Where condition is to ensure that the user only displays one of the wrong questions for the same problem (for example, topic A, the user answered two times, the answer is wrong, Two data will be recorded in the title table, which will only be shown once.

SQL query result itself to be used two times

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.