本文介绍了如何删除列中的Null?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 嗨我是SQL Server 2008的新手r2 实际上,我有一张这样的桌子 STUDENTID STUDENTSUBJECT YEAR1 MARKS1 RANK1 YEAR2 MARKS2 1 MATHS 2013-06-07-2014-04-05 10 DGRADE NULL NULL 1 BPC NULL NUll NULL 2012-06-11-2013-05-14 6 RANK2 YEAR3 MARKS3 RANK3 null null null null DGRADE null null null i worte a像这样查询 SELECT max(STUDENTID) as STUDENTID,MAX(STUDENTSUBJECT) AS STUDENTSUBJECT,MAX(YEAR1),MAX(MARKS1),MAX(RANK1),MAX (YAER2),MAX(MARKS2),MAX(RANK2),MAX(YEAR3),MAX(MARKS3),MAX(RANK3) FROM STUDENT GROUP BY STUDENTSUBJECT 以上查询适用于SAME SUBJECT(MATHS,MATHS等),但它不适用于上面提到的差异主题。 请帮帮我 除外o / p: o / p: 学生学生项目年份1 MARKS1 RANK1 YEAR2 MARKS2 1 MATHS 2013-06-07-2014-04-05 10 DGRADE 2012-06-11-2013-05-14 null 1 BPC NULL NUll NULL null 6 RANK2 YEAR3 MARKS3 RANK3 null null null null DGrade null null nul i必须得到如上表所示的第一行全部一个解决方案 使用ISNULL()内置函数。 SELECT MAX(STUDENTID) AS STUDENTID, MAX(STUDENTSUBJECT) AS STUDENTSUBJECT, ISNULL(MAX(YEAR1), )作为 Year1, ISNULL(MAX(MARKS1), ) 作为 Marks1, ISNULL(MAX(RANK1), ) As Rank1, ISNULL(MAX(YEAR2), )作为 Year2, ISNULL(MAX(MARKS2), )作为 Marks2, ISNULL( MAX(RANK2), ) As Rank2, ISNULL(MAX(YEAR3), )作为 Year3, ISNULL(MAX(MARKS3), ) As Marks3, ISNULL(MAX(RANK3), )作为 Rank3 FROM 学生 GROUP BY STUDENTSUBJECT I不明白你为什么使用MAX(StudentID)和MAX(StudentSubject)。也许您应该使用改善问题并准确解释您想要获得的结果(简单条款中的要求声明)。 SELECT STUDENTID ,STUDENTSUBJECT ,ISNULL(YEAR1,'')AS'YEAR1',ISNULL(MARKS1,'')AS'MARKS1',ISNULL(RANK1,'')AS' RANK1',ISNULL((选择MAX(Year2)FROM Table26),'')AS'YEAR2',ISNULL(MARKS2,'')AS'MARKS2',ISNULL(RANK2) ,'')AS'RANK2',ISNULL(YEAR3,'')AS'YEAR3',ISNULL(MARKS3,'')AS'MARKS3',ISNULL(RANK3,' ')AS'RANK3' INTO #TableName FROM Table26 UPDATE #TableName SET YEAR2 ='' WHERE STUDENTSUBJECT ='BPC' HiI am new to SQL Server 2008 r2Actually, I have a table like thisSTUDENTID STUDENTSUBJECT YEAR1 MARKS1 RANK1 YEAR2 MARKS21 MATHS 2013-06-07-2014-04-05 10 DGRADE NULL NULL1 BPC NULL NUll NULL 2012-06-11-2013-05-14 6RANK2 YEAR3 MARKS3 RANK3null null null nullDGRADE null null nulli worte a query like thisSELECT max(STUDENTID)as STUDENTID,MAX(STUDENTSUBJECT )AS STUDENTSUBJECT ,MAX(YEAR1),MAX(MARKS1),MAX(RANK1),MAX(YAER2),MAX(MARKS2),MAX(RANK2),MAX(YEAR3),MAX(MARKS3),MAX(RANK3) FROM STUDENT GROUP BY STUDENTSUBJECTThis above query is working for SAME SUBJECT like (MATHS,MATHS, etc.) but it is not working for diff subjects like above table I mention.please help meexcepted o/p:o/p:STUDENTID STUDENTSUBJECT YEAR1 MARKS1 RANK1 YEAR2 MARKS21 MATHS 2013-06-07-2014-04-05 10 DGRADE 2012-06-11-2013-05-14 null1 BPC NULL NUll NULL null 6RANK2 YEAR3 MARKS3 RANK3null null null nullDGrade null null nuli have to get all in first row in a table like above one 解决方案 Use the ISNULL() built-in function.SELECTMAX(STUDENTID) AS STUDENTID,MAX(STUDENTSUBJECT) AS STUDENTSUBJECT,ISNULL(MAX(YEAR1),"") As Year1,ISNULL(MAX(MARKS1),"") As Marks1,ISNULL(MAX(RANK1),"") As Rank1,ISNULL(MAX(YEAR2),"") As Year2,ISNULL(MAX(MARKS2),"") As Marks2,ISNULL(MAX(RANK2),"") As Rank2,ISNULL(MAX(YEAR3),"") As Year3,ISNULL(MAX(MARKS3),"") As Marks3,ISNULL(MAX(RANK3),"") As Rank3FROM STUDENTGROUP BY STUDENTSUBJECTI do not understand why you use MAX(StudentID) and MAX(StudentSubject). Maybe you should use "Improve Question" and explain exactly what you are trying to get as a result (the requirements statement in simple terms).SELECTSTUDENTID,STUDENTSUBJECT,ISNULL(YEAR1,'')AS 'YEAR1',ISNULL(MARKS1,'')AS 'MARKS1',ISNULL(RANK1,'')AS 'RANK1',ISNULL((Select MAX(Year2) FROM Table26),'') AS 'YEAR2',ISNULL(MARKS2,'')AS 'MARKS2',ISNULL(RANK2,'')AS 'RANK2',ISNULL(YEAR3,'')AS 'YEAR3',ISNULL(MARKS3,'')AS 'MARKS3',ISNULL(RANK3,'')AS 'RANK3'INTO #TableNameFROM Table26UPDATE #TableNameSET YEAR2 = ''WHERE STUDENTSUBJECT = 'BPC' 这篇关于如何删除列中的Null?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!