无法理解这个问题！ [英] Cannot get my head around this query!

问题描述

为了我的利益，忽略它的颜色。此查询连接两个表并输出第三个表。

Ignore the coloring it was for my benefit. This query joins two tables and puts out a third table.

基本上，tblContractAge是一个年龄列表以及要分配这些年龄的人的存储桶。 Cognos是一个特定年龄段的人的表。这是一个巨大的过度简化，但就足够了。

Basically tblContractAge is a list of ages and the buckets to which people of those ages are to be assigned. Cognos is a table of people of certain ages. This is a vast oversimplification but will suffice.

即使没有人（在Cognos中）表具有桶的年龄（在tblContract中），客户也希望结果表包含行，这不会发生。在这种情况下，不输出任何行。我对DISTINCT和GROUP
BY的效果以及它如何与ON和WHERE结合起来感到困惑。据我了解，ON执行连接，WHERE过滤连接的表。但DISTINCT和GROUP BY什么时候生效？谁有人澄清这个？

The customer is expecting the resulting table to contain rows even if no people (in Cognos) table have the age for the bucket (in tblContract), This is not happening. No rows are output in this case. I'm confused as to the effect of the DISTINCT and GROUP BY and how this works combined with the ON and WHERE. As I understand it, the ON does the join and the WHERE filters the joined tables. But when does the DISTINCT and GROUP BY come into effect? Can anyone clarify this?

SELECT DISTINCT tblContractAge.Seq AS [order]，tblContractAge.Seq，tblContractAge.CMS，Cognos.Primary_Ahcccs_Id AS [Primary Ahcccs Id ]，Cognos.Title，tblContractAge.Age_range，tblContractAge.Service_Delivery，IIf（[cognos]。[title] ='Title XXI'，0，IIf（[FPL_Percent] ='0
- 100'，1,0 ））AS [0-100]，IIf（[cognos]。[title] ='Title XXI'，1，IIf（[FPL_Percent] ='101 - 200'，1,0））AS [101-200]， 1 AS Ever_Enrolled，Cognos.New9 AS New，Cognos.Disenrolled，IIf（[Disenrolled] = 1,0,1）AS EligAtEnd，Cognos.Member_Months，Cognos.Sex，Cognos.Race，
Cognos.Ethnicity INTO < span style ="color：＃9b00d3"> mtblDistribute

FROM tblContractAge LEFT JOIN Cognos ON（tblContractAge。[Contract Type] = Cognos。[Contract_Type]）AND（tblContractAge.Title = Cognos.Title）

FROM tblContractAge LEFT JOIN Cognos ON (tblContractAge.[Contract Type] = Cognos.[Contract_Type]) AND (tblContractAge.Title = Cognos.Title)

WHERE（（（IIf（[cognos]。[age] -Fix（[cognos]。[age]）> 0.999，Ro und（[cognos]。[age]，0），Fix（[cognos]。[age]）））= [tblContractAge]。[age]））OR（（Cognos.Title ='Title XXI adult'））

GROUP BY tblContractAge.Seq，tblContractAge.CMS，Cognos.Primary_Ahcccs_Id，Cognos.Title，tblContractAge.Age_range，tblContractAge.Service_Delivery，IIf（ [cognos]。[title] ='Title XXI'，0，IIf（[FPL_Percent] ='0 - 100'，1,0）），IIf（[cognos]。[title] ='Title
XXI '，1，IIf（[FPL_Percent] ='101 - 200'，1,0）），1，Cognos.New9，Cognos.Disenrolled，IIf（[Disenrolled] = 1,0,1），Cognos.Member_Months，Cognos .Sex，Cognos.Race，Cognos.Ethnicity，tblContractAge.Seq;

推荐答案

 据我所知，ON执行连接，WHERE筛选连接的表。但DISTINCT和GROUP BY什么时候生效？任何人都可以澄清这个吗？

 As I understand it, the ON does the join and the WHERE filters the joined tables. But when does the DISTINCT and GROUP BY come into effect? Can anyone clarify this?

当有多个相同的记录时，DISTINCT将输出一条记录。

DISTINCT is to output a single record when there are multiple identical ones.

GROUP BY是执行SUM，MIN，MAX，AVG，COUNT等时的累积级别。

GROUP BY is the roll up level when doing SUM, MIN, MAX, AVG, COUNT, etc.

我不明白你在做什么[Cognos]。[年龄] WHERE&NBSP;&NBSP;你有小数年龄吗？

I do not understand what you are doing with the [Cognos].[age] in the WHERE.   Do you have decimal ages?

这篇关于无法理解这个问题！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！