主页 / dba / 问题 / 2772
Accepted
kacalapy
Asked: 2011-05-18 10:58:50 +0800 CST

如何将布尔值获取到基于另一个表的 SELECT 操作中？

  • 772

我有两个表：Message和Attachments（表示具有一对多关系的实体类型）如下：

CREATE TABLE message (
    messageId ..., 
    [from]..., 
    [to]..., 
    subject..., 
    body...
);

CREATE TABLE attachments (
    messageId..., 
    filename...
);

我想选择基本消息数据并包含一个位列来表示消息是否有附件。我正在尝试在如下所示的操作中使用 EXISTS：

SELECT  
M.*,
hasAttachments = EXISTS(SELECT AttachmentId 
                          FROM Attachment 
                         WHERE messageId = M.messageId),
FROM Message M

但这不起作用 - 做我正在寻找的方法是什么？

sql-server sql-server-2005

2 个回答

  1. Best Answer

    我会用 CASE 语句来做：

    select
    m.*,
    hasAttachments = CASE WHEN EXISTS(select * from Attachment where messageId = M.messageId) then 1 else 0 end
from Message M

    或者

    select distinct 
    m.*,
    hasAttachments = CASE WHEN a.AttachmentId is not null then 1 else 0 end
from Message m
left join Attachment a on a.MessageId = m.MessageId
    • 7 
  2. select  
    M.*,
   CAST(select COUNT(*) from Attachment A where A.messageId = M.messageId) AS bit) AS hasAttachments,
FROM Message M

    或者

    select  
    M.*,
   CAST(hasAttachments AS bit) AS hasAttachments,
FROM
  Message M
  CROSS APPLY
  (select COUNT(*) AS hasAttachments
   from Attachment A
    where A.messageId = M.messageId) A2
    • 3

相关问题