如何从数据库一一返回示例行

Question

网页应显示来自PostgreSql数据库中特定产品类别的一个产品图像。该图像应每25秒自动更改为其他图像。退货产品可能是随机的，也可能是某种顺序的。某些产品可能会丢失，而某些产品可能会重复，但是应该退回标准中的大多数产品。样品检索之间的可用图像总数可能会略有变化

当前使用下面的代码，该代码每25秒执行一次。这需要对数据库进行两个查询：一个查询可能是slwo的计数，第二个查询单个图像的检索。在两种情况下，子句都是重复的，在实际应用中，子句很大，要更改子句，需要在两个地方进行更改。

如何改善这一点，以便单个查询返回样本？列类型无法更改，使用自然主键。如果有帮助，可以添加其他列，触发器，索引，序列。

使用ASP.NET/Mono MVC3和npgsql。

$count = select count(*)
         from products
         where prodtype=$sometype and productid in (select productid from images);

$random = next random integer between 0 .. $count-1;

--  $productsample  is result: desired sample product
$productsample = select product
          from products
          where prodtype=$sometype and productid in (select productid from images)
          offset $random
          limit 1;


create table products ( productid char(20) primary key,
   prodtype char(10) references producttype 
);

create table images(
id serial primary key, 
productid char(20) references products,
mainimage bool
);

Answer 1

order by特别地，如果未按by排序的表达式索引，则an总是会很昂贵。所以不要点。相反count()，在查询中的中做一个随机偏移，但要一次全部完成。

with t as (
    select *
    from
        products p
        inner join
        images i using (productid)
    where
        prodtype = $sometype
)
select *
from t
offset floor(random() * (select count(*) from t))
limit 1

这个版本可能更快

with t as (
    select *, count(*) over() total
    from
        products p
        inner join
        images i using (productid)
    where
        prodtype = $sometype
)
select *
from t
offset floor(random() * (select total from t limit 1))
limit 1