从SQL子查询中获取更多详细信息
问题描述:
我有一个SQL查询向我提供数据库中的双记录列表。从SQL子查询中获取更多详细信息
select periodid, itemid from periodscore
group by periodid, itemid
having count(*) > 1
这个按预期工作,但现在我想检索这些记录的其他字段(如日期最后更新等)。所以,我想:
select * from periodscore where periodscoreid in
(select periodscoreid from periodscore
group by periodid, itemid
having count(*) > 1)
当然,这并不工作,给我的错误:
Column 'periodscore.PeriodScoreID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
我怎样才能检索到在此查询的附加字段?
答
select ps.*
from periodscore ps
inner join (
select periodid, itemid
from periodscore
group by periodid, itemid
having count(*) > 1
) psm on ps.periodid = psm.periodid and ps.itemid = psm.itemid
答
select p1.* from periodscore p1 JOIN
(select periodid, itemid from periodscore
group by periodid, itemid
having count(*) > 1) p2
ON (p1.periodId = p2.periodId
AND p1.itemid = p2.itemid)
如果periodid或项目有快速的答案空值,那么
select p1.* from periodscore p1 JOIN
(select periodid, itemid from periodscore
group by periodid, itemid
having count(*) > 1) p2
ON (IFNULL(p1.periodId,0) = IFNULL(p2.periodId,0))
AND IFNULL(p1.itemid,0) = IFNULL(p2.itemid,0))
谢谢!这个查询也给我216记录,而不是原来的103 – Sam 2010-09-08 11:41:24
@Sam - 第一个查询告诉你的组。这会为您提供属于这些组的行。当然,所有的组将至少有2行,因为这是查询的目的!如果你只想每组返回一行,那么哪一行? – 2010-09-08 11:48:54
嗯,好点:)谢谢你的答案! – Sam 2010-09-08 11:53:38