Oracle Beginners take only one record per field and count the number of repeating bars

In the normal development, to repeat the data often used to, I novice, contact Oracle also soon, the development of small knowledge points, record, the birds can bypass, if there is wrong, please correct me.

To duplicate records can use distinct, when querying only a column of data, you can easily remove duplicate data, when querying multiple columns of data, if there is a column of data is not the same, distinct that the data is not the same, that is, the data will not be merged, it is similar to the group by The result of a write segment is the same, and the result may not be what we want at this point. The following is the number of rows to repeat and sum up when querying multiple columns.

As a different link query, the results are as follows:

1 Select 2 RowNum, 3  4z.adsljdsbmc,5z.glwxtgdbh,6sysdate7  from leftjoins  rebase_dzgd D8 on Z.GLWXTGDBH =D.DJBH

And if it's written

SelectROWNUM,Z.ZDBH, Z.adsljdsbmc,z.glwxtgdbh,sysdate fromREBASE_ZTGD Z Left JoinREBASE_DZGD D onZ.glwxtgdbh=D.DJBHwhereZ.glwxtgdbh is  not NULL   andZ.idinch(Select min(ID) fromREBASE_ZTGD bGroup  byB.GLWXTGDBH)Order  byZ.iddesc;

Repeat data only takes one, the key code is here:

Z.id in (the Select min (id) from REBASE_ZTGD b  Group by B.GLWXTGDBH) This sentence is from the repeated

The Glwxtgdbh field is grouped and then the record with the minimum ID is taken out, of course, the inside is not necessarily the min, or you can use Max

Here's how to record the number of duplicate records:
First look at the effect of this line of SQL statements:

Select  Over (order by from REBASE_ZTGD Z;

In fact, using the row_number () function, sort by ID, generate a column of contiguous numbered columns X

Look at the following line of SQL statements:

Select  Over  by Order  by  from REBASE_ZTGD z;

This means, with the row_number () function with the over aggregate function, on a single row glwxtgdbh

Sort by ID in grouped groupings:

Then look at the results of the above two subtraction:

Select  Over (orderby-throughorder by REBASE_ZTGD Z

The approximate meaning is the ID sort value minus GLWXTGDBH

Intra-group ID sort value = sorted value with continuous same value

You can then use the Count function to calculate the number of bars per group.

SelectMax(ZDBH),Count(*),Max(ADSLJDSBMC) power-saving device name,Max(GLWXTGDBH) Related work orders, Sysdate from(SelectZ.id,z.glwxtgdbh,z.zdbh,z.adsljdsbmc,row_number () Over(Order  byZ.id)-Row_number () Over(Partition byZ.glwxtgdbhOrder  byz.id) x fromREBASE_ZTGD Z Left JoinREBASE_DZGD D onZ.glwxtgdbh=d.djbh)whereGlwxtgdbh is  not NULLGroup  byxOrder  by min(ID)

I want to merge into this kind of

Then combine the two columns to do the following:

SelectTo_char (rownum) serial number, T.*  from(Select Case  when Count(*)=1  Then Max(ZDBH)||'have a'||Count(*)||'Zhang' Else Max(ZDBH)||'wait'||Count(*)||'Zhang' Endmain order number,Max(ADSLJDSBMC) power-saving device name,Max(GLWXTGDBH) Related work orders, Sysdate from(SelectZ.id,z.glwxtgdbh,z.zdbh,z.adsljdsbmc,row_number () Over(Order  byZ.id)-Row_number () Over(Partition byZ.glwxtgdbhOrder  byz.id) x fromREBASE_ZTGD Z Left JoinREBASE_DZGD D onZ.glwxtgdbh=d.djbh)whereGlwxtgdbh is  not NULLGroup  byxOrder  by min(ID)) TUnion  AllSelect 'Total'Serial number' -',"',"', Sysdate fromDual

The effect is as follows:

So it's done.

Get Duplicate data available:

Select *  from where inch Select  from Group  by  having Count > 1 )

Oracle Beginners take only one record per field and count the number of repeating bars