代码之家 › 专栏 › 技术社区 › Brad The App Guy

限制SQL查询中一列的连续值

join mysql sql

Brad The App Guy · 技术社区 · 15 年前

我有一张桌子,结构如下:

id           -int(11)
event_id     -int(11)
photo_id     -int(11)
created_at   -datetime

如何编写一个查询,该查询将返回最近的100行,但确保照片id中具有相同值的连续行不超过4行

5 回复 | 直到 14 年前

Andomar 15 年前

您可以添加一个where子句,它筛选出4行以下的行 photo_id 存在:

select *
from YourTable t1
where 4 > (
    select count(*)
    from YourTable t2
    where t1.event_id = t2.event_id
    and t1.photo_id < t2.photo_id
)
limit 100

对于大桌子来说这可能会有点慢。一个更快的,但非常特定于mysql的选项是使用变量。例如:

select *
from (
    select
        @nr := case 
            when event_id = @event then @nr + 1 
            else 1 
        end as photonr
    ,   @event := event_id
    ,   t1.*
    from YourTable as t1
    cross join (select @event := -1, @nr := 1) as initvars
    order by event_id
) as subquery
where subquery.photonr < 5
limit 100;

使用的测试数据:

drop table if exists YourTable;

create table YourTable (
  id int auto_increment primary key
, event_id int
, photo_id int
);

insert into YourTable (event_id, photo_id)
values (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (1,6);

Randy 15 年前

在oracle中,可以使用lag函数

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

不确定在mysql中是否可能。

Stin 15 年前

如果您使用t-sql,请查看 http://msdn.microsoft.com/en-us/library/ms189798.aspx 为功能排序。

从你的问题看来,你想要的是什么。下面是我对查询的快速尝试,我不在终端,所以它没有被检查,但它应该让您开始:

SELECT
  id,
  event_id,
  photo_id,
  created_at,
  NTILE(4) OVER (ORDER BY photo_id) AS 'Quartile'
FROM tbl
WHERE NTILE(4) OVER (ORDER BY photo_id)<2
ORDER BY created_at DESC

链接页面提供了所有排名功能的一个很好的示例。

祝你好运

Stiivi 15 年前

试试这个:

SELECT p.id, p.event_id, p.photo_id, p.created_at
FROM photo_table p,
    (

        SELECT photo_id, MAX(created_at) max_date
        FROM photo_table
        GROUP BY photo_id 
    ) t
WHERE p.created_at = t.max_date
        AND p.photo_id = t.photo_id
ORDER BY p.created_at DESC
LIMIT 100

它的作用是: 1。查找最新照片更改日期 2。只查找每张照片的最后事件三。选择前100个最近的

在postgresql或oracle中,使用分析/窗口功能会更简单,例如:

FIRST (created_at) OVER (PARTITION BY photo_id ORDER BY created_at DESC)

Josh Lee ZZ Coder 14 年前

我想说这样的话会让你走上正轨:

$sql = "SELECT DISTINCT * FROM myTable ORDER BY id ASC LIMIT 100";

在这种情况下,“distinct”将只检索不同的行,而忽略重复的行。

希望有帮助。