Mysql Code To Mark New And Duplicate Values

问题: I have a mysql table (shown below) with some values, I need to get the new values and duplicate values I tried SELECT *,COUNT(SerialNumber) FROM config_log GROUP BY Seria...

问题:

I have a mysql table (shown below) with some values, I need to get the new values and duplicate values

I tried SELECT *,COUNT(SerialNumber) FROM config_log GROUP BY SerialNumber;

But it return only the duplicate count. How to find this ???

Thank You

Table :

   Id   SerialNumber   ConfigDate  ProductId
    ---+--------------+-----------+---------
    1    11111          2018-12-22    1
    2    22222          2018-12-22    2
    3    33333          2018-12-22    3
    4    11111          2018-12-23    1

Product Table :

 ProductId    Name
-----------+------
  1           a
  2           b
  3           c

Expected Result

Id   SerialNumber   ConfigDate   Type      Name
---+--------------+------------+----------+-------
1    11111          2018-12-22   New          a
2    22222          2018-12-22   New          b
3    33333          2018-12-22   New          c
4    11111          2018-12-23   Duplicate    a

回答1:

You can try below -

DEMO

select id,SerialNumber,ConfigDate, case when num=1 then 'New' else 'Duplicate' end as  Type,
productid,name
from
(
SELECT 
    @row_number:=CASE
        WHEN @SerialNumber = SerialNumber THEN @row_number + 1
        ELSE 1
    END AS num,
    @SerialNumber:=SerialNumber as PSerialNumber, id, SerialNumber,ConfigDate,productid,name
FROM
    t1 left join product on productid=pid ,(SELECT @SerialNumber:=0,@row_number:=0) AS t
order by SerialNumber
)A

回答2:

the following mysql query will produce the expected result

SELECT cl.*, 
       IF (cl.id = (SELECT Min(id) 
                    FROM   `config_log` 
                    WHERE  cl.serialnumber = serialnumber 
                    ORDER  BY id ASC), 'NEW', 'DUPLICATE') AS type, 
       p.name 
FROM   `config_log` AS `cl` 
       INNER JOIN products AS p 
               ON p.id = cl.productid 

Tested on mysql Distrib 5.7.24, for Linux (x86_64)


回答3:

for filtering the result you can use having like this

SELECT cl.*, 
       IF (cl.id = (SELECT Min(id) 
                    FROM   `config_log` 
                    WHERE  cl.serialnumber = serialnumber 
                    ORDER  BY id ASC), 'NEW', 'DUPLICATE') AS type, 
       p.name 
FROM   `config_log` AS `cl` 
       INNER JOIN products AS p 
               ON p.id = cl.productid 
HAVING type = 'DUPLICATE' 
  • 发表于 2019-01-07 17:36
  • 阅读 ( 203 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除