交叉表数据与空值的比较 [英] Crosstab data comparisons with nulls
问题描述
我有一张桌子，Times。字段是：马，轨道，HTime
基本上，我的目标是找出任何轨道的时间之间的差异，只使用在2个用户中的每个用户运行的马匹 - 选择的曲目。
我的数据表现在看起来像这样:(为简单起见，它只有3个曲目;实际上超过100个）
Horse1 ... CT ... 52.50
Horse1 ... LRL ... 53.50
Horse1 ... PIM ... 53.00
Horse2 ... CT ... 53.00
Horse2 ... LRL ... 53.25
Horse3 ... LRL ... 52.95
Horse3 ... PIM ... 53.25
Horse4 ... PIM ... 53.15
Horse4 ... CT ... 52.75
我已经尝试过一个名为Test的交叉路径，rowheading是Horse，listheading是Track，数据填充是每条赛道上每匹马的平均HTime。
我想做的虽然是一个方法，所以用户可以输入2个曲目，只获得所有时间的平均时差在两条选定的赛道上奔跑的马匹。如果一匹马没有跑到两条轨道上，他的时间就不算计算了。
是否有一种方式允许用户选择哪些列很重要（即CT和LRL或用户希望从潜在的100多列中获得的任何组合）然后只计算跑两条赛道的马？
我在这里使用交叉表是错误的吗？我不是代码智能的，但如果给出了前进的方法，可以得到实例。
I have a table, Times. fields are: Horse, Track, HTime
Basically, my goal is to find out the difference between any track''s timing, using only horses that have run at each of the 2 user-selected tracks.
My data table looks like this right now: (for simplicity sake, it has only 3 tracks; there are in actuality over 100)
Horse1 ... CT ... 52.50
Horse1 ... LRL ... 53.50
Horse1 ... PIM ... 53.00
Horse2 ... CT ... 53.00
Horse2 ... LRL ... 53.25
Horse3 ... LRL ... 52.95
Horse3 ... PIM ... 53.25
Horse4 ... PIM ... 53.15
Horse4 ... CT ... 52.75
I have tried the avenue of a crosstab called Test, rowheading is the Horse, columnheading is the Track and data-fills is the average HTime for each horse at each track.
What I want to do though is figure a method so a user can put in 2 tracks and get only the average difference in times for all horses that have run at both selected tracks. If a horse hasn''t run at both tracks, his times aren''t calculated.
Is there a manner to allow the user to select which columns matter (ie, CT and LRL or any combination the user desires out of a potential 100+ columns) and then only count the horses that ran both tracks?
Am I going the wrong way here with crosstabs? I am not code-smart but can get examples to work if given a way forward.
推荐答案
我们首先要看一个子查询来提供按[分组的数据]马]选择标准：
We need first to look at getting a subquery to provide data grouped by [Horse] with selection criteria of :
我已经提供了相当远的信息，但是我我仍然无法确定伯爵（*）的来源。
我走得很慢，但这里是我目前的SQL代码。
I have gotten moderately far off the information you''ve provided, however I''m still unable to determine where the Count(*) comes from.
I''m going very slowly, but here''s the SQL code i have currently.
我猜这样获得的数据集应该是自连接的，以便在一条记录中获得每匹马的时间，从而可以计算差异。这个，BTW，过滤数据集，为马匹提供两条轨道结果的记录。
如下所示：
让我们说您发布的查询以名称qry保存
I guess the dataset thus obtained should be self-joined to get times per horse in one record thus making possible to calculate differences. This, BTW, filters dataset to give records for horses having results for both tracks.
Something like the following:
let us say the query you''ve posted is saved under name "qry"
这篇关于交叉表数据与空值的比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
