22,209
社区成员
发帖
与我相关
我的任务
分享
select message,b.username,c.username from message a left join [user] b on a.useridfrom=b.userid
left join [user] c on a.useridto=c.userid
Select
a.[message] As [消息内容],
b.username As [发消息人姓名],
c.username As [接收消息人姓名]
From [message] As a
Inner Join [user] As b On b.userid=b.UserIdFrom
Inner Join [user] As c On c.userid=b.UserIdTo
set nocount on
if object_id('[user]')is not null drop table [user] --生成测试数据
go
create table [user](userid int,username varchar(10))
insert [user] select 1, '张三'
insert [user] select 2, '李四'
insert [user] select 3, '王五'
if object_id('message')is not null drop table message
go
create table message ( message varchar(100),UserIdFrom int ,UserIdTo int) --生成测试数据
insert message select '你好',1,3
insert message select '好的',3,1
insert message select '下午打球',2,1
insert message select '好的,球场见',1,2
if object_id('P_message') is not null drop proc P_message --生成存储过程
go
select b.message,a.username,c.username
from [message] b
left join [user] a on b.UserIdFrom = a.userid
left join [user] c on b.UserIdTo = c.userid
/*
message username username
------------------------------ ---------- ----------
你好 张三 王五
好的 王五 张三
下午打球 李四 张三
好的,球场见 张三 李四
*/
set nocount on
if object_id('[user]')is not null drop table [user] --生成测试数据
go
create table [user](userid int,username varchar(10))
insert [user] select 1, '张三'
insert [user] select 2, '李四'
insert [user] select 3, '王五'
if object_id('message')is not null drop table message
go
create table message ( message varchar(100),UserIdFrom int ,UserIdTo int) --生成测试数据
insert message select '你好',1,3
insert message select '好的',3,1
insert message select '下午打球',2,1
insert message select '好的,球场见',1,2
if object_id('P_message') is not null drop proc P_message --生成存储过程
go
create proc P_message
as
select t.message,t.username,u.username from
(select m.message,u.username,m.UserIdTo from [user] u inner join message m on u.userid=m.useridfrom) t
inner join [user] u
on t.useridto=u.userid
go
exec P_message --执行存储过程
/*--结果
message username username
---------------------------------------------------------------------------------------------------- ---------- ----------
你好 张三 王五
好的 王五 张三
下午打球 李四 张三
好的,球场见 张三 李四
*/
SELECT A.message , A.UserIdFrom, B.username AS UserIdTo
FROM (SELECT C.message , D.username AS UserIdFrom, D.userid FROM message C JOIN user D ON C.userid = D.UserIdFrom )
AS TMPtable A
JOIN user B
ON A.userid = B.UserIdTo
SELECT A.message , A.username AS UserIdFrom, B.username AS UserIdTo
FROM (SELECT C.message , D.username AS UserIdFrom FROM message C JOIN user D ON C.userid = D.UserIdFrom )
AS TMPtable A
JOIN user B
ON A.userid = B.UserIdFrom