sql查找具有不同列值的行

My database table is (it has millions of records)

我的数据库表是(它有数百万条记录)

    sitename        rank     date
    facebook.com    1       2016-2-13
    gmail.com       2       2016-2-13
    yahoo.com       3       2016-2-13
    aol.com         4       2016-2-13
    facebook.com    1       2016-2-14
    gmail.com       2       2016-2-14
    yahoo.com       4       2016-2-14
    aol.com         3       2016-2-14

I want to find sites whose ranking has changed. in the above illustration yahoo and aol has changed. I tried several queries but cant get it to work.

我想找到排名已经改变的网站。在上面的例子中，雅虎和美国在线已经改变了。我试了几个问题，但没能使它生效。

5 个解决方案

#1

Its a simple select, group by and having query like this:

它是一个简单的选择，分组，并有这样的查询:

SELECT sitename,MAX(rank) - MIN(rank) as changed
FROM YourTable
GROUP BY sitename
HAVING COUNT(DISTINCT rank) > 1

#2

Based on the ideas given, here's what I've got:

基于所给出的想法，我得到了以下几点:

SELECT sitename, rank, rank - (SELECT rank from sites WHERE sitename = main.sitename ORDER BY date LIMIT 1,2) as rankChange
FROM sites as main
WHERE date <= NOW()
GROUP BY sitename
HAVING COUNT(DISTINCT rank) > 1

This will return

这将返回

sitename    rank    rankChange
aol.com        4             1
yahoo.com      3            -1

If you remove HAVING COUNT(DISTINCT rank) > 1 from the query, you will see the set with the unchanged webs:

如果您从查询中删除具有COUNT(不同等级)的> 1，您将看到具有未更改web的集合:

sitename    rank    rankChange
aol.com        4             1
facebook.com   1             0
gmail.com      2             0
yahoo.com      3            -1

#3

you can do by like this:

你可以这样做:

select * from your_table t_outer
group by sitename, date
having rank <> (select rank from your_table t_internal 
where t_outer.date > t_internal.date and t_outer.sitename = t_internal.sitename
order by t_internal.date limit 1)

and it is output:

这是输出:

#4

Something like following. And you need a record for every site every day

就像下面。你每天都需要每个网站的记录

SELECT sitename, MAX(rank) - MIN(rank) as ChangeRank
FROM ChangeHistoryTable
WHERE date between dateFromWhichYouNeedChanges and dateToWhichYouNeedChanges
GROUP BY sitename
HAVING COUNT(DISTINCT rank) > 1

#5

Try this:

试试这个:

Select sitename from table group by sitename having count(*) > 1 order by sitename, rank DESC

#1