求赐教一sql语句

[FocusNT]

有一表聊天信息表table
字段id,sender,receiver,content,isread
要显示如下内容(与我有过聊天的人，包括我发给的或者我接受的)，对象这列为与我有过聊天的人
对象 未读数
aaaa 5
bbbb 6

不知道有没有描述清楚

[發糞塗牆]

表数据贴一点来看看，如果有什么特殊规则也说清楚，大概写法： select id,sum(case whe isread=0 then sender else null end )+sum(case whe isread=0 then receiver else null end)未读数 from table group by id 我假设isread=0的为未读

[KeepSayingNo]

select [对象], sum([未读数]) from
(
select receiver as [对象],1 as [未读数] from [聊天信息表] where sender='我的名称' and isread=0
union all 
select sender as [对象],1 as [未读数] from [聊天信息表] where receiver='我的名称' and  isread=0
)  t group by [对象]

[--小F--]

最好把表结构和所需要的结果都列出来。

[FocusNT]

表结构如下

[FocusNT]

f_isread=1为未读，f_isread=2为已读

[發糞塗牆]

select content,sum(case when f_isread=1 then sender else null end )+sum(case when f_isread=1 then receiver else null end)未读数 from table group by content

[FocusNT]

想要数据界面如下的

[FocusNT]

DBA_Huangzj 应该不对，应该是针对sender和receiver进行group才对的。

[發糞塗牆]

那你用2楼的试试吧

[FocusNT]

group by id肯定是不对的

[發糞塗牆]

2楼没用group by id...1楼才用了id，你连每个列都没说清楚，我当然用id