MySQL:限制查询或子查询(在左/内部联接中?) [英] MySQL: limit query or subquery (in left/inner join?)

问题描述

预先致歉，我是(My)SQL的新手-这对于专家级DBA来说应该是一个简单的问题-但我什至不知道该从哪里开始寻找解决方案.我什至不确定我是否以下面的正确方式应用了LEFT JOIN.

Apologise in advance, I'm novice in (My)SQL - this should be an easy question for expert DBAs - but I don't even know where to start finding a solution at all. I'm not even sure if I applied LEFT JOIN in the correct way below.

我的(DB)结构非常简单:

My (DB) structure is quite simple:

我有testsuite个，并且每个testsuite都有几个testcase链接(逻辑实体") 在测试用例启动期间，我为testsuiteinstance表中的每个testsuite创建一个条目-为每个testcase在testcaseinstance中创建一个条目.

I have testsuites, and several testcases are linked to each testsuite ("logical entities") During testcase kick-off, I'm creating an entry for each testsuite in the testsuiteinstance table - and one entry in testcaseinstance for each testcase.

我的目标是获取属于某个testsuite

My goal is to fetch the last 10 testcaseinstances of all testcases belonging to a certain testsuite

这是我用来获取 all testcaseinstances的查询:

This is the query I use to fetch all testcaseinstances:

SELECT * FROM testcaseinstance AS tcinst
LEFT JOIN testsuiteinstance tsinst ON tsinst.id=tcinst.testsuiteinstance_id
LEFT JOIN testsuite ts ON ts.id=tsinst.testsuite_id
WHERE ts.id = 349 ORDER BY tcinst.id DESC;

因此，假设我在testsuite中有两个testcase，并且两个testcase都分别执行了100次.该查询给了我200行.如果我在末尾加上"LIMIT 10"，我只会得到一种testcase类型的最后10行，但是我想得到20行(属于两个testcase的最后10-10行)

So, let's say I have two testcases in a testsuite and both testcase was executed 100 times each. This query gives me 200 rows. If I put "LIMIT 10" at the end, I will only get the last 10 rows for one testcase type, but I want 20 rows (the last 10-10 belonging to the two testcases)

除了解决方案查询或指向教程"的指针外，我将不胜感激，我可以开始查看与该主题相关的内容(无论是什么:D)

I'd appreciate some description beside the solution query or a pointer to a "tutorial" I can start looking at related to the topic (whatever would that be :D)

提前谢谢！

推荐答案

这是一种方法.考虑一下这个(稍作)的例子...

Here's one approach; consider this (slightly contrived) example...

SELECT * FROM ints;

+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

假设我们要返回此列表中的前3个偶数和前3个奇数.暂时忽略此特定示例的另一个更简单的解决方案，我们可以改为执行类似的操作...

Let's say we want to return the top 3 even numbers and the top 3 odd numbers from this list. Ignoring for the moment that there's another, simpler, solution to this particular example we can instead do something like this...

SELECT x.*
     , COUNT(*) rank 
  FROM ints x 
  JOIN ints y 
    ON MOD(y.i,2) = MOD(x.i,2) 
   AND y.i >= x.i 
 GROUP 
    BY i 
 ORDER 
    BY MOD(x.i,2) DESC
     , x.i DESC;
+---+------+
| i | rank |
+---+------+
| 9 |    1 |
| 7 |    2 |
| 5 |    3 |
| 3 |    4 |
| 1 |    5 |
| 8 |    1 |
| 6 |    2 |
| 4 |    3 |
| 2 |    4 |
| 0 |    5 |
+---+------+

从这里开始，从每个组中仅获取前3名的过程变得微不足道...

From here, the process of grabbing just the top 3 from each group becomes trivial...

SELECT x.*
     , COUNT(*) rank 
  FROM ints x 
  JOIN ints y 
    ON MOD(y.i,2) = MOD(x.i,2) 
   AND y.i >= x.i 
 GROUP 
    BY i 
HAVING rank <=3 
 ORDER 
    BY MOD(x.i,2),x.i DESC;
+---+------+
| i | rank |
+---+------+
| 8 |    1 |
| 6 |    2 |
| 4 |    3 |
| 9 |    1 |
| 7 |    2 |
| 5 |    3 |
+---+------+

...这可以简化为...

...and this can be simplified to...

SELECT x.*
  FROM ints x 
  JOIN ints y 
    ON MOD(y.i,2) = MOD(x.i,2) 
   AND y.i >= x.i 
 GROUP 
    BY i 
HAVING COUNT(*) <=3;

+---+
| i |
+---+
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

这篇关于MySQL:限制查询或子查询(在左/内部联接中?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！