给MSSQL查询结果追加行号

最新推荐文章于 2024-04-11 09:51:51 发布

转载最新推荐文章于 2024-04-11 09:51:51 发布 · 3.2k 阅读

标签

#join

SQL 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个复杂的 SQL 查询案例，该查询涉及多个表的联接操作、子查询以及窗口函数的使用，目的是为了从不同的角度对数据进行聚合和排序，以便于进行更深入的数据分析。

SELECT ROWNO = ROW_NUMBER() OVER(PARTITION BY T1.GUANLINO, T1.FAZHUDATE, T1.SHIJIDATE, T1.RUKUDATE ORDER BY T1.CAILIAOCD, T1.CAILIAONM, T1.CAILIAOLOTNO, T1.QTY, T1.DEMO),
         T1.SUBROWNO,
         T1.GUANLINO,
         T1.FAZHUDATE,
         T1.SHIJIDATE,
         T1.RUKUDATE,
         T1.CAILIAOCD,
         T1.CAILIAONM,
         T1.KIND,
         T1.CAILIAOLOTNO,
         T1.QTY,
         T1.DEMO
    FROM (SELECT SUBROWNO = ROW_NUMBER() OVER(PARTITION BY PZ.GUANLINO, ISNULL(FZ.FAZHUDATE, ''), ISNULL(PZ.SHIJIDATE, ''), ISNULL(RKZS.RUKUDATE, ''), PZ.CAILIAOCD, CM.CAILIAONM ORDER BY PZ.CAILIAOLOTNO),
                 PZ.GUANLINO,
                 ISNULL(FZ.FAZHUDATE, '') AS FAZHUDATE,
                 ISNULL(PZ.SHIJIDATE, '') AS SHIJIDATE,
                 ISNULL(RKZS.RUKUDATE, '') AS RUKUDATE,
                 PZ.CAILIAOCD,
                 CM.CAILIAONM,
                 '' AS KIND,
                 PZ.CAILIAOLOTNO,
                 ISNULL(PZ.LIANGPINSUM, 0) AS QTY,
                 ISNULL(PZ.[REMARK ], '') AS DEMO,
                 FZ.GOURUXIANCD,
                 FZ.GOURUXIANNM
            FROM T_PINZHI PZ
            LEFT JOIN T_CAILIAORKZS RKZS ON RKZS.JIANSHOUNO = PZ.JIANSHOUNO
                                        AND RKZS.CAILIAOCD = PZ.CAILIAOCD
            LEFT JOIN T_CAILIAO_MST CM ON CM.CAILIAOCD = PZ.CAILIAOCD
            LEFT JOIN (SELECT FZR.*
                        FROM T_FAZHU_RECORD FZR
                       INNER JOIN (SELECT FAZHUNO,
                                          CAILIAOCD,
                                          MAX(ISNULL(CAOZUOKB, 0)) AS CAOZUOKB
                                     FROM T_FAZHU_RECORD
                                    GROUP BY FAZHUNO, CAILIAOCD) RS ON RS.FAZHUNO = FZR.FAZHUNO
                                                                   AND RS.CAILIAOCD = FZR.CAILIAOCD) FZ ON FZ.FAZHUNO = PZ.GUANLINO
                                                                                                       AND FZ.CAILIAOCD = PZ.CAILIAOCD
            LEFT JOIN T_CAILIAOJRJC T_JR ON T_JR.GUANLINO = PZ.GUANLINO
            LEFT JOIN T_YOUDA_MST T_YR ON T_YR.YOUDACD = T_JR.MUBIAOXIAN
            LEFT JOIN T_CAILIAOJRJC T_JC ON T_JC.GUANLINO = PZ.GUANLINO
            LEFT JOIN T_YOUDA_MST T_YC ON T_YC.YOUDACD = T_JC.MUBIAOXIAN
           WHERE ISNULL(RKZS.FLG, 0) <> 4
         ) T1