select 编号 ,max(字段) from tb group by 编号 在你动态的时候用上面的格式
这种情况,应该按照后面每个字段生成一个包含行号的临时表 (5个),然后5个表 full join 就可以了。 稍有点复杂,不知道哪位另有高见
这是其中一个临时表: 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]
这么多个临时表如何join呢?
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
我测试了一下,这个把完全的相同编号的,合并为一行了,请看我要的合并效果。 比如 编号 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
把query发上来看看,应该是你写错了
应该是我的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 , 教师
没问题了。 是我的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)非常感谢,学习到了很多!
对于下面 的数值,为什么我的查询条件 WHERE (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL) 这样写,查询出来的是空值呢?
而采用 WHERE (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL ) 却可以查询到值
这个过滤条件怎么写? 用 WHERE NOT (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL) 还是过滤布了F2-F6 为空的情况
比如执行以下语句 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 , 教师返回值为
对以上做出一个补充,添加的查询条件是 WHERE 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 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 ...
改成 not f2 is null or not f3 is null or not ... 还是不可以。仍然可以查询出F2-F6 同时为null的行
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 的行
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
那个是我用网页输入的,输入错了。 即使是 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 的行 ,真的
from tb
group by 编号
在你动态的时候用上面的格式
稍有点复杂,不知道哪位另有高见
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]
这么多个临时表如何join呢?
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
我测试了一下,这个把完全的相同编号的,合并为一行了,请看我要的合并效果。
比如
编号 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
应该是我的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 ,
教师
是我的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)非常感谢,学习到了很多!
ISNULL(a.编号,isnull(b.编号,isnull(c.编号,ISNULL(d.编号,e.编号))))
编号要这么写 数据才全的。
其中,编号和教师都为null的情况,漏掉了
FROM ( SELECT a.编号 ,
a.教师 ,
F2 ,
F3 ,
F4 ,
F5 ,
F6 这里编号和教师 一定要取全 ,怎么可能有空编号和空教师呢 ,对不对 ?呵呵
对于下面 的数值,为什么我的查询条件
WHERE (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL)
这样写,查询出来的是空值呢?
WHERE (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL )
却可以查询到值
这个过滤条件怎么写?
用 WHERE NOT (f2 IS NULL AND f3 IS NULL AND f4 IS NULL AND f5 IS NULL AND f6 IS NULL)
还是过滤布了F2-F6 为空的情况
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 的都排除掉。
对以上做出一个补充,添加的查询条件是
WHERE 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 ...
改成
not f2 is null or not f3 is null or not ... 还是不可以。仍然可以查询出F2-F6 同时为null的行
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 的行
那个是我用网页输入的,输入错了。
即使是
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 的行 ,真的
是否有QQ,我发文件给你和测试SQL给你。
我的QQ :1273327738