Table:
firstname | lastname | age
Joey | Sanchez | 33
Joey | Sanchez | 33
Rachel | Dudok | 28我正在尝试创建一个MySQL select语句,当两个列重复时,它将在age列中返回一个值"ERROR“
我尝试了Case语句,但它最后只返回了1行。
SELECT
Firstname,
Lastname,
CASE
WHEN count(firstname) AND count(lastname) AND count(age) >= 2
THEN '**ERROR**'
ELSE age
END
FROM Table;发布于 2015-06-16 19:53:31
您可以尝试使用group by
select Firstname
, Lastname
, case when count(*) > 1 then '**ERROR**' else age end
from Table
group by Firstname
, Lastname
, age;或者,如果您想返回带重复项的所有行:
select t.Firstname
, t.Lastname
, case when (select count(*)
from Table
where Firstname = t.Firstname and
Lastname = t.Lastname and
age = t.age) > 1 then '**ERROR**' else age
end
from Table t发布于 2015-06-16 20:24:52
处理重复记录的方法有很多。对于大多数情况,最好的方法可能是防止它们完全使用唯一的键:
ALTER TABLE `mytable` ADD UNIQUE INDEX (`firstname`,`lastname`,`age`);但是,由于您已经有了副本,需要找到它们,这是以后的工作。对于您的特定请求,这里有一种处理方法:
SELECT s.`firstname`,s.`lastname`,IF(c.rc > 1,'ERROR',s.`age`) AS 'age'
FROM `myTable` AS s
JOIN (SELECT `firstname`, `lastname`, `age`, count(*) AS rc
FROM `myTable`
GROUP BY `firstname`,`lastname`,`age`)
AS c ON c.`firstname` = s.`firstname`
AND c.`lastname` = s.`lastname`
AND c.`age` = s.`age`;如果您只想在每个名称/年龄组合中接收1行,请向外部查询添加DISTINCT:
SELECT DISTINCT s.`firstname`,s.`lastname`,IF(c.rc > 1,'ERROR',s.`age`) AS 'age'
FROM `myTable` AS s
JOIN (SELECT `firstname`, `lastname`, `age`, count(*) AS rc
FROM `myTable`
GROUP BY `firstname`,`lastname`,`age`)
AS c ON c.`firstname` = s.`firstname`
AND c.`lastname` = s.`lastname`
AND c.`age` = s.`age`;https://stackoverflow.com/questions/30876878
复制相似问题