提取加入表作为Postgres中的列的结果

发布于 2025-02-04 01:30:47 字数 1541 浏览 1 评论 0原文

我试图找出一种生成SQL查询的方法，即在视图中使用，以生成像亲子关系的Excel一样。

以下桌子的

ID

名称	电子邮件	接受	1
a	-	-2	b
名称	-	3	2
C--	访客	ID	1

人

我有	接受	ID_Person	（forefer_key--＆gt;
1	aguest1	---	person.ID
）	Bguest1	---	2
3	BGUEST2	---	2
4	CGUEST1	---	3
5	CGUEST2	---	3
6	CGUEST3	---	3

一个人可以有多个客人陪同他/她。

我想做的是生成一个给我以下结果的SQL：

person Name	Guest 1	Guest 2	Guest 3名称
A	aguest1	-b	查询
bguest1	bguest2	-c	cguest1
cguest1	cguest3	cguest2	我可以产生两个单独的查询，

这些查询将会产生两个独立的生成包含该信息的行列表，但我正在努力根据自己的信息来生成多列。

我已经研究了crosstab（）寻找Postgres，但是到目前为止，我没有运气来产生任何想要的东西。

朝着正确方向的任何帮助或指示都会有所帮助，谢谢！

原文

I trying to figure out a way to generate a SQL query, to be used in a view, to generate an Excel like format for parent-child relationship.

I have the following tables

Person

id	name	email	accepted
1	A	--	--
2	B	--	--
3	C	--	--

Guest

id	name	accepted	id_person (foreign_key -> person.id)
1	AGuest1	---	1
2	BGuest1	---	2
3	BGuest2	---	2
4	CGuest1	---	3
5	CGuest2	---	3
6	CGuest3	---	3

A person can have multiple guests accompanying him/her.

What I want to do is generate a SQL that gives me the following result:

Person Name	Guest 1 Name	Guest 2 Name	Guest 3 Name
A	AGuest1	--	--
B	BGuest1	BGuest2	--
C	CGuest1	CGuest2	CGuest3

I can generate two separate queries that will generate a list of rows with that information but I am struggling to generate multiple columns based on the information that I have.

I have looked into crosstab() for postgres, but so far I have no luck with generating anything that would like this.

Any help or pointers in the right direction would be helpful, thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

挽梦忆笙歌 2025-02-11 01:30:47

您可以使用crosstab：

SELECT *
FROM CROSSTAB (
    'SELECT p.name, g.name, g.name
    FROM person p JOIN guest g
    ON p.id = g.id_person
    ORDER BY 1,2')
    AS ("Person Name" varchar(100), "Guest 1 Name" varchar(100),
    "Guest 2 Name" varchar(100), "Guest 3 Name" varchar(100));

请注意：如您所述，这将适合1至3位来宾。如果每人有更多客人，您需要扩展此信息。

I created a working example here: db<>fiddle

You can use CROSSTAB:

SELECT *
FROM CROSSTAB (
    'SELECT p.name, g.name, g.name
    FROM person p JOIN guest g
    ON p.id = g.id_person
    ORDER BY 1,2')
    AS ("Person Name" varchar(100), "Guest 1 Name" varchar(100),
    "Guest 2 Name" varchar(100), "Guest 3 Name" varchar(100));

Please note: This will work for 1 to 3 guest like you described. In case more guests per person are possible, you need to extend this.

I created a working example here: db<>fiddle

回复收藏 0 原文

~没有更多了~