Query partition groups as required

2010-12-09  来源:本站原创  分类:Database  人气:77 

select a.*
                from (select SCORE_ID,EX_EB_ID,EB_NAME,ss.ADMISSION_TICKET,SCOPE,EXAM_TIME,UPDATE_TIME,
               ei.examinee_id,ei.examinee_name, ei.register_dept,ei.dept_code,ei.register_station, ei.station_code,
               row_number() OVER(partition by dept_code, ei.station_code ORDER BY ss.scope desc) a_rownum
          from TM_STUDENT_SCORE ss
          left join tm_examinee_info ei on ss.admission_ticket =ei.admission_ticket) a
                where a_rownum <= 3

The role of this SQL statement:
Calculated results of the candidates and the candidate data, grouped according to position and draw the top 3 positions,

Difficult to understand the place:
1.row_number () OVER (partition by dept_code, ei.station_code ORDER BY ss.scope desc) a_rownum
Sentence is based on dept_code, ei.station_code groups, according to ss.scope ascending last named: a_rownum

相关文章
  • Query partition groups as required 2010-12-09

    select a.* from (select SCORE_ID,EX_EB_ID,EB_NAME,ss.ADMISSION_TICKET,SCOPE,EXAM_TIME,UPDATE_TIME, ei.examinee_id,ei.examinee_name, ei.register_dept,ei.dept_code,ei.register_station, ei.station_code, row_number() OVER(partition by dept_code, ei.stati

  • ORACLE query partition table 2011-04-12

    If [the partition table] in the field [createdate] on the partition, a partition for the month: June 2009 for the partition P200906 July 2009 for the partition P200907 August 2009 for the partition P200908 . . . Partition key used to execute SQL to r

  • To create a partition table and query partition sql --- Zhao Wangtao 2011-01-21

    select table_name, partition_name from user_tab_partitions where table_name = 'ETL_DAT_WAREHOUSE' alter table ETL_DAT_WAREHOUSE add partition P201101 VALUES LESS THAN (TO_DATE ('2011 / 02/01 ',' YYYY / MM / DD '))

  • Oracle database, the operation method of the partition table 2010-04-15

    In the large-scale enterprise applications or enterprise-level database applications , we must deal with the amount of data can usually reach tens to hundreds of GB, some even go to TB level. Although the storage medium and data processing Jishu's Fa

  • How Oracle Database partition table 2010-06-09

    Article Transfer from: http://xu20cn.blog.51cto.com/274020/90342 Abstract: In a large number of business data processing projects, consider using the system partition table to improve application performance and facilitate data management, This paper

  • Turn - Database partition 2010-06-13

    In the large-scale enterprise applications or enterprise-level database applications, to handle the amount of data can often be the order of tens to hundreds of GB, some even go to TB level. Although the storage medium and data processing technology

  • Oracle database creation and operation of the partition table 2010-08-14

    Abstract: In a large number of business data processing of the project, consider using a partition table to Boost the performance of application systems and facilitate data management, this paper describes the partition table of Xiangxi use. In the l

  • SQL Server 2005 files and file groups 2011-01-06

    1, the meaning of files and file groups and relationships Each database has one primary data file. and a number from the file. The physical embodiment of database file. File group can include distribution of documents across multiple logical partitio

  • Actual partition table: SQL Server 2k5 & 2k8 Series (3) 2011-01-10

    Original works, for permission to reproduce, reprint, please be sure to hyperlink to the article indicating the original source , author information and this statement. Otherwise held liable. http://terryli.blog.51cto.com/704315/169601 By Bowen on th

  • oracle partition table creation method 2011-02-22

    Oracle provides the partitioning technology to support VLDB (Very Large DataBase). Partition table to determine through the partition column, the column partition different records into different partitions. Completely transparent to the application

  • Oracle database partition table methods of operation 2011-04-06

    In the large-scale enterprise applications or enterprise-level database applications, the amount of data to be processed can usually reach tens to hundreds of GB, and some even to the TB level. Although the storage medium and data processing technolo

  • Chapter lock table partition 2011-05-24

    Chapter 3, Oracle locks, table partitioning 1, the technical objectives Understand the concept of lock Use table partitioning 2, the concept of lock Multiple users can simultaneously access the same data, locking the database used to control concurre

  • ORACLE partition table operations (1) 2011-09-07

    ORACLE partition table of the operational applications Abstract: In a large number of business data processing projects, consider using the system partition table to improve application performance and facilitate data management, This paper describes

  • oracle partition table 2010-02-22

    oracle partition table Oracle provides the partitioning technology to support the VLDB (Very Large DataBase). Partition table to judge by partition column to partition out the different records, into a different partition. Completely transparent to t

  • Notes on 'Expert Oracle' - No.13.1: partition - partition of benefits 2010-02-25

    1. To improve the system reliability (HA): do not put all your eggs in one basket a. A bad area, but the data partition B can still be inquiries into b. A bad partition, and restore the partition of the time table than the entire restoration was much

  • About how to achieve Oracle database partition table 2010-04-15

    About how to achieve Oracle database partition table Oracle database Oracle database partition is a performance optimization as an important means and methods to do the project before hand, just listen too zone name, I feel especially mysterious, see

  • Linux partition scheme. File structure and mount 2011-05-16

    Linux partition on an analysis described in recommendations, we must master the following Linux file structure, and then recommend a comprehensive overview of the Linux partition, Linux is a development tool software, editor software, CPU graphical u

  • ORACLE simple partition table. Partition index 2011-04-14

    ORACLE way for the partition table is actually stored in the table section, ordinary table is stored in a segment, and the partition table will be divided into multiple segments, so the search process is the first positioning data based on query posi

  • Oracle's partition technique 2011-08-25

    One, Oracle Partitioning is a partition profile ORACLE handling very large tables, indexes and other technologies. Partition is a "divide and conquer" technique, by taking large tables and indexes into manageable pieces, thus avoiding each table

  • Find duplicate data in the database T-SQL 2010-04-14

    Table stuinfo, there are three fields recno (since increased), stuid, stuname Construction of the table Sql statement as follows: CREATE TABLE [StuInfo] ( [Recno] [int] IDENTITY (1, 1) NOT NULL, [Stuid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NU