MySQL - 将这两个查询连同一个连接或子查询一起加入?
此查询返回我需要给定具体traceid
值(50在本例中,这是存在两个表中,tblResults
和tblTraces
一密钥对数据:MySQL - 将这两个查询连同一个连接或子查询一起加入?
SELECT count(changed)
FROM (
SELECT changed
FROM tblResults
WHERE traceid = 50
AND changed = 1
ORDER BY resultid DESC
LIMIT 0,20
) as R
我想运行针对此上述查询几乎每traceid
(因此,选择每个traceid 20行,而不仅仅是traceid 50,所以3 traceid的将意味着60行)这个简单的选择下面获取所需traceid
值:
SELECT `traceid` FROM `tblTraces` WHERE `enabled` = 1
如何将两个查询“粘合”在一起?
所以我想像类似下面的查询,但由于子查询返回多行,它不会工作(我想):
SELECT count(changed)
FROM (
SELECT changed
FROM tblResults
WHERE traceid = (
SELECT `traceid` FROM `tblTraces` WHERE `enabled` = 1
)
AND changed = 1
ORDER BY resultid DESC
LIMIT 0,20
) as R
在MySQL中,没有简单的方法来做到这一点。在其他数据库中,您只需使用row_number()
,但它不可用。
这是一种方法,假设resultId
是在各行唯一的:
select t.traceId, count(changed)
from (select traceid, ResultId, changed,
(select count(*) from tblResults r2 where r2.traceId = r.traceId and r2.ResultId >= r.ResultId and r2.changed = 1) as seqnum
from tblResults r join
tblTraces t
on r.TraceId = t.TraceId and t.enabled = 1
where r.changed = 1
) t
where seqnum <= 20
group by traceId
如果你不希望这样的traceid
做,只是想总计数,然后取出group by
,改变select
到count(changed)
。
唷!那是相当的面条面包师!所以,这是完美的。我只需要删除'和r2.changed = 1',我手动检查了它的完美工作。现在我需要制作一杯咖啡,花一些时间充分理解这个答案,它很棒。谢谢! – jwbensley 2013-03-23 21:21:04
可你只是做一个内部联接,像这样
select count(traceid)
from tblResults a inner join tblTraces b on a.traceid = b.traceid
and b.enabled = 1
and a.changed = 1
还是我在这里错过了别的东西?
每个痕迹的LIMIT 20'使事情复杂化。 – 2013-03-23 20:45:40
这是另一种方法来做到这一点。我改编了Bill Karwin's example。查看他的帖子以获得更全面的解释。
SELECT x.traceId, COUNT(*) FROM ( SELECT a.* FROM tblResults a -- Be sure not to exclude tblResults which are the only record in the trace LEFT OUTER JOIN tblResults b -- We're going to count how many rows came "before" this one ON a.changed = b.changed AND a.traceId = b.traceId AND a.resultId < b.resultId WHERE a.changed = 1 AND a.traceId IN (SELECT traceId FROM tblTraces WHERE enabled = 1) GROUP BY a.resultId HAVING COUNT(*) < 20 -- Eliminate rows ranked higher than 20 ORDER BY b.resultId DESC -- when we rank them by resultid ) x GROUP BY x.traceId
外查询只验证每个traceid包含不超过20个选定的记录,并且内查询结果集,你真正感兴趣的。
您需要'最少()',而不是'MIN()'。但我有同样的想法。 – 2013-03-23 21:01:12
虽然我不满意这个答案,它可能是最简单的较小的表:
SELECT tblTraces.traceid, LEAST(20, SUM(tblResults.changed))
FROM tblTraces
LEFT JOIN tblResults
ON tblTraces.traceid = tblResults.traceid
WHERE tblTraces.enabled = 1
AND tblResults.traceid.changed = 1
GROUP BY tblTraces.traceid
限柱上真正复杂的连接。
如果您需要对这些结果进行计数(仅限于每次追踪20个结果)或实际结果,这并不完全清楚。对于只有数,有一个简单的方法:
SELECT t.traceid,
LEAST(COUNT(*),20) AS cnt
FROM tblTraces AS t
JOIN tblResults AS r
ON r.traceid = t.traceid
WHERE t.enabled = 1
AND r.changed = 1
GROUP BY t.traceid ;
向我们展示'SHOW的输出CREATE TABLE tblResults;' – 2013-03-23 20:41:30
在表'tblTraces'是不同的'traceid'? – Justin 2013-03-23 20:41:50
@Justin是的,他们是 – jwbensley 2013-03-23 20:49:22