用于标记新值和重复值的mysql代码

toe95027 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(300)

我有一个mysql表（如下所示）和一些值，我需要得到新的值和重复的值
我试过了 SELECT *,COUNT(SerialNumber) FROM config_log GROUP BY SerialNumber ;
但它只返回重复计数。怎么找到这个？？？
谢谢您
表格：

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

产品表：

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

预期结果

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

mysql stored-procedures select-query

来源：https://stackoverflow.com/questions/54001310/mysql-code-to-mark-new-and-duplicate-values

3条答案

按热度按时间

cbeh67ev1#

对于过滤结果，您可以使用如下方式

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'

赞(0）回复(0）举报 2021-06-17

0s7z1bwu2#

你可以试试下面-
演示

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

赞(0）回复(0）举报 2021-06-17

os8fio9y3#

下面的mysql查询将产生预期的结果

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

在mysql发行版5.7.24上测试，适用于linux（x86\u 64）

赞(0）回复(0）举报 2021-06-17

我来回答

用于标记新值和重复值的mysql代码

3条答案

相关问题

热门标签

最新问答