sqlserver 分组查询

sqlserver 分组查询物料历史订单最高金额 ,最高金额供应商 ，最小金额，最小金额供应商

```sql
SELECT
    t2.fnumber,
    t3.fname,
    t3.FSPECIFICATION,
    ( SELECT fname FROM T_ORG_ORGANIZATIONS_L WHERE FORGID = FPURCHASEORGID AND fname IS NOT NULL AND fname != '' ) zz,
    (
    SELECT TOP
        1 a5.fname 
    FROM
        T_PUR_POORDERENTRY a1
        LEFT JOIN T_BD_MATERIAL a2 ON a1.FMATERIALID = a2.FMATERIALID
        LEFT JOIN T_PUR_POORDERENTRY_F a3 ON a1.FENTRYID = a3.FENTRYID
        LEFT JOIN t_PUR_POOrder a4 ON a1.fid = a4.fid
        LEFT JOIN T_BD_SUPPLIER_L a5 ON a4.FSUPPLIERID = a5.FSUPPLIERID 
    WHERE
        a2.fnumber = t2.fnumber 
        AND a4.FPURCHASEORGID = t0.FPURCHASEORGID 
    ORDER BY
        FPRICE 
    ) zdjgys,
    MIN ( FPRICE ) zdj,
    (
    SELECT TOP
        1 a5.fname 
    FROM
        T_PUR_POORDERENTRY a1
        LEFT JOIN T_BD_MATERIAL a2 ON a1.FMATERIALID = a2.FMATERIALID
        LEFT JOIN T_PUR_POORDERENTRY_F a3 ON a1.FENTRYID = a3.FENTRYID
        LEFT JOIN t_PUR_POOrder a4 ON a1.fid = a4.fid
        LEFT JOIN T_BD_SUPPLIER_L a5 ON a4.FSUPPLIERID = a5.FSUPPLIERID 
    WHERE
        a2.fnumber = t2.fnumber 
        AND a4.FPURCHASEORGID = t0.FPURCHASEORGID 
    ORDER BY
        FPRICE DESC 
    ) zgjgys,
    MAX ( FPRICE ) zgj 
FROM
    T_PUR_POORDERENTRY t1
    LEFT JOIN t_PUR_POOrder t0 ON t1.fid = t0.fid
    LEFT JOIN T_BD_MATERIAL t2 ON t1.FMATERIALID = t2.FMATERIALID
    LEFT JOIN T_BD_MATERIAL_L t3 ON t3.FMATERIALID = t1.FMATERIALID
    LEFT JOIN T_PUR_POORDERENTRY_F t4 ON t4.FENTRYID = t1.FENTRYID 
GROUP BY
    t2.fnumber,
    t3.fname,
    t3.FSPECIFICATION,
    FPURCHASEORGID```

现在是这样查的 不过速度太慢 不知道怎么更快一点 数据量刚好100万

【相关推荐】

• 帮你找了个相似的问题, 你可以看下: https://ask.csdn.net/questions/7669302
• 你也可以参考下这篇文章：SQL数据库语言基础之SqlServer数据库原理与设计课程期末复习-条件查询实例练习【学生表、选课表、家庭作业表】
• 除此之外, 这篇博客: SQLServer【恢复挂起的解决方案】附加文件时候的提示“无法重新生成日志，原因是数据库关闭时存在打开的事务/用户，该数据库没有检查点或者该数据库是只读的。 ”中的 第一步：新建一个同名的数据库 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
  

  

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^

优化建议：

1. 避免使用子查询：在原代码中，使用了两个子查询来获取最低价和最高价对应的供应商名称。这种写法通常会比较慢，可以尝试使用 JOIN 或者 EXISTS 等方式替换掉子查询，例如：

