34,593
社区成员
发帖
与我相关
我的任务
分享
SELECT ID, name, MSG,
max(CASE username WHEN 'weiwei' THEN 1 ELSE 0 END) AS Flag
FROM tbUser GROUP BY ID;
select id,name,msg,max(count) flag from (
select *,1 count from tbUser where username='weiwei' group by id
union all
select *,0 count from tbUser
) t group by id;
select ID,name,msg,flag=case when tbuser_username='weiwei' then 1 else 0 end
from tbmessage left join tbfav
on tbmessage.id=tbfav.tbmessage_id and tbuser_username='weiwei'
SELECT a.ID, a.name, a.MSG, ISNULL(b.Flag, 0)
FROM tbMessage a LEFT JOIN
(SELECT ID, 1 AS Flag FROM tbFav
WHERE tbUser_Username='weiwei'
) b ON a.ID=b.ID
SELECT a.* ,
( CASE WHEN EXISTS ( SELECT 1
FROM @tbFav
WHERE tbMessage_ID = a.Id
AND tbUser_Username = @UserName ) THEN 1
ELSE 0
END ) Flag
FROM @tbMessage a
SELECT a.ID, a.name, a.MSG,
(CASE WHEN EXISTS(SELECT 1 FROM #tbFav WHERE ID=a.ID AND tbUser_Username='weiwei') THEN 1 ELSE 0 END) Flag
FROM #tbMessage a
DECLARE @tbMessage TABLE
(
Id CHAR(11) ,
Name VARCHAR(50) ,
Msg VARCHAR(50)
);
INSERT INTO @tbMessage
( Id, Name, Msg )
VALUES ( '20130405bAA', '李', '欣赏ffff' ),
( '20130405bAB', '李', '你好' ),
( '20130405bAC', '李', '收到了' ),
( '20130405bAD', '李', '可以关闭了' );
DECLARE @tbFav TABLE
(
tbUser_Username VARCHAR(50) ,
tbMessage_ID CHAR(11)
);
INSERT INTO @tbFav
( tbUser_Username, tbMessage_ID )
VALUES ( 'weiwei', '20130405bAA' ),
( 'latlong', '20130405bAA' ),
( 'latlong', '20130405bAC' );
DECLARE @UserName VARCHAR(50);
SET @UserName = 'weiwei';
SELECT m.* ,
CASE WHEN t.tbMessage_ID IS NULL THEN 0
ELSE 1
END
FROM @tbMessage m
OUTER APPLY ( SELECT TOP 1
*
FROM @tbFav f
WHERE m.id = f.tbMessage_ID
AND tbUser_Username = @UserName
) AS t;
SELECT ID, name, MSG,
CASE tbUser_Username WHEN 'weiwei' THEN 1 ELSE 0 END AS Flag
FROM 视图
这样写就会出现重复记录。
20130405bAA 李 欣赏ffff flag=1
20130405bAA 李 欣赏ffff flag=0
这两条记录我只要flag=1 的。SELECT ID, name, MSG,
CASE tbUser_Username WHEN 'weiwei' THEN 1 ELSE 0 END AS Flag
FROM 视图