从多个工作表中提取前五名的学生信息填写

zpy2

pyysm 发表于 2024-11-15 09:40
大神，谢谢您的帮忙，但我只是小白，会用一些简单的公式，这个代码我实在是不懂

这条SQL语句的目的是从 `consolidateSheet` 表中筛选出符合特定条件的记录，并对结果进行排序和格式化输出。下面是对这条SQL语句的逐步解析：

```sql
SELECT
    ROW_NUMBER() OVER () AS 序号,          -- 生成一个序号列，按结果的顺序编号
    SUBSTR(f02, 1, 1) AS 年级,            -- 从字段 f02 中提取第一个字符作为年级
    f02 AS 班级,                          -- 直接显示字段 f02 作为班级
    f05 AS 姓名,                          -- 直接显示字段 f05 作为姓名
    f11 AS 级排名                        -- 直接显示字段 f11 作为级排名
FROM
    consolidateSheet                      -- 从表 consolidateSheet 中查询
WHERE
    CAST(f11 AS INT) < 6                  -- 过滤 f11 字段的值，确保其转换为整数后小于 6
    AND (f04 LIKE '6%' OR f04 LIKE '5%')   -- 进一步过滤 f04 字段，筛选出以 6 或 5 开头的记录
ORDER BY
    年级, f11;                            -- 按年级（即 f02 的第一个字符）和级排名（即 f11）排序
```

### 解析：
1. **`ROW_NUMBER() OVER () AS 序号`**:
   - `ROW_NUMBER()` 是一个窗口函数，用于为查询结果集中的每一行分配一个唯一的行号。
   - `OVER ()` 表示不指定排序规则，这意味着行号的生成顺序与查询结果中的顺序一致。
   - `序号` 是该列的别名，表示每行的序号。

2. **`SUBSTR(f02, 1, 1) AS 年级`**:
   - `SUBSTR(f02, 1, 1)` 从字段 `f02` 中提取出第一个字符。
   - 这个第一个字符被作为年级输出，假设 `f02` 是班级字段，通常会通过这个字段的第一个字符来表示年级。

3. **`f02 AS 班级`**:
   - 直接将字段 `f02` 显示为班级。

4. **`f05 AS 姓名`**:
   - 直接将字段 `f05` 显示为姓名。

5. **`f11 AS 级排名`**:
   - 直接将字段 `f11` 显示为级排名。

6. **`WHERE CAST(f11 AS INT) < 6`**:
   - 该条件确保只有 `f11` 转换为整数后小于 6 的记录被选中。`f11` 可能存储的是数字格式的字符串，`CAST(f11 AS INT)` 将其转换为整数进行比较。

7. **`AND (f04 LIKE '6%' OR f04 LIKE '5%')`**:
   - 该条件要求 `f04` 字段的值以 '6' 或 '5' 开头。`LIKE '6%'` 和 `LIKE '5%'` 是使用通配符 `%` 来匹配以 6 或 5 开头的值。

8. **`ORDER BY 年级, f11`**:
   - 最终的结果按 `年级` 和 `f11` 进行排序。`年级` 是从 `f02` 中提取出来的第一个字符，而 `f11` 是级排名字段。先按年级排序，如果年级相同，再按级排名排序。

### 总结：
这条SQL语句的目的是从 `consolidateSheet` 表中查询那些 `f11` 小于 6 且 `f04` 以 '5' 或 '6' 开头的记录，并返回这些记录的序号、年级、班级、姓名和级排名。查询结果按年级和级排名排序。

逐渐陌生

参考公式：=LET(v,VSTACK('501:602'!B3:H27),m,FILTER(v,TAKE(v,,1)<>0),DROP(REDUCE("",{5,6},LAMBDA(x,y,VSTACK(x,LET(a,FILTER(m,--LEFT(TAKE(m,,1),1)=y),t,TAKE(a,,-1),m,MATCH(t,SORT(t,1,-1),),FILTER(HSTACK(LEFT(TAKE(a,,1),1),TAKE(a,,2),m),m<=5))))),1))

edwin11891

pyysm 发表于 2024-11-15 09:45
大神，这个公式我复制到单元格中，好像不能正确计算，您能帮忙看看吗？

版本低了，升级一下EXCEL版本吧

pyysm

edwin11891 发表于 2024-11-15 12:49
版本低了，升级一下EXCEL版本吧

专业增强版2021，还低吗

博爱风雨同舟

=LET(s,VSTACK('501:602'!B3:I26),sa,TAKE(s,,1),sb,CHOOSECOLS(s,6),FILTER(CHOOSECOLS(s,{1,2,6}),IFNA(sb>=VLOOKUP(sa,GROUPBY(sa,sb,LAMBDA(X,@INDEX(SORT(X,,-1),5)),,0,,sb>0),2,),0)))

博爱风雨同舟

=LET(s,VSTACK('501:602'!B3:I26),sa,TAKE(s,,1),sb,CHOOSECOLS(s,6),FILTER(CHOOSECOLS(s,{1,2,6}),IFNA(sb>=VLOOKUP(sa,GROUPBY(sa,sb,LAMBDA(X,@LARGE(IF(FREQUENCY(X,X),X),5)),,0,,sb>0),2,),0)))
中国式，同成绩一名

博爱风雨同舟

=LET(s,VSTACK('501:602'!B3:I26),sa,TAKE(s,,1),sb,CHOOSECOLS(s,6),FILTER(CHOOSECOLS(s,{1,2,6}),IFNA(sb>=VLOOKUP(sa,GROUPBY(sa,sb,LAMBDA(X,@AGGREGATE(14,3,X,5)),,0,,sb>0),2,),0)))
举一反三

pyysm

博爱风雨同舟 发表于 2024-11-18 15:09
=LET(s,VSTACK('501:602'!B3:I26),sa,TAKE(s,,1),sb,CHOOSECOLS(s,6),FILTER(CHOOSECOLS(s,{1,2,6}),IFNA(s ...

版本不支持啊，专业增强版2021的，提示还是低版本