--- SQL code according to a classical field, the largest group to take (small) value of the data row

2011-07-11  来源:本站原创  分类:Database  人气:110 

- Grouping by a field to take the largest (small) value of data row
- (Aisingiorro. Yuk Wah (eighteen years of wind and rain, snow lotus flowers keep the tip too) 2007-10-23 in Hangzhou, Zhejiang)
/ *
As follows:
name val memo
a 2 a2 (a second value)
a 1 a1 - a first value
a 3 a3: a third value
b 1 b1 - b of the first value
b 3 b3: b of the third value
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
* /
- Create table and insert data:
create table tb (name varchar (10), val int, memo varchar (20))
insert into tb values ​​('a', 2, 'a2 (a second value)')
insert into tb values ​​('a', 1, 'a1 - a first value')
insert into tb values ​​('a', 3, 'a3: a third value')
insert into tb values ​​('b', 1, 'b1 - b of the first value')
insert into tb values ​​('b', 3, 'b3: b of the third value')
insert into tb values ​​('b', 2, 'b2b2b2b2')
insert into tb values ​​('b', 4, 'b4b4')
insert into tb values ​​('b', 5, 'b5b5b5b5b5')
go

- A group by name to take the maximum value val row of data.
- Method 1:
select a. * from tb a where val = (select max (val) from tb where name = a.name) order by a.name
- Method 2:
select a. * from tb a where not exists (select 1 from tb where name = a.name and val> a.val)
- Method 3:
select a. * from tb a, (select name, max (val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
- Method 4:
select a. * from tb a inner join (select name, max (val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
- Method 5
select a. * from tb a where 1> (select count (*) from tb where name = a.name and val> a.val) order by a.name
/ *
name val memo
---------- ----------- --------------------
a 3 a3: a third value
b 5 b5b5b5b5b5
* /

- Second, grouped by name to take the smallest value val row of data.
- Method 1:
select a. * from tb a where val = (select min (val) from tb where name = a.name) order by a.name
- Method 2:
select a. * from tb a where not exists (select 1 from tb where name = a.name and val <a.val)
- Method 3:
select a. * from tb a, (select name, min (val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
- Method 4:
select a. * from tb a inner join (select name, min (val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
- Method 5
select a. * from tb a where 1> (select count (*) from tb where name = a.name and val <a.val) order by a.name
/ *
name val memo
---------- ----------- --------------------
a 1 a1 - a first value
b 1 b1 - b of the first value
* /

- Third, the group by name to take the first occurrence of the line where the data.
select a. * from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/ *
name val memo
---------- ----------- --------------------
a 2 a2 (a second value)
b 1 b1 - b of the first value
* /

- Fourth, according to a randomly selected group name data.
select a. * from tb a where val = (select top 1 val from tb where name = a.name order by newid ()) order by a.name
/ *
name val memo
---------- ----------- --------------------
a 1 a1 - a first value
b 5 b5b5b5b5b5
* /

- Five, grouped by name to take a minimum of two (N-) val
select a. * from tb a where 2> (select count (*) from tb where name = a.name and val <a.val) order by a.name, a.val
select a. * from tb a where val in (select top 2 val from tb where name = a.name order by val) order by a.name, a.val
select a. * from tb a where exists (select count (*) from tb where name = a.name and val <a.val having Count (*) <2) order by a.name
/ *
name val memo
---------- ----------- --------------------
a 1 a1 - a first value
a 2 a2 (a second value)
b 1 b1 - b of the first value
b 2 b2b2b2b2
* /

- Six, according to the name to take the two largest groups (N a) val
select a. * from tb a where 2> (select count (*) from tb where name = a.name and val> a.val) order by a.name, a.val
select a. * from tb a where val in (select top 2 val from tb where name = a.name order by val desc) order by a.name, a.val
select a. * from tb a where exists (select count (*) from tb where name = a.name and val> a.val having Count (*) <2) order by a.name
/ *
name val memo
---------- ----------- --------------------
a 2 a2 (a second value)
a 3 a3: a third value
b 4 b4b4
b 5 b5b5b5b5b5
* /
- Seven, if the entire row of data duplication, all of the columns are the same.
/ *
As follows:
name val memo
a 2 a2 (a second value)
a 1 a1 - a first value
a 1 a1 - a first value
a 3 a3: a third value
a 3 a3: a third value
b 1 b1 - b of the first value
b 3 b3: b of the third value
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
* /
- In sql server 2000 can only be resolved with a temporary table to generate a self-add, first take the maximum or minimum of val, and then added to get through from the data.
- Create table and insert data:
create table tb (name varchar (10), val int, memo varchar (20))
insert into tb values ​​('a', 2, 'a2 (a second value)')
insert into tb values ​​('a', 1, 'a1 - a first value')
insert into tb values ​​('a', 1, 'a1 - a first value')
insert into tb values ​​('a', 3, 'a3: a third value')
insert into tb values ​​('a', 3, 'a3: a third value')
insert into tb values ​​('b', 1, 'b1 - b of the first value')
insert into tb values ​​('b', 3, 'b3: b of the third value')
insert into tb values ​​('b', 2, 'b2b2b2b2')
insert into tb values ​​('b', 4, 'b4b4')
insert into tb values ​​('b', 5, 'b5b5b5b5b5')
go

select *, px = identity (int, 1,1) into tmp from tb

select m.name, m.val, m.memo from
(
select t. * from tmp t where val = (select min (val) from tmp where name = t.name)
) M where px = (select min (px) from
(
select t. * from tmp t where val = (select min (val) from tmp where name = t.name)
) N where n.name = m.name)

drop table tb, tmp

/ *
name val memo
---------- ----------- --------------------
a 1 a1 - a first value
b 1 b1 - b of the first value

(2 rows affected)
* /
- In sql server 2005 can use the row_number function without the use of temporary tables.
- Create table and insert data:
create table tb (name varchar (10), val int, memo varchar (20))
insert into tb values ​​('a', 2, 'a2 (a second value)')
insert into tb values ​​('a', 1, 'a1 - a first value')
insert into tb values ​​('a', 1, 'a1 - a first value')
insert into tb values ​​('a', 3, 'a3: a third value')
insert into tb values ​​('a', 3, 'a3: a third value')
insert into tb values ​​('b', 1, 'b1 - b of the first value')
insert into tb values ​​('b', 3, 'b3: b of the third value')
insert into tb values ​​('b', 2, 'b2b2b2b2')
insert into tb values ​​('b', 4, 'b4b4')
insert into tb values ​​('b', 5, 'b5b5b5b5b5')
go

select m.name, m.val, m.memo from
(
select *, px = row_number () over (order by name, val) from tb
) M where px = (select min (px) from
(
select *, px = row_number () over (order by name, val) from tb
) N where n.name = m.name)

drop table tb

/ *
name val memo
---------- ----------- --------------------
a 1 a1 - a first value
b 1 b1 - b of the first value

(2 rows affected)
* /

<script type="text/javascript"> </ script>

相关文章
  • --- SQL code according to a classical field, the largest group to take (small) value of the data row 2011-07-11

    - Grouping by a field to take the largest (small) value of data row - (Aisingiorro. Yuk Wah (eighteen years of wind and rain, snow lotus flowers keep the tip too) 2007-10-23 in Hangzhou, Zhejiang) / * As follows: name val memo a 2 a2 (a second value)

  • DB2 Database SQL code optimization based 2011-08-08

    Introduction When used to ensure that IBM DB2 ® Universal Database ™ (DB2 UDB) and Borland ® tools (such as Delphi ™, C + + Builder ™, or Kylix ™) to build enterprise applications with optimal performance, the programmer can use the DB2 optimizer abi

  • Change PL / SQL code Set 2010-09-20

    In the implementation of an external sql file, if the pl / sql character set will not cause the problem to import data of Chinese garbage. Change the pl / sql code set as follows: 1. The operation of the implementation of regedit, open the registry 2

  • Add to delete SQL statements used to modify the field 2011-07-28

    Add to delete SQL statements used to modify the field 1. Increase the field alter table docdsp add dspcode char (200) 2 Remove the field ALTER TABLE table_NAME DROP COLUMN column_NAME 3. Modify field types ALTER TABLE table_name ALTER COLUMN column_n

  • Change the PL / SQL code set 2010-09-20

    In the implementation of external sql file, if the pl / sql character set does not import the data will result in Chinese garbled. Change the pl / sql code set as follows: 1 run run regedit, open the registry 2 items found NLS_LANG, change the corres

  • Using pl / sql software ORACEL automatically set up field 2010-10-15

    First create a table TB_CARMESSAGE Which code for the automatic growth of the field to Then create a sequence Figure Enter the following data S_COUNTRY name for the sequence Then create a trigger, aimed at inserting the data automatically inserted be

  • SQL code performance optimization 2011-05-19

    SQL code performance optimization in traditional database optimization theory, there are some controversial issues, such as: Traditionally, the database through the use of indexes on tables will be able to walk faster, but in the actual database syst

  • Sql sentence two commonly used classical 2010-05-13

    1. Description : To copy a table ( Only the structure, the source table name :a The new table name :b) (Access Available ) Method one :select * into b from a where 1 <> 1 Method two :select top 0 * into b from a 2. Description : Copy table ( Copy th

  • After installing Oracle, frequently used SQL code to modify the table space 2010-10-10

    Objectives: Rollback segment is too small for the system, is intended to generate a new rollback segments, Establishment of large, new table space (table space, index table space, table space for rollback, temporary table space) Built two data table

  • sql server query based on date field 2010-11-08

    select * from tableName where datediff (day, dateField, getdate ()) = 4 dateField tableName the table is the date of a field in the name of the sql statement to query the records from the four-day today, as today if it was 11-08, the query is dateFie

  • [Transfer] mysql command-line commands and SQL statements, MySQL modify delete add field type, field data, etc. .. 2011-08-23

    I. Common mysql command-line command 1 Start service net start mysql MYSQL Stop service net stop mysql MYSQL 2. Netstat-na | findstr 3306 view was listening port, findstr to find the back of the port for the existence of 3. MYSQL landing on the comma

  • [Reserved] sql statement, how to replace the field in the single quotes 2010-10-12

    How to replace the field in the single quotes, as follows: update google set title = replace (title ,'''','')

  • SQL increase. Deleted. Change the table field names 2010-10-31

    1. To the table, add new fields alter table table_name add column_name varchar2 (20) not null 2. Delete a field in the table delete table table_name column column_name 3. Change a field name in the table alter table table_name rename column oldname t

  • SQL statement to add remove modify field 2011-05-09

    Increase the field ALTER TABLE table_NAME ADD COLUMN_NAME data_type; Delete field ALTER TABLE table_NAME DROP COLUMN column_NAME; Modify the field type ALTER TABLE table_name MODIFY COLUMN_NAME new_data_type;

  • [Reserved] sql statement, how to replace the field in single quotes 2010-10-12

    How to replace the field in single quotes, as follows: update google set title = replace (title ,'''','')

  • Oracle Sql modify database tables in a field can be empty 2010-11-20

    Because of the need to modify an existing table column does not allow for the change for Null Null, found online ALTER TABLE table1 ALTER COLUMN [name] varchar (60) NULL; Does not apply to Oracle, Oracle should use the following form: ALTER TABLE TAB

  • SQL code to connect remote sql server 2000 server solution 2011-02-20

    Ping the server IP to see whether the ping. This fact is to look at and remote sql server 2000 server physical connection exists. If not, please check your network, view the configuration, of course, have to ensure that the remote sql server 2000 ser

  • SQL code block into the test data 2011-02-08

    /****************** T_MESSAGE_INFO **********************/ declare i NUMBER; v_s varchar2 (50); v_phone_number VARCHAR2 (21); v_date DATE; v_rows NUMBER; START_DATE DATE; Used_time VARCHAR2 (50); begin execute immediate 'truncate table T_MESSAGE_INFO

  • Oracle PL / SQL Code Library 2011-02-17

    http://psoug.org/

  • SQL statement to determine whether the field is empty 2011-02-25

    select * from LAWPEOPLE where retirement is null and name = 'Kang Xiaoguo'; Query results are select * from LAWPEOPLE where retirement = null and name = 'Kang Xiaoguo'; No query results