The usage of SQL in distinct

2010-06-10  来源:本站原创  分类:Database  人气:162 

When using mysql, and sometimes need to check out a field does not duplicate records, although there are distinct mysql provides the keyword to filter out unwanted duplicate records to retain only one, but often do not use it to return to the Number of duplicate records instead of using it to return all the values are not re-record. The reason is distinct objectives can only be returned to its field, but can not return to other fields, this problem plagued me long, can not be solved with a distinct, I only use two cyclic query to solve, and this amount of data for a very large station is undoubtedly a direct impact on efficiency.

Let's look at the following example:

table
id name
1 a
2 b
3 c
4 c
5 b

Library structure about this, this is a simple example, the situation will be much more complex.

I would like to use a statement such as query by name does not duplicate all the data, it must use distinct remove unnecessary duplication of records.

select distinct name from table
The result is:

name
a
b
c

Seems to achieve the desired effect, and yet, I want to get the id value of it? Query to change about it:

select distinct name, id from table

Result will be:

id name
1 a
2 b
3 c
4 c
5 b

how distinct not working? Role is played, but the role he had two fields, that is, have to have the same id and name will be excluded. . . . . . .

We changed to change the query:

select id, distinct name from table

Unfortunately, in addition to the error message you get nothing, distinct to be placed at the beginning. Difficult to place where conditions can not be distinct in? Energy, still error. . . . . . .

A lot of trouble, right? Indeed, effort failed to solve the problem.

Finally found a use mysql manual with group_concat (distinct name) with the group by name I need to achieve the functionality.

Error .......

Be investigated carefully, group_concat support function is 4.1, halo, I 4.0. No way, upgrade or complete class a try, success. . . . . .

Finally buttoned up, but this way, they must require customers to upgrade the.

Sudden brainwave flash, since you can use group_concat function, that other functions work?

Quickly with a try count function successfully, the original's that simple. . . . . .

Will now release a full statement:

select *, count (distinct name) from table group by name

Results:

id name count (distinct name)
1 a 1
2 b 1
3 c 1

The last one is unnecessary, do not control on the line, aim to achieve. . . . .

Alas, the original mysql so stupid, what took him lightly deceived later, also depressed me, and now out hope that we will not be tossing this issue.

Oh, right, and then by the way, group by order by and limit must be placed before, or will be error.

