所以我遇到了这样一个问题:我试图获取具有最高id
的行, 在第一个示例中,我之所以对RESTResults.id进行分组,是因为出于某种原因,它显示了两个相同的行,但由于TestSuiteCollectionID
和TestCaseID
具有相同的TestCaseID
但具有不同的TestSuiteCollectionID
,并且我需要具有最高ID
的行,或者正如这里使用的TestResults.id
,我认为我可以对ID进行分组,并按照TestResult.id Desc执行顺序,以首先 这是我相当长的sql代码,不要介意所有
nameID
。 请记住limit2
不能工作,因为我有更多的TestCaseID
这只是针对Id1841
,因此它必须是每个组的最高Id,由TestSuiteCollectionId,TestCaseID
和不同的TestCaseID
组成
SELECT
testresults.id, nameId, TestSuiteId, TestCaseId, TestSuiteCollectionId, TestCaseName, TestSuiteName, TestSuiteCollectionName, StatusId
FROM
Testreportingdebug.testresults
LEFT JOIN
TestResultRequirementLink ON testresults.id = TestResultRequirementLink.testresultid
LEFT JOIN
TestCaseRequirement ON TestResultRequirementLink.requirementId = TestCaseRequirement.id
LEFT JOIN
testcase ON testresults.TestCaseId = testcase.id
LEFT JOIN
testsuite ON testresults.TestSuiteId = testsuite.id
LEFT JOIN
testsuitecollection ON testresults.TestSuiteCollectionId = testsuitecollection.id
WHERE
TRUE
AND Testcaserequirement.nameId IN ('49971' , '49974',
'49976',
'305652',
'305653',
'305648',
'46486',
'46487',
'46440',
'46432',
'46447',
'46474',
'46441',
'651706',
'651709',
'305655',
'309133',
'46488',
'46317',
'46364',
'46365',
'46336',
'46342',
'49963',
'49956',
'49961',
'46467',
'46468',
'46469',
'171651',
'171652',
'171654',
'171655',
'190800',
'190799',
'190801',
'171232',
'171647',
'171648',
'171650',
'226065',
'226064',
'236257',
'46344',
'46332',
'46345',
'46331',
'46347',
'46330',
'46346',
'46720',
'46723',
'46725',
'46728',
'46737',
'46740',
'46743',
'46527',
'46530',
'46534',
'46535',
'46550',
'46557',
'46528',
'46533',
'46551',
'46559',
'46560',
'46703',
'46704',
'46706',
'46574',
'46708',
'46537',
'46543',
'46563',
'46567',
'46326',
'46328',
'46680',
'46643',
'46627',
'46626',
'46625',
'46622',
'46623',
'46644',
'46645',
'46647',
'46648',
'46714',
'46673',
'46674',
'46677',
'46679',
'46683',
'46689',
'46713',
'46678',
'46684',
'46699',
'46707',
'49994',
'46601',
'46604',
'46579',
'46602',
'46603',
'46605',
'46690',
'46456',
'46437',
'46438',
'46439',
'46475',
'46455',
'83985',
'46472',
'46471',
'1178127',
'1178128',
'1178129',
'1178132',
'1271718',
'46473',
'1178137',
'1301190',
'1308824',
'1273524',
'1273525',
'1273526',
'1273527',
'1273528',
'1273529',
'1308825',
'1308827',
'1308826',
'1336930',
'1308828',
'991788',
'1273496',
'1308819',
'1273497',
'1273500',
'1273503',
'1273508',
'1273510',
'1273512',
'1273518',
'1308822',
'1273515',
'1284164',
'1301195',
'1273516',
'1273517',
'1271722',
'1271723',
'1271724',
'1284162',
'1271720',
'1271728',
'1271731',
'1271727',
'1271730',
'1271733',
'1280758',
'1271736',
'1271738',
'1271740',
'1271743',
'1271747',
'1271746',
'1271744',
'1271745',
'1301193',
'1271726',
'1271729',
'1271732',
'1271735',
'1271737',
'1271739',
'1271741',
'1301632',
'1273520',
'1273522',
'1280762',
'1280765',
'1284168',
'1284167',
'1301196',
'1284165',
'1284166',
'1273509',
'1273511',
'1273514',
'1308820',
'1308821',
'1273498',
'1273499',
'1273501',
'1273502',
'1273504',
'1273505',
'1308829',
'1308830',
'1308831',
'1273530',
'1273531',
'1273532',
'1273533',
'1273534',
'1273535',
'1308832',
'1308833',
'1308834',
'1273537',
'1273542',
'1273543',
'1273539',
'1280768',
'1273540',
'1273544',
'1273541',
'1301197',
'1280766',
'1273538',
'1280767',
'1280769',
'1284169',
'1308835',
'1308836',
'1308837',
'1273545',
'1273546',
'1273547',
'1273548',
'1273549',
'1273550',
'1308838',
'1308839',
'1308840',
'1273552',
'1273557',
'1273558',
'1280770',
'1273553',
'1280771',
'1273559',
'1273556',
'1280773',
'1273554',
'1280772',
'1301198',
'1273555',
'1284170',
'1273521',
'1280761',
'1280763',
'1280764')
AND `TestSuiteCollectionName` LIKE '%GiM10%' AND TestCaseId = 1841
GROUP BY testresults.id
ORDER BY testresults.id DESC;
输出量
很抱歉把这个贴成图片,但是太宽了
表输出
然后,我尝试按ID分组,从最高的testresult.id
到最低的ID。 但无论在无DESC时还是在有DESC时,它都表现得最低。
按TestCaseId,TestSuiteCollectionId分组
如果ID是整数,则可以尝试使用聚合函数max():
max(Testcaserequirement.nameId)
如果我没有理解错的话,每个TestSuiteCollectionId和TestCaseId都需要max Id,这就是为什么我在FROM语句中放入max(Id)和group by,然后将其他表连接到该max Id。 SQL:
select
testresults.id,
nameId,
TestSuiteId,
TestCaseId,
TestSuiteCollectionId,
TestCaseName,
TestSuiteName,
TestSuiteCollectionName,
StatusId
from(
select TestSuiteCollectionId, TestCaseId, max(testresults.id) id
FROM Testreportingdebug.testresults
LEFT JOIN testsuitecollection ON testresults.TestSuiteCollectionId = testsuitecollection.id
WHERE TRUE AND Testcaserequirement.nameId IN ('49971' , '49974','49976',........,'1280764')
AND `TestSuiteCollectionName` LIKE '%GiM10%' AND TestCaseId = 1841
GROUP BY TestSuiteCollectionId, TestCaseId) testresults
LEFT JOIN
TestResultRequirementLink ON testresults.id = TestResultRequirementLink.testresultid
LEFT JOIN
TestCaseRequirement ON TestResultRequirementLink.requirementId = TestCaseRequirement.id
LEFT JOIN
testcase ON testresults.TestCaseId = testcase.id
LEFT JOIN
testsuite ON testresults.TestSuiteId = testsuite.id
LEFT JOIN
testsuitecollection ON testresults.TestSuiteCollectionId = testsuitecollection.id
听起来您希望每个TestSuiteCollection,TestCase都有最高的TestResults.id行。 有几种方法去做它像一个小组和加入它。 为了最干净,最容易阅读的方法,我建议使用窗口函数。
您可以这样做:
SELECT * FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY TestSuiteCollectionId, TestCaseId ORDER BY TestResults.Id DESC) AS RowNumber,
testresults.id,
nameId,
TestSuiteId,
TestCaseId,
TestSuiteCollectionId,
TestCaseName,
TestSuiteName,
TestSuiteCollectionName,
StatusId
FROM
Testreportingdebug.testresults
LEFT JOIN
TestResultRequirementLink ON testresults.id = TestResultRequirementLink.testresultid
LEFT JOIN
TestCaseRequirement ON TestResultRequirementLink.requirementId = TestCaseRequirement.id
LEFT JOIN
testcase ON testresults.TestCaseId = testcase.id
LEFT JOIN
testsuite ON testresults.TestSuiteId = testsuite.id
LEFT JOIN
testsuitecollection ON testresults.TestSuiteCollectionId = testsuitecollection.id
--WHERE
--any additional criteria
)foo
WHERE foo.RowNumber = 1