如何使用SQL查询省略空值

I am trying to only display the rows in which there is date for Researchers. I cannot manage to omit the rows with Null Values. I even tried this solution How to remove null rows from sql query result?..

我试图只显示研究人员有日期的行。我无法使用Null值省略行。我甚至试过这个解决方案如何从sql查询结果中删除空行？

This is my Query:

这是我的查询：

SELECT Submission.Title AS [Submission_Title], CA.Surname AS [Researchers], Submission.Status AS [Status] 
FROM Submission 
CROSS APPLY (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
FROM ResearcherSubmission rs INNER JOIN Researcher r 
ON r.ResearcherID = rs.ResearcherID 
WHERE CONCAT (DATENAME(MONTH,[Submission].[SubmissionDate]), ' ',DATEPART (YEAR,[Submission].[SubmissionDate])) = 'October 2015'  
AND Submission.SubmissionID = rs.SubmissionID 
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')) AS CA (Surname) 
GROUP BY convert(varchar(10),datename(month,Submission.SubmissionDate)), Submission.Title, CA.Surname, Submission.Status;

This is my Current output:

这是我的当前输出：

any suggestion. Thank you

任何建议。谢谢

2 个解决方案

#1

Quickfix, without reading query:

Quickfix，无需读取查询：

WITH cte AS
(
SELECT Submission.Title AS [Submission_Title], CA.Surname AS [Researchers], Submission.Status AS [Status] 
FROM Submission 
CROSS APPLY (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
FROM ResearcherSubmission rs INNER JOIN Researcher r 
ON r.ResearcherID = rs.ResearcherID 
WHERE CONCAT (DATENAME(MONTH,[Submission].[SubmissionDate]), ' ',DATEPART (YEAR,[Submission].[SubmissionDate])) = 'October 2015'  
AND Submission.SubmissionID = rs.SubmissionID 
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')) AS CA (Surname) 
GROUP BY convert(varchar(10),datename(month,Submission.SubmissionDate)), Submission.Title, CA.Surname, Submission.Status
)
SELECT *
FROM cte
WHERE Researchers IS NOT NULL;

There is probably more elegant solution, but you need to share sample data and structures.

可能有更优雅的解决方案，但您需要共享示例数据和结构。

This part may cause problems:

这部分可能会导致问题：

SELECT DISTINCT ', ' + r.Surname

try with CONCAT instead or :

尝试使用CONCAT或：

SELECT DISTINCT ', ' + ISNULL(r.Surname, '')

#2

You should filter out the researchers before the group by rather than afterwards. When possible, it is better (performance-wise) to put conditions before aggregation.

您应该在小组之前而不是之后过滤掉研究人员。如果可能，在聚合之前放置条件会更好（性能方面）。

SELECT s.Title AS Submission_Title, CA.Surname AS Researchers, s.Status 
FROM Submission s CROSS APPLY
     (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
                    FROM ResearcherSubmission rs INNER JOIN
                         Researcher r 
                         ON r.ResearcherID = rs.ResearcherID 
                    WHERE s.SubmissionID = rs.SubmissionID 
                    FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'
                   ), 1, 2, ' '))
    ) AS CA(Surname) 
WHERE s.SubmissionDate >= '2015-10-01' AND s.SubmissionDate < '2015-11-01' AND
      ca.Surname IS NULL
GROUP BY YEAR(s.SubmissionDate), MONTH(s.SubmissionDate), s.Title, CA.Surname, s.Status;

Note the changes made:

请注意所做的更改：

Table aliases make the query easier to write and to read.
表别名使查询更容易编写和读取。
I changed the date comparison to have no functions on the date itself. This would allow SQL Server to use an index, if appropriate.
我更改了日期比较，在日期本身没有任何功能。如果合适，这将允许SQL Server使用索引。
I also moved the date comparison from the CROSS APPLY subquery to the outer query. This could be a big gain in efficiency. Why do the extra work for rows that will be filtered out anyway?
我还将日期比较从CROSS APPLY子查询移动到外部查询。这可能是效率的重大提升。为什么要对过滤掉的行进行额外的工作呢？
I added the NOT NULL condition to the WHERE clause.
我在WHERE子句中添加了NOT NULL条件。
The date key in the outer GROUP BY is redundant because the query is only using one month of data. I simplified the logic but left it.
外部GROUP BY中的日期键是多余的，因为查询仅使用一个月的数据。我简化了逻辑，但离开了它。

#1