如何删除列中的Null? [英] How to remove Nulls in a column?
问题描述
嗨
我是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 < span class =code-keyword> null null
DGrade null < span class =code-keyword> 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'
Hi
I am new to SQL Server 2008 r2
Actually, I have a table like this
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 query like this
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
This 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 me
excepted o/p:
o/p:
STUDENTID STUDENTSUBJECT YEAR1 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 have to get all in first row in a table like above one
Use the ISNULL() built-in function.
SELECT MAX(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 Rank3 FROM STUDENT GROUP BY STUDENTSUBJECT
I 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).
SELECT STUDENTID , 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 #TableName FROM Table26 UPDATE #TableName SET YEAR2 = '' WHERE STUDENTSUBJECT = 'BPC'
这篇关于如何删除列中的Null?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!