SQL查询重复记录
问题描述:
我下面table.Oquer工作正常,但它显示了完整的重复record.I希望与名称的所有记录中出现单一的时间SQL查询重复记录
CREATE TABLE Customer
(`Name` varchar(7), `Address` varchar(55), `City` varchar(15),`Contact` int,`timestamp` int)
;
INSERT INTO Customer
(`Name`,`Address`, `City`, `Contact`,`timestamp`)
VALUES
('Jack','New City','LA',79878458,456125),
('Joseph','New Lane23','LA',87458458,794865),
('Rosy','Old City','Paris',79878458,215125),
('Maria','New City','LA',79878458,699125),
('Jack','New City','LA',79878458,456125),
('Rosy','Old City','Paris',79878458,845125),
('Jack','New Main Street','New York',79878458,555525),
('Jack','New City','LA',79878458,456125),
('Rosy','Old City','Paris',79878458,845125),
('Jack','New Main Street','New York',79878458,555525),
('Joseph','Near Bank','SAn Francisco',79878458,984521)
;
查询:
select t1.*
from customer t1 join
(select Name, max(`timestamp`) as maxstamp
from customer
group by Name) xx
on t1.Name = xx.Name
and t1.`timestamp` = xx.maxstamp;
答
Use DISTINCT keyword
select DISTINCT t1.* from customer t1 join (select Name, max(timestamp) as
maxstamp from customer group by Name) xx on t1.Name = xx.Name
and t1.timestamp = xx.maxstamp;
答
在SQLite 3.7.11或更高版本(Android Jelly Bean或更高版本)中,您可以简单地使用MAX()和GROUP BY:
SELECT Name, Address, City, Contact, MAX(timestamp) AS timestamp
FROM Customer
GROUP BY Name;
你想要个人用户的最大时间戳数据? – andy