SQL 选择 1 列中的唯一值

Question

我遇到了问题(我想是个小问题)，我希望你能帮助我。
我使用 Sybase Anywhere，这是我的代码:

SELECT TOP 4 Person.Id_person, Person.Name, Person.Surname, Visit.Date, Visit.Place
From Person, Visit
WHERE Visit.Id_person = Person.Id_person
ORDER BY Visit.DATE DESC

结果如下:

3  | Paul | McDonald | 2010-01-19 | Ohio
3  | Paul | McDonald | 2010-01-18 | New York
19 | Ted  | Malicky  | 2009-12-24 | Tokyo
12 | Meg  | Newton   | 2009-10-13 | Warsaw

我不想重复保罗麦克唐纳，并且只进行第一次(按日期)访问。我想要这样的结果:

3  | Paul | McDonald | 2010-01-19 | Ohio
19 | Ted  | Malicky  | 2009-12-24 | Tokyo
12 | Meg  | Newton   | 2009-10-13 | Warsaw
 ....

我该怎么办？你可以帮帮我吗？ :(

Answer 1

这是使用 ROW_NUMBER 函数执行此操作的另一种方法，以确保如果有人在同一天有两个 session ，它仍然有效:

SELECT TOP 4
    Person.Id_person,
    Person.Name,
    Person.Surname,
    T1.Date,
    T1.Place
FROM
    (SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Id_person ORDER BY Date DESC) AS rn
    FROM Visit) AS T1
JOIN Person
ON T1.Id_person = Person.Id_person
WHERE rn = 1
ORDER BY Date DESC

这是我得到的结果:

Id_person Name Surname  Date       Place
3         Paul McDonald 2010-01-19 Ohio
19        Ted  Malicky  2009-12-24 Tokyo
12        Meg  Newton   2009-10-13 Warsaw
1         Foo  Bar      2009-06-03 Someplace

这是我使用的测试数据:

CREATE TABLE Person (Id_person INT NOT NULL, Name NVARCHAR(100) NOT NULL, Surname NVARCHAR(100) NOT NULL);
INSERT INTO Person (Id_person, Name, Surname) VALUES
(3, 'Paul', 'McDonald'),
(19, 'Ted', 'Malicky'),
(12, 'Meg', 'Newton'),
(1, 'Foo', 'Bar'),
(2, 'Baz', 'Qux');

CREATE TABLE Visit (Id_person INT NOT NULL, Date DATE NOT NULL, Place NVARCHAR(100) NOT NULL);
INSERT INTO Visit (Id_person, Date, Place) VALUES
(3, '2010-01-19', 'Ohio'),
(3, '2010-01-18', 'New York'),
(19, '2009-12-24', 'Tokyo'),
(12, '2009-10-13', 'Warsaw'),
(1, '2009-06-03', 'Someplace'),
(12, '2009-10-13', 'Anotherplace'),
(2, '2009-05-04', 'Somewhere');

在 SQL Server 2008 上测试过，但我相信 Sybase 的语法是相似的。