订单每天选择10行

i have a db with records with date (timestamp) i need to select 10 records for each day (there are many more per day) and order them by few columns...

我有一个带有日期记录的数据库(时间戳)我需要为每天选择10条记录(每天还有更多记录)并按几列排序...

how should that query look like?

该查询应该如何?

9 个解决方案

#1

You have to get your 10 records per day in a subquery for each day and join them to the main table by a left join, so you'll get max 10 records per day. The SQL would look like this:

您必须每天在子查询中获取10条记录,并通过左连接将它们连接到主表,这样您每天最多可获得10条记录。 SQL看起来像这样:

SELECT t1.columns
FROM mytable t1 
  LEFT JOIN 
     (SELECT pk FROM mytable t2 
     WHERE t2.datecol = t1.datecol 
     ORDER BY t2.orderFor10Rows LIMIT 10) t3
  ON t1.pk = t3.pk
ORDER BY t1.anyOtherColumns

No warranty for proper MySQL-syntax as I'm not used to it.

不保证正确的MySQL语法,因为我不习惯它。

#2

To select the top 10 records for each day ordered by days in SQL Server 2005

在SQL Server 2005中选择按天排序的每天的前10条记录

select * from 
(select *,ROW_NUMBER()
OVER
(PARTITION BY record.day order by record.day desc,record.score desc)
as row from record) 
as table1 where row < 11

Now since your record table doesn´t have a day column as such, you need to use something like datepart(day, record.date) instead of record.day

既然你的记录表没有这样的日期列,你需要使用像datepart(day,record.date)而不是record.day这样的东西。

This should solve your problem

这应该可以解决您的问题

#3

If you just need ten rows — any ten rows, you don't care which, and there is no guarantee they're random, you can use the LIMIT clause. Example:

如果你只需要十行 - 任何十行,你不关心哪一行,并且不能保证它们是随机的,你可以使用LIMIT子句。例:

SELECT whatever
  FROM tablename
  WHERE datecol = '2009-07-13'
  LIMIT 10

That'll give you ten rows. Its up to MySQL which ten. You can use ORDER BY or additional WHERE items to pick a certain 10. For example, here is the most recent 10:

那会给你十排。它取决于MySQL十。您可以使用ORDER BY或其他WHERE项来选择某个10.例如,这是最近的10个:

SELECT whatever
  FROM tablename
  WHERE datecol = '2009-07-13'
  ORDER BY timecol DESC
  LIMIT 10

LIMIT is documented as part of the SELECT syntax.

LIMIT记录为SELECT语法的一部分。

#4

If you're working from a programming language and not directly querying the server, you could dynamically construct a query for the union of the 'Limit 10' or 'Top 10' for each day. Not incredibly efficient, but it would at least work and be easy to debug and modify later. You could even create the query dynamically via an SP in the server and work straight from there.

如果您使用的是编程语言而不是直接查询服务器,则可以为每天的“Limit 10”或“Top 10”联合动态构建查询。效率不是很高,但它至少可以工作,并且以后很容易调试和修改。您甚至可以通过服务器中的SP动态创建查询,并直接从那里开始工作。

#5

If you are working with MySQL, and the column is of type timestamp. You need to convert it to Date and then compare it with the date you want to compare with.

如果您正在使用MySQL,并且该列的类型为timestamp。您需要将其转换为日期,然后将其与您要比较的日期进行比较。

SELECT * FROM tablename tName

SELECT * FROM tablename tName

where

Date(timestampFieldName) = Date('2009-07-08')

日期(timestampFieldName)=日期('2009-07-08')

limit 0,10

#6

Here is a possible solution, but it will require a little work outside of sql. This is from a live example of a list of movies. All you need to do after this is pull the first 10 movies of the concatenated list.

这是一个可能的解决方案,但它需要在sql之外做一些工作。这是一个电影列表的实例。在此之后您需要做的就是拉出连续列表的前10部电影。

SELECT Movie_Release_Year, GROUP_CONCAT( Movie_Title
ORDER BY Movie_Title ) 
FROM movies
GROUP BY Movie_Release_Year

see Group Concat for more details

有关详细信息,请参阅Group Concat

#7

After looking at (the ever excellent) xaprb blog from Baron Schwarz, http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ I wonder if the use of user-defined variables could work here.

看了Baron Schwarz的(优秀的)xaprb博客后,http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/我不知道是否使用了用户定义的变量可以在这里工作。

select hiredate, ename, sal
from ( select hiredate, ename, sal,
       @num := if(@hiredate = hiredate, @num + 1, 1) as row_number,
       @hiredate := hiredate as dummy
     from emp_temp
) as x
where row_number < 3
order by hiredate, ename, sal

I've only tried the above for small sets of data, but it seems to work in bringing back just two records per hiredate. So far as I can tell from limited testing it should scale up for greater data sets. ( there may be performance issues in large data sets as Mysql is creating a temporary table)

我只是尝试了上面的小数据集,但似乎每个hiredate只返回两个记录。据我所知,从有限的测试中可以扩展到更大的数据集。 (由于Mysql正在创建临时表,因此大型数据集中可能存在性能问题)

#8

Actually it´s more like this, I had misplaced the order by for the date.

实际上它更像是这样,我把订单放错了日期。

Here we assume there is a datetime field in the Records table and a score field to rank records.

这里我们假设记录表中有一个日期时间字段和一个用于对记录进行排名的分数字段。

SELECT      *
FROM            (SELECT *, ROW_NUMBER() OVER (PARTITION BY datepart(year, Record.date), datepart(month, Record.date), datepart(day, Record.date)
ORDER BY Record.score desc) AS row
FROM            Record ) AS table1
WHERE        row < 11
ORDER BY datepart(year, Record.date) desc, datepart(month, Record.date) desc, datepart(day, Record.date) desc

#9

old question, but I found magnificient answer ( not my own ) using session variables.

老问题,但我发现使用会话变量的答案(不是我自己的)。

Considering table

CREATE TABLE `report` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`reportDate` int(11) DEFAULT NULL,
`count` int(11) DEFAULT NULL,
`parameter` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `INDEX1` (`reportDate`,`parameter`,`count`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Following query will select top 10 parameter per day based on their counts on that day

以下查询将根据当天的计数每天选择前10个参数

SELECT parameter, reportDate, count
FROM
    (SELECT parameter, 
             reportDate, 
             count, 
             @reportDate_rank := IF(@current_reportDate = reportDate,@reportDate_rank + 1, 1) AS reportDate_rank,
             @current_reportDate := reportDate 
    FROM report
    ORDER BY reportDate, count DESC
    ) dateRanked
WHERE reportDate_rank <= 10;

#1