Remove duplicate data by day. If the value is 0, 0 is used. Otherwise, the maximum value is used. If the value is 0

Source: Internet
Author: User

Remove duplicate data by day. If the value is 0, 0 is used. Otherwise, the maximum value is used. If the value is 0

Test data: mysql> select * from t2; + ---- + -------- + --------------------- + ---------- + | id | userid | inputDate | infoStatus | + ---- + -------- + --------------------- + ---------- + | 1 | 1 | 00:00:00 | 20013 | 2 | 1 | 00:00:00 | 0 | 3 | 2 | 00:00:11 | 20015 | 4 | 2 | 00:00:22 | 20013 | 5 | 2 | 00:00:00 | 0 | 6 | 2 | 00:00:00 | 20013 | 7 | 2 | 00:00:00 | 0 | 8 | 2 | 00:00:00 | 0 | 9 | 2 | 00:00:00 | 20013 | 10 | 2 | 00:00:00 | 0 | 11 | 2 | 00:00:00 | 0 | + ---- + -------- + ----------------------- + ------------ + query SQL: select id, userid, date (inputDate) as date, max (infoStatus) as m, min (infoStatus) as I, if (min (infoStatus) = 0, max (infoStatus )) as max from t2 where infoStatus = 0 or infoStatus <= 99999 group by date; receipt result: + ---- + -------- + ------------ + ------- + | id | userid | date | m | I | max | + ---- + -------- + ------------ + ------- + | 1 | 1 | 2014-07-11 | 20013 | 0 | 0 | 3 | 2 | 2014-07-12 | 20015 | 20013 | 20015 | 9 | 2 | 20013 | 0 | 0 | 5 | 2 | 2014-07-14 | 0 | 0 | 0 | 6 | 2 | 2014-07-15 | 20013 | 0 | 0 | 8 | 2 | 2014-07-16 | 0 | 0 | 0 | + ---- + -------- + ------------ + ------- + my idea is to group by time, at the same time, the query is the smallest, followed by the largest. If the smallest is 0, then the max field is 0; otherwise, the largest one is obtained.

 


Remove duplicate excel Data. Only one value is retained. can I remove 0 by the way? Using the formula

If the data is in A1: A1000, B2.
= T (INDEX (A: A, MATCH (, 0/IF (COUNTIF (B $1: B1, A $1: A $1000) = 0, ROW (A $1: A $1000 )),)))
At the same time, press CTRL + SHIFT + ENTER to ENTER the array formula, and then use the auto-fill handle to copy the formula down.

Filter data in excel. do not duplicate data, but take the maximum value in the second column of repeated data.

Change the date format, replace "." with "-" with the format of yyyy-mm-dd, and sort the data in descending order of the date. Add A secondary column and enter the formula COUNTIF ($ A $1: A1, A1). Column A is the number. If the formula is equal to 1, it indicates that no duplicates or duplicate numbers correspond to the maximum time.

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.