查询的结果太多
查询的结果太多
提问于 2011-04-26 01:36:18
我试着理解下面的问题，
SELECT s.LAST_NAME||', '||s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ') AS FULL_NAME,
s.LAST_NAME,
s.FIRST_NAME,
s.MIDDLE_NAME,
s.STUDENT_ID,
ssm.SCHOOL_ID,
ssm.SCHOOL_ID AS LIST_SCHOOL_ID,
ssm.GRADE_ID ,
sg1.BENCHMARK_ID,
sg1.GRADE_TITLE,
sg1.COMMENT AS COMMENT_TITLE,
ssm.STUDENT_ID,
sg1.MARKING_PERIOD_ID,
sg1.LONGER_COURSE_COMMENTS,
sp.SORT_ORDER,
sched.COURSE_PERIOD_ID
FROM STUDENTS s,
STUDENT_ENROLLMENT ssm ,
SCHEDULE sched
LEFT OUTER JOIN STUDENT_REPORT_CARD_BENCHMARKS sg1 ON (
sg1.STUDENT_ID=sched.STUDENT_ID
AND sched.COURSE_PERIOD_ID=sg1.COURSE_PERIOD_ID
AND sg1.MARKING_PERIOD_ID IN ('0','442','445','450')
AND sg1.SYEAR=sched.SYEAR)
LEFT OUTER JOIN COURSE_PERIODS rc_cp ON (
rc_cp.COURSE_PERIOD_ID=sg1.COURSE_PERIOD_ID
AND rc_cp.DOES_GRADES='Y')
LEFT OUTER JOIN SCHOOL_PERIODS sp ON (sp.PERIOD_ID=rc_cp.PERIOD_ID)
WHERE ssm.STUDENT_ID=s.STUDENT_ID
AND ssm.SCHOOL_ID='1'
AND ssm.SYEAR='2010'
AND ('22-APR-11' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL))
AND (LOWER(s.LAST_NAME) LIKE 'la''porsha%' OR LOWER(s.FIRST_NAME) LIKE 'la''porsha%' )
AND sched.STUDENT_ID=ssm.STUDENT_ID AND sched.MARKING_PERIOD_ID IN ('0','444','446','447','445','448','450','443','449')
AND ('22-APR-11' BETWEEN sched.START_DATE AND sched.END_DATE OR (sched.END_DATE IS NULL AND '22-APR-11'>=sched.START_DATE))
ORDER BY s.LAST_NAME,s.FIRST_NAME并修改它以返回正确的结果--只返回一个不同的人。当搜索某个特定的人时，会返回多个结果，因为schedule.course_period_id返回了唯一的值。因为在course_period_id字段上有几个左外连接，但是跨不同的表，所以我不知道在哪里修改查询。
回答 3
Stack Overflow用户
回答已采纳
发布于 2011-04-26 02:00:03
当然，如果子表连接到同一个person的多个记录，那么您就有多个记录。这是预期的和正确的行为。
如果您只希望每个人有一条记录，那么您必须修改查询，告诉它您想让它选择多个子记录中的哪一个。但是为什么你不想看到这个人的所有预定课程，而不是只有一个呢？
如果需要，您可以使用group by，然后对导致多个记录的字段进行聚合(如min或max)。但是，您仍然需要知道您是否只需要第一个周期记录或最后一个周期记录，或者您如何决定要查看的人的六个记录中的哪一个？
Stack Overflow用户
发布于 2011-04-26 02:03:47
我试图通过格式化你的查询和摆脱混合的语法来帮助人们回答。不是真正的答案，但对于评论来说太长了：
SELECT s.LAST_NAME || ', ' || s.FIRST_NAME || ' ' || COALESCE(s.MIDDLE_NAME,' ')
AS FULL_NAME,
s.LAST_NAME, s.FIRST_NAME, s.MIDDLE_NAME, s.STUDENT_ID,
ssm.SCHOOL_ID, ssm.SCHOOL_ID AS LIST_SCHOOL_ID, ssm.GRADE_ID ,
sg1.BENCHMARK_ID, sg1.GRADE_TITLE, sg1.COMMENT AS COMMENT_TITLE,
ssm.STUDENT_ID, sg1.MARKING_PERIOD_ID, sg1.LONGER_COURSE_COMMENTS,
sp.SORT_ORDER, sched.COURSE_PERIOD_ID
FROM STUDENTS s
INNER JOIN STUDENT_ENROLLMENT ssm
ON ssm.STUDENT_ID=s.STUDENT_ID -- moved from WHERE to here
INNER JOIN SCHEDULE sched
ON sched.STUDENT_ID=ssm.STUDENT_ID -- moved from WHERE to here
LEFT OUTER JOIN STUDENT_REPORT_CARD_BENCHMARKS sg1
ON ( sg1.STUDENT_ID=sched.STUDENT_ID
AND sched.COURSE_PERIOD_ID=sg1.COURSE_PERIOD_ID
AND sg1.MARKING_PERIOD_ID IN ('0','442','445','450')
AND sg1.SYEAR=sched.SYEAR)
LEFT OUTER JOIN COURSE_PERIODS rc_cp
ON ( rc_cp.COURSE_PERIOD_ID=sg1.COURSE_PERIOD_ID
AND rc_cp.DOES_GRADES='Y')
LEFT OUTER JOIN SCHOOL_PERIODS sp
ON (sp.PERIOD_ID=rc_cp.PERIOD_ID)
WHERE ssm.SCHOOL_ID='1'
AND ssm.SYEAR='2010'
AND ('22-APR-11' BETWEEN ssm.START_DATE AND ssm.END_DATE
OR (ssm.END_DATE IS NULL))
AND ( LOWER(s.LAST_NAME) LIKE 'la''porsha%'
OR LOWER(s.FIRST_NAME) LIKE 'la''porsha%' )
AND sched.MARKING_PERIOD_ID
IN ('0','444','446','447','445','448','450','443','449')
AND ( '22-APR-11' BETWEEN sched.START_DATE AND sched.END_DATE
OR ( sched.END_DATE IS NULL
AND '22-APR-11' >= sched.START_DATE))
ORDER BY s.LAST_NAME, s.FIRST_NAME希望能有所帮助。
Stack Overflow用户
发布于 2011-04-26 01:45:53
Look up group by子句。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5781153
复制相关文章
相似问题
腾讯云开发者
