我怎么写以下存储过程？

I have four tables in database.

我在数据库中有四个表。

person(Perid ,firstname , lastname , gender)

人(Perid,名字,姓氏,性别)

Research (Resid , Resname ,stareddate)

研究(Resid,Resname,stareddate)

person_research(Resid , Perid , others)

person_research(Resid,Perid,其他)

Supervisors (Resid , Supervisorid , others)

主管(Resid,Supervisorid,其他)

I want to write stored procedure that returns each Research and Researcher Name and Supervisor Names for put the results in datagridview.

我想编写存储过程,返回每个Research and Researcher Name和Supervisor Names,将结果放入datagridview。

2 个解决方案

#1

for SQL server:

对于SQL服务器:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.test 
AS
BEGIN

    SET NOCOUNT ON;

    SELECT R.resname as researchname
        , (p1.firstname + p1.lastname) as researcher
        , (p2.firstname + p2.lastname) as supervisor
    FROM Research R
    JOIN person_research PR
        ON R.Resid = PR.Resid
    JOIN person p1
        on PR.Perid = p1.Perid 
    JOIN supervisors s
        on R.Resid = s.Resid
    JOIN person p2
        on PR.Perid = p2.Perid 
END
GO

#2

You did not specify a DBMS so I'm assuming PostgreSQL:

你没有指定DBMS所以我假设PostgreSQL:

create or replace function get_researchers()
  returns table(research_name, researchers, supervisors)
  language sql
AS
$body$
  select r.resname as research_name,
         string_agg(p.firstname||' '||p.lastname, ',') as researchers,
         string_agg(sp.firstname||' '||sp.lastname, ',') as supervisors
  from research r
    join person_research pr on pr.resid = r.resid
    join person p on pr.perid = p.perid,
    join supervisors s on s.resid = r.resid
    join person sp on sp.perid = s.perid
  group by r.resname
  order by r.resname;  
$body$

#1