sql复杂的查询

来源：百度知道 编辑：UC知道 时间：2024/05/28 00:47:34
有表message如下
ID----FromID----ToID----content
1------101-------104-----你好
2------101-------105-----啊啊啊
3------104-------107------很高兴
4------105-------101-----滚滚
怎么查询可在前台数据控件中显示成 (假如当前用户101)
ID----content-----type
1------你好-------发送
2------啊啊啊-----发送
4------滚滚-------接收

SELECT ID, content1, '发送' AS type
FROM message
WHERE (FromID = 101)
UNION
SELECT ID, content1, '接收' AS type
FROM message AS message_1
WHERE (ToID = 101)

或者

SELECT ID, content1, CASE WHEN FromID = 101 THEN '发送' ELSE '接收' END AS type
FROM message
WHERE (FromID = 101) OR (ToID = 101)

declare @ID/*参数 就是你传101*/
select id,content, Type=case @ID when @ID=FromID then '发送 ' else '接收' end from Message where fromid=@ID or toid=@ID

select id,content,
(case when fromid='101' then '发送' when toid='101' then '接收' end ) as 'type' from message
where fromid='101' or toid='101'

测试通过