mysql - 寻找在 Mysql 中查询多个电子邮件地址的最有效方法

Question

我正在寻找一种方法来选择 joe 的电子邮件地址，以便每次运行选择查询时，我只得到一个按 ID 顺序返回的电子邮件地址。对结果进行排序..

      so on first select I get joe@blogs.com 
      and then second I get joe@gmail.com
      and third joe@outlook.com
      and next joe@blogs.com as there are only three entries

如果你明白我的意思。

            addresses
            ---------------------
            id|name|email
            ---------------------
            1 |joe |joe@blogs.com
            2 |joe |joe@gmail.com
            3 |joe |joe@outlook.com

Answer 1

试一下:

CREATE TABLE #results (id INT, name VARCHAR(50), email VARCHAR(100))
DECLARE @currentID INT;

WHILE(1 = 1)
BEGIN
    SET @currentID =
    (SELECT TOP 1 t.id 
    FROM [YOURTABLE] t
    LEFT JOIN #results r
        ON r.id = t.id
    WHERE t.name = 'joe'
        AND r.id IS NULL)

    IF @currentID IS NULL
        BREAK;

    SELECT * FROM [YOURTABLE] WHERE id = @currentID 

    INSERT INTO #results
    SELECT TOP 1 t.id, t.name, t.email 
    FROM [YOURTABLE] t
    WHERE t.id = @currentID 
END