如果条件匹配,SQL返回true

DECLARE @Assignments TABLE
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(30) NOT NULL
)

DECLARE @AssignmentNotes TABLE
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    AssignmentId INT NOT NULL,
    Note VARCHAR(MAX)
)

INSERT INTO @Assignments(Name) VALUES('Biology')
INSERT INTO @Assignments(Name) VALUES('Chemistry')

INSERT INTO @AssignmentNotes (AssignmentId, Note) VALUES(1, 'Studies on DNA')
INSERT INTO @AssignmentNotes (AssignmentId, Note) VALUES(1, 'Evolution notes from Darwin')

SELECT
    A.*,
    CASE WHEN COUNT(AN.Id) > 0 THEN 1 ELSE 0 END AS HasNotes    
FROM    
    @Assignments AS A
    LEFT JOIN
    @AssignmentNotes AS AN
    ON A.Id = AN.AssignmentId
GROUP BY
    A.Id,
    A.Name

没有准备好测试SQL Server,但这样的查询应该可以工作:

SELECT A.*, 
  CAST(
    CASE (SELECT TOP 1 AssignmentNotes_ID 
          FROM AssignmentNotes AN 
          WHERE AN.AssignmentID = A.AssignmentID)
      WHEN NULL THEN 0 ELSE 1 END
    AS BIT) AS HasNotes
FROM Assignments A

SELECT a.*, 
    (SELECT COUNT(*) 
     FROM assignment_notes an 
     WHERE an.assignmentid = a.id) as NumNotes
FROM Assignment a

这会给你那作业的笔记数。

如果使用case语句,可以将伪代码转换为SQL。在MSDN上: http://msdn.microsoft.com/en-us/library/ms181765.aspx

还有一篇文章以防万一: http://www.devx.com/tips/Tip/15633

这种方法意味着您不需要由于返回大量赋值字段而使用庞大的groupby语句。

SELECT Assignment.*, 
CAST(CASE WHEN NotesQty>0 THEN 1 ELSE 0 END as bit) AS HasNotes
FROM Assignment
LEFT JOIN
(SELECT AssignmentId,COUNT(*) AS NotesQty 
 FROM assignment_notes
 GROUP BY AssignmentId) as Assignment_NotesQty
ON Assignment_NotesQty.AssignmentId=Assignment.AssignmentId