相关文章
  • The usage of SQL in distinct 2010-06-10

    When using mysql, and sometimes need to check out a field does not duplicate records, although there are distinct mysql provides the keyword to filter out unwanted duplicate records to retain only one, but often do not use it to return to the Number

  • Usage ddsdsds sql error 2010-09-28

    Usage ddsdsds sql error

  • SQL SELECT DISTINCT Statement 2011-08-12

    SQL SELECT DISTINCT Statement In the table may contain duplicate values. This is not a problem, but sometimes you may want to just list the different (distinct) values. Keyword DISTINCT is used to return only different values. Syntax: SELECT DISTINCT

  • Oracle tree query start with ... connect by usage profile & sql directed graph problems looking at the new ... 2010-11-12

    ORACLE is a relational database management system that uses a table in the form of organizing data in some data in the table is also showing a tree structure of the contact. For example, we now discuss the employee information table EMP, which contai

  • SQL COUNT DISTINCT function 2010-03-23

    SELECT COUNT(DISTINCT column(s)) FROM table For example, With this "Orders" Table: This with the "Orders" table: Company OrderNumber Sega 3412 W3Schools 2312 Trio 4678 W3Schools 6798 For example, SELECT COUNT(Company) FROM Orders Resul

  • view table space usage oracle SQL 2010-09-14

    SELECT a.tablespace_name " Table space name ", total The table space size , free Table space remaining size , (total - free) Table space usage size , ROUND((total - free) / total, 4) * 100 " Usage %" FROM (SELECT tablespace_name, SUM(b

  • [Transfer] sql in Distinct.Group by.having.order by Caution 2011-03-18

    SELECT COUNT (*) AS COUNT, REQUEST, METHOD FROM REQUESTMETH GROUP BY REQUEST, METHOD HAVING (REQUEST = 'FC.OCEAN.JOB.SERVER.CBIZOZBKHEADER' OR REQUEST = 'FC.Ocean.Job.Server.CBizOzDocHeader') AND COUNT (*)> 3 ORDER BY REQUEST As the statement notes H

  • SQL中distinct的用法 2011-11-08

    在使用mysql时,有时需要查询出某个字段不重复的记录,虽然mysql提供有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只 用它来返回不重复记录的条数,而不是用它来返回不重记录的所有值.其原因是distinct只能返回它的目标字段,而无法返回其它字段,这个问题让我困扰 了很久,用distinct不能解决的话,我只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的.所以我花了很多 时间来研究这个问题,网上也查不到解决方案,期间把容容拉来帮忙,

  • The usage of SQL in the GROUP BY 2010-04-19

    GROUP BY is a group query, the general GROUP BY and aggregate functions are used in conjunction, you can think of You use a GROUP BY groups by ITEM.ITEMNUM this field, that different contents of other fields into one to many to change how they displa

  • In the usage of SQL EXISTS 2010-05-12

    http://www.cnblogs.com/fredlau/articles/993829.html

  • Common usage of sql statement 2010-06-06

    create table Table 1 as select * from table 2, you can create a table structure with the same table b oracle join queries http://shawnfree.javaeye.com/blog/355837

  • Two rows of data into two mutually exclusive column shows the usage of SQL statements 2010-08-25

    If there is a table, each row of data contains two kinds of information, one is 1, a is 2, in order to make this line of data in two columns show, as a first, second as 2, how to achieve? That you can use the following statement: select decode (tmp.m

  • LTrim, and RTrim in the usage of SQL 2010-09-09

    LTRIM Remove initial space character after the return expression. Grammar LTRIM (character_expression) Parameter character_expression Is the expression of character or binary data. character_expression can be constants, variables or columns. characte

  • Lucene search syntax in the SQL syntax similar to the usage of IN 2010-11-03

    Lucene in today's exploration of a similar usage of SQL syntax IN: ID: (XZXT_009_XZ_XXKW_79563842107502 OR XZXT_009_XZ_XXKW_77144338881602 OR XZXT_009_XZ_XXKW_59998214491402 OR XZXT_009_XZ_XXKW_86645151264302 OR XZXT_009_XZ_XXKW_66009723045802 OR XZX

  • Oracle: SQL * Loader and external tables (External Table) Usage 2011-08-29

    exp: Binary -> Database sqlloader: text -> Database oracle_loader: text -> Database oracle_datapump: Binary -> Database ================================================== ================================= Usage of SQL * Loader: ORACLE SQL * LO

  • Sybase common sql statements 2010-11-26

    Common sql statements 1. View the version of the database select @ @ version 2. See the operating system parameters of the machine where the database resides exec master .. xp_msver 3. View the database startup parameters sp_configure 4. View the dat

  • Sybase sql statement used 2010-11-26

    Common sql statements 1. View the version of the database select @ @ version 2. See the operating system parameters of the machine where the database resides exec master .. xp_msver 3. View the database startup parameters sp_configure 4. View the dat

  • oracle dba Common SQL statements 2011-07-28

    See the table space name and size: SQL> select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name; View the table space of

  • How IN.LIMIT.CONCAT.DISTINCT keywords such as MySQL 2011-06-14

    Record some select tips: 1, select statements can be separated with a carriage return $ Sql ​​= "select * from article where" And $ sql = "select * from article where ", you can get the right result, but sometimes may be able to separa

  • How to use SQL to return all the row between the two dates dates 2010-04-06

    How to use SQL to return all the row between the two dates dates ------------------------------------------- In the level of the query, Oracle introduced a pseudo-column level, used to indicate the current row (node) corresponds to the level, It bega