限制在MySQL中的条件 [英] LIMIT on a condition in MySql

问题描述

我正在尝试以这种形式从表中获取信息:

I am trying to get info from a table in this form :

表_1

jobid(PK) projectid desc
1            1      whatever
2            1         .
3            1         .
4            2         .
5            2         .
.            .         .
.            .         .

我想得到的是一个查询，每个项目id仅给我5行. (在WHERE语句上为LIMIT，但在整个SELECT上不为

What I am trying to get is a query which will give me only 5 rows per projectid. ( a LIMIT on the WHERE statement but not on the whole SELECT)

如果我使用LIMIT，我将总共获得5个结果.

If I use LIMIT, I get a total of 5 results.

推荐答案

您可以使用以下查询以动态形式构建SQL:

What you can do is build the SQL in a dynamic form using the following query:

SELECT GROUP_CONCAT( 
  DISTINCT CONCAT(
    '(select jobid, projectid, desc from jobs where projectid=',
    projectid,
    ' order by jobid limit 5)') 
  SEPARATOR ' union ') AS q 
FROM table_1;

将结果保存到变量中，然后执行保存的SQL.

Save the result into a variable, and then execute the saved SQL.

这篇关于限制在MySQL中的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！