SELECT TOP 1 a5.fname 
FROM T_PUR_POORDERENTRY a1
LEFT JOIN T_BD_MATERIAL a2 ON a1.FMATERIALID = a2.FMATERIALID
LEFT JOIN T_PUR_POORDERENTRY_F a3 ON a1.FENTRYID = a3.FENTRYID
LEFT JOIN t_PUR_POOrder a4 ON a1.fid = a4.fid
LEFT JOIN T_BD_SUPPLIER_L a5 ON a4.FSUPPLIERID = a5.FSUPPLIERID 
WHERE a2.fnumber = t2.fnumber AND a4.FPURCHASEORGID = t0.FPURCHASEORGID 
ORDER BY FPRICE

-- 可以替换为

SELECT TOP 1 a5.fname 
FROM T_PUR_POORDERENTRY a1
LEFT JOIN T_BD_MATERIAL a2 ON a1.FMATERIALID = a2.FMATERIALID
LEFT JOIN T_PUR_POORDERENTRY_F a3 ON a1.FENTRYID = a3.FENTRYID
LEFT JOIN t_PUR_POOrder a4 ON a1.fid = a4.fid
LEFT JOIN T_BD_SUPPLIER_L a5 ON a4.FSUPPLIERID = a5.FSUPPLIERID 
WHERE a2.fnumber = t2.fnumber AND a4.FPURCHASEORGID = t0.FPURCHASEORGID 
AND FPRICE = (SELECT MIN(FPRICE) FROM T_PUR_POORDERENTRY WHERE FMATERIALID = a1.FMATERIALID AND FPURCHASEORGID = a4.FPURCHASEORGID)

-- 或者

SELECT a5.fname 
FROM T_PUR_POORDERENTRY a1
LEFT JOIN T_BD_MATERIAL a2 ON a1.FMATERIALID = a2.FMATERIALID
LEFT JOIN T_PUR_POORDERENTRY_F a3 ON a1.FENTRYID = a3.FENTRYID
LEFT JOIN t_PUR_POOrder a4 ON a1.fid = a4.fid
LEFT JOIN T_BD_SUPPLIER_L a5 ON a4.FSUPPLIERID = a5.FSUPPLIERID 
WHERE a2.fnumber = t2.fnumber AND a4.FPURCHASEORGID = t0.FPURCHASEORGID 
AND FPRICE = (SELECT MIN(FPRICE) FROM T_PUR_POORDERENTRY WHERE FMATERIALID = a1.FMATERIALID AND FPURCHASEORGID = a4.FPURCHASEORGID)

2. 使用 JOIN 优化查询：在原代码中，使用了多个 LEFT JOIN，可能会导致查询性能下降。可以尝试使用 INNER JOIN 或者 LEFT JOIN + WHERE 子句的方式进行 JOIN 操作，例如：

SELECT a5.fname 
FROM T_PUR_POORDERENTRY a1
INNER JOIN T_BD_MATERIAL a2 ON a1.FMATERIALID = a2.FMATERIALID
INNER JOIN t_PUR_POOrder a4 ON a1.fid = a4.fid
INNER JOIN T_BD_SUPPLIER_L a5 ON a4.FSUPPLIERID = a5.FSUPPLIERID 
WHERE a2.fnumber = t2.fnumber AND a4.FPURCHASEORGID = t0.FPURCHASEORGID 
AND FPRICE = (SELECT MIN(FPRICE) FROM T_PUR_POORDERENTRY WHERE FMATERIALID = a1.FMATERIALID AND FPURCHASEORGID = a4.FPURCHASEORGID)

3. 索引优化：对于经常用于查询和筛选数据的列，可以考虑创建索引来提高查询性能。在这个查询中，可以尝试为 T_PUR_POORDERENTRY 表的 FMATERIALID 列和 T_PUR_POOrder 表的 FPURCHASEORGID 列创建索引。

4. 分页查询：如果您的查询结果集非常大，可以考虑使用分页查询来减少查询时间和网络传输时间。在 SQL Server 中，可以使用 OFFSET FETCH 或者 ROW_NUMBER() OVER() 等方式进行分页查询。