本帖最后由 talhanwenhui 于 2012-11-08 15:33:35 编辑

解决方案 »

  1.   

    select 编号 ,max(字段)
    from tb
    group by 编号
    在你动态的时候用上面的格式
      

  2.   

    这种情况,应该按照后面每个字段生成一个包含行号的临时表 (5个),然后5个表 full join 就可以了。
    稍有点复杂,不知道哪位另有高见
      

  3.   

    这是其中一个临时表:
     select 编号,[14:00],ROW_NUMBER() over(partition by 编号 order by [14:00]) as rowid
     from TB
     where not [14:00] is null
     group by 编号,[14:00]
      

  4.   


    这么多个临时表如何join呢?
      

  5.   

    select ISNULL(a.编号,isnull(b.编号,isnull(c.编号,ISNULL(d.编号,e.编号)))),各个时间点补全...
    from TB1 as a full join TB2 as b on a.编号=b.编号 and a.rowid=b.rowid
    full join TB3 as c on on a.编号=c.编号 and a.rowid=c.rowid
    full join TB3 as d on on a.编号=d.编号 and a.rowid=d.rowid
    full join TB3 as e on on a.编号=e.编号 and a.rowid=e.rowid
      

  6.   


    我测试了一下,这个把完全的相同编号的,合并为一行了,请看我要的合并效果。
    比如
    编号     14:00      15:00
    1           a         b
    1          null       c
    1           d         null按照你说的方法,最后合并结果为可能为:
    编号     14:00      15:00
    1           d          c而我要的效果是
    编号     14:00      15:00
    1          a         b
    1          d          c
      

  7.   

    把query发上来看看,应该是你写错了
      

  8.   


    应该是我的where 条件错了,查询条件会出现f2,f3,f4,f5,f6 都为null的情况,排除掉就可以了。SELECT  *
    FROM    ( SELECT    a.编号 ,
                        a.教师 ,
                        F2 ,
                        F3 ,
                        F4 ,
                        F5 ,
                        F6
              FROM      ( SELECT    编号 ,
                                    教师 ,
                                    F2 ,
                                    ROW_NUMBER() OVER ( PARTITION BY 编号, 教师 ORDER BY F2 ) AS rowid
                          FROM      tbl
                          WHERE     NOT F2 IS NULL
                          GROUP BY  编号 ,
                                    教师 ,
                                    F2
                        ) A
                        FULL JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F3 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F3 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F3 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F3
                                  ) B ON A.编号 = B.编号
                                         AND A.教师 = B.教师
                                         AND A.rowid = B.rowid
                        FULL JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F4 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F4 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F4 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F4
                                  ) C ON A.编号 = C.编号
                                         AND A.教师 = C.教师
                                         AND A.rowid = C.rowid
                        FULL JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F5 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F5 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F5 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F5
                                  ) D ON A.编号 = D.编号
                                         AND A.教师 = D.教师
                                         AND A.rowid = D.rowid
                        FULL JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F6 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F6 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F6 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F6
                                  ) E ON A.编号 = E.编号
                                         AND A.教师 = E.教师
                                         AND A.rowid = E.rowid
            ) AS F
    WHERE   编号 IS NOT NULL
            AND ( f2 IS NOT NULL
                  AND f3 IS NOT NULL
                  AND f4 IS NOT NULL
                  AND f5 IS NOT NULL
                  AND f6 IS NOT NULL
                )
    ORDER BY F.编号 DESC ,
            教师
      

  9.   

    没问题了。
    是我的where 条件写错了。
    应该是 WHERE   编号 IS NOT NULL AND NOT ( F2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL)非常感谢,学习到了很多!
      

  10.   

    还是你写的有问题
    ISNULL(a.编号,isnull(b.编号,isnull(c.编号,ISNULL(d.编号,e.编号))))
    编号要这么写 数据才全的。
      

  11.   


    其中,编号和教师都为null的情况,漏掉了 
      

  12.   

    SELECT  *
     FROM    ( SELECT    a.编号 ,
                         a.教师 ,
                         F2 ,
                         F3 ,
                         F4 ,
                         F5 ,
                         F6   这里编号和教师 一定要取全 ,怎么可能有空编号和空教师呢 ,对不对 ?呵呵 
      

  13.   


    对于下面 的数值,为什么我的查询条件
     WHERE    (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL) 
    这样写,查询出来的是空值呢? 
      

  14.   

    而采用 
     WHERE    (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL ) 
    却可以查询到值
      

  15.   


    这个过滤条件怎么写?
    用 WHERE  NOT  (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL)
    还是过滤布了F2-F6 为空的情况
      

  16.   

    比如执行以下语句
    SELECT  *
    FROM    ( SELECT     ISNULL(a.编号,isnull(b.编号,isnull(c.编号,ISNULL(d.编号,e.编号)))) AS 编号 ,
                        ISNULL(a.教师,isnull(b.教师,isnull(c.教师,ISNULL(d.教师,e.教师)))) AS 教师 ,
                        F2 ,
                        F3 ,
                        F4 ,
                        F5 ,
                        F6
              FROM      ( SELECT    编号 ,
                                    教师 ,
                                    F2 ,
                                    ROW_NUMBER() OVER ( PARTITION BY 编号, 教师 ORDER BY F2 ) AS rowid
                          FROM      tbl
                          WHERE     NOT F2 IS NULL
                          GROUP BY  编号 ,
                                    教师 ,
                                    F2
                        ) A
                        FULL outer JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F3 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F3 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F3 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F3
                                  ) B ON A.编号 = B.编号
                                         AND A.教师 = B.教师
                                         AND A.rowid = B.rowid
                        FULL outer JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F4 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F4 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F4 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F4
                                  ) C ON A.编号 = C.编号
                                         AND A.教师 = C.教师
                                         AND A.rowid = C.rowid
                        FULL outer JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F5 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F5 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F5 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F5
                                  ) D ON A.编号 = D.编号
                                         AND A.教师 = D.教师
                                         AND A.rowid = D.rowid
                        FULL OUTER JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F6 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F6 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F6 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F6
                                  ) E ON A.编号 = E.编号
                                         AND A.教师 = E.教师
                                         AND A.rowid = E.rowid
            ) AS F
    ORDER BY F.编号 DESC ,
            教师返回值为
     
    然后对以上SQL语句,再添加一个查询条件,返回的结果还是这样
    现在是想要把F2-F6 为 NULL 的都排除掉。
      

  17.   


    对以上做出一个补充,添加的查询条件是
    WHERE NOT   (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL)
      

  18.   

    NOT   (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL)
    等同于 not f2 is null and not f3 is null and not .... 对吗?
    应该改成 not f2 is null or not f3 is null or not ...
      

  19.   


    改成
    not f2 is null or not f3 is null or not ...  还是不可以。仍然可以查询出F2-F6 同时为null的行
      

  20.   


    SELECT  *
    FROM    ( SELECT     ISNULL(a.编号,isnull(b.编号,isnull(c.编号,ISNULL(d.编号,e.编号)))) AS 编号 ,
                        ISNULL(a.教师,isnull(b.教师,isnull(c.教师,ISNULL(d.教师,e.教师)))) AS 教师 ,
                        F2 ,
                        F3 ,
                        F4 ,
                        F5 ,
                        F6
              FROM      ( SELECT    编号 ,
                                    教师 ,
                                    F2 ,
                                    ROW_NUMBER() OVER ( PARTITION BY 编号, 教师 ORDER BY F2 ) AS rowid
                          FROM      tbl
                          WHERE     NOT F2 IS NULL
                          GROUP BY  编号 ,
                                    教师 ,
                                    F2
                        ) A
                        FULL outer JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F3 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F3 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F3 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F3
                                  ) B ON A.编号 = B.编号
                                         AND A.教师 = B.教师
                                         AND A.rowid = B.rowid
                        FULL outer JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F4 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F4 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F4 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F4
                                  ) C ON A.编号 = C.编号
                                         AND A.教师 = C.教师
                                         AND A.rowid = C.rowid
                        FULL outer JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F5 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F5 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F5 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F5
                                  ) D ON A.编号 = D.编号
                                         AND A.教师 = D.教师
                                         AND A.rowid = D.rowid
                        FULL OUTER JOIN ( SELECT  编号 ,
                                            教师 ,
                                            F6 ,
                                            ROW_NUMBER() OVER ( PARTITION BY 编号,
                                                                教师 ORDER BY F6 ) AS rowid
                                    FROM    tbl
                                    WHERE   NOT F6 IS NULL
                                    GROUP BY 编号 ,
                                            教师 ,
                                            F6
                                  ) E ON A.编号 = E.编号
                                         AND A.教师 = E.教师
                                         AND A.rowid = E.rowid
            ) AS F
    WHERE NOT F2 IS NULL OR NOT F3 IS NULL OR NOT F4 IS NULL OR NOT F5 IS NOT OR NOT F6 IS NULL
    ORDER BY F.编号 DESC ,
            教师
    --即使把查询条件改成 
    WHERE NOT F2 IS NULL AND NOT F3 IS NULL AND NOT F4 IS NULL AND NOT F5 IS NOT AND NOT F6 IS NULL还是出现F2-F6 同时为 NULL 的行
      

  21.   

    WHERE NOT F2 IS NULL OR NOT F3 IS NULL OR NOT F4 IS NULL OR NOT F5 IS NULL OR NOT F6 IS NULL
      

  22.   


    那个是我用网页输入的,输入错了。
    即使是
    WHERE NOT F2 IS NULL OR NOT F3 IS NULL OR NOT F4 IS NULL OR NOT F5 IS NULL OR NOT F6 IS NULL 还是出现F2-F6 同时为 NULL 的行 ,真的 
      

  23.   


    是否有QQ,我发文件给你和测试SQL给你。
    我的QQ :1273327738