左连接与访问枢轴输出 [英] Left join with access pivot output

问题描述

我有一张这样的桌子可以访问ms：

Hi,

I have a table like this in ms access:

empid score category
emp1      3       a
emp1      4       b
emp1      3       c
emp1      5       c
emp1      2       c

i想要输出为：

i want output as:

emp id a-score b-score avg-c-score
emp1     3        4       3.333

我必须用这个输出加入另一个表输出。



我有什么尝试过：



我使用了枢轴，但我无法获得平均部分。还有与其他表连接的枢轴正在给出错误：



TRANSFORM first（得分）

SELECT empid

FROM tbl_score

其中类别（'a'，'b'）

GROUP BY empid

PIVOT类别

I have to left join another table output with this output.

What I have tried:

I have used pivot but I am not able to get the avg part. Also left join with other table with the pivot is giving error:

TRANSFORM first(score)
SELECT empid
FROM tbl_score
where category in('a', 'b')
GROUP BY empid
PIVOT category

推荐答案

左连接？哪里？我没看到....



此外，你是在正确的卡车上。您必须用 AVG 替换 FIRST 。这就是全部。



试试这个：

Left join? Where? I do not see....

Besides, you're on the right truck. You have to replace FIRST with AVG. That's all.

Try this:

TRANSFORM AVG(t.score)
SELECT t.empid
FROM tbl_score AS t
GROUP BY t.empid
PIVOT t.category 

请注意，您无法在多个字段上转移数据...因此，无法获得 FIRST（（a ，b））和 AVG（c）在一个查询中。



详情请见： TRANSFORM语句（Microsoft Access SQL）  [Access 2007 Developer Reference] [ ^ ]

Note, that you cannot pivot data on multiple fields... So, it's impossible to get FIRST((a, b)) and AVG(c) in one query.

For further details, please see: TRANSFORM Statement (Microsoft Access SQL) [Access 2007 Developer Reference][^]

这篇关于左连接与访问枢轴输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！