22,209
社区成员
发帖
与我相关
我的任务
分享
Select
sender.Name sender,
checker.name checker,
Files.Content
FROM Files
left Join Worker sender on sender.ID=Files.SenderID
left Join Worker checker on checker.ID=Files.CheckerID
WHERE sender.Type=@Type
And Files.Time>=@TimeFrom AND Files.Time<@TimeTo
And checker.name=@name
select a.senderid,b.name as sender,a.content,c.name as checkerr,d.name as receiver
from Files a
join Worker b on a.senderid=b.id
join Worker c on a.checkerid=c.id
join Worker d on a.ReceiveID=d.id
/*
senderid sender content checkerr receiver
----------- ------ -------- -------- --------
1 张三 你好 王五 李四
1 张三 你在家吗 王五 刘六
*/
--> 测试数据: @Worker
CREATE table Worker(ID int,Name varchar(4))
insert into Worker
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'刘六'
GO
select * from Worker
--> 测试数据: @Files
CREATE table Files(SenderID int,ReceiveID int,CheckerID int,Content varchar(8))
insert into Files
select 1,2,3,'你好' union all
select 1,4,3,'你在家吗'
select * from Files
GO
create function GetNameById(@ID int)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @NAME VARCHAR(20)
SELECT @NAME =NAME FROM Worker WHERE ID=@ID
return @NAME
END
GO
select dbo.GetNameById(SenderID) AS Sender,dbo.GetNameById(ReceiveID) AS Receive,
dbo.GetNameById(CheckerID) AS Checker,[Content] from Files
/*
Sender Receive Checker Content
---------- ---------- ---------- --------
张三 李四 王五 你好
张三 刘六 王五 你在家吗
*/
select b.senderid,a.name,b.content,c.name
from files b
join worker a on b.senderid=a.id
join worker c on b.checkerid=c.id