比较查询不返回所有记录 [英] Comparison-query does not return all records

问题描述

亲爱的！

我有一个存储学生成绩的数据库 - 不，它不是图表问题:) :) :)来自后门。


在此数据库中，有一个名为qry-yr10-end-of-year-results的查询。该查询包含字段student_ID，名称和名称。和SumofWeighting_applied。


这个sumofWeighting_applied除了计算出来的数值之外别无其他，我不会因为它的名字而感谢你。


查询返回11条记录，它应该做的，以及SQL for查询是

展开 | 选择 | Wrap | 行号

解决方案

不同的数据库环境以不同的方式处理BETWEEN运算符。他们中的一些人以包容的方式使用它，这意味着如果你说什么在b和c之间 - 它实际上将包括值b和c。其他人只会返回介于b和c之间的值，并且实际上会将b和c输出。我认为这是非常明确的，它不会返回69和79的记录，因为它们是最终值。


尝试将此用于WHERE子句：

展开 | 选择 | Wrap | 行号

使用JOIN执行此操作的方法是：

展开 | Selec t | Wrap | 行号

非常感谢Pat

想法也发生在我身上，但是一旦我意识到查询已经拿到了值49，我就把它解雇了。就是这样，我只是不明白为什么它没有拿起值69和79最终，时间不多了 - 毕竟这是世界杯年 - 我改变了tbl _％_ 2_grades中的值，但是你的想法很简单，我的这一次碰巧工作，因为没有学生碰巧有圆值现在出现两次：

low-perc;高四氯乙烯;等级

50; 60; c

60; 70; b

70; 80;一个


i知道我将很快改变它。

再次感谢提示

一切顺利

Dear all!
I have a database that stores student results - no, it''s not a chart-graph question :):):) via the back door.

Within this database, sits a query called "qry-yr10-end-of-year-results". This query contains the fields "student_ID", "Name" and "SumofWeighting_applied".

This "sumofWeighting_applied" is nothing other than a calculated numerical value, I won''t bore you with the resaons for its name.

The query returns 11 records, as it should do, and the SQL for the query is

Expand|Select|Wrap|Line Numbers

解决方案

Hi,

Different database environments treat the BETWEEN operator differently. Some of them use it in an inclusive manner, meaning that if you say WHERE a BETWEEN b AND c - it will actually include the values b and c. Others will return only values that lie between b and c, and will actually leave b and c out. I think it is very telling that it is not returning the records with 69 and 79, as those are end values.

Try using this for your WHERE clause instead:

Expand|Select|Wrap|Line Numbers

The way to do this using a JOIN is:

Expand|Select|Wrap|Line Numbers

Thanks so much Pat
the thought had occured to me too but i dismissed it as soon as i realised that the query had picked up the value 49. this being the case, i just couldn''t see why it didn''t pick up the values 69 and 79. in the end, running out of time - it is world-cup-year after all - i changed the values in tbl_%_2_grades but your idea is lots neater, mine just happens to work this time round because no student happens to have the round values which now appear twice:
low-perc; high-perc; grade
50; 60; c
60; 70; b
70; 80; a

i know i''ll have to change it soon.
Thanks again for the tip
all the best

这篇关于比较查询不返回所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！