如何在 SQL 查询中执行此操作？

发布于 2024-11-30 13:06:01 字数 723 浏览 2 评论 0原文

Table: A                  Table: B                 Table: C
------------            ----------------          -------------
P_id | G_id              P_id  |  Name            G_id | Title
------------            ----------------          -------------
 1   |  1                 1    | john              1   | php
 2   |  1                 2    | jack              2   | sql
 3   |  2                 3    | sam

现在我查询如下：

Select B.name, C.title
from B inner join A on...
inner join c on...

如果我们在这里输入 john 那么它将显示如下：

john php.

但我想显示如下：

john jack  php.

因为 john 和 jack 的 G_id 是相同的。
我该怎么做？

原文

Table: A                  Table: B                 Table: C
------------            ----------------          -------------
P_id | G_id              P_id  |  Name            G_id | Title
------------            ----------------          -------------
 1   |  1                 1    | john              1   | php
 2   |  1                 2    | jack              2   | sql
 3   |  2                 3    | sam

Now I am quering like:

Select B.name, C.title
from B inner join A on...
inner join c on...

If we input john here then it will display like this:

john php.

But I want to display it like:

john jack  php.

Because G_id of john and jack is same.
How can i do this?

分享到QQ

分享到微博

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

发布评论

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

何时共饮酒 2024-12-07 13:06:01

伪代码（类似于mysql）：

SELECT B.name, C.title 
FROM B
INNER JOIN A ON A.P_id = B.P_id
INNER JOIN C ON A.G_id = C.G_id
WHERE A.G_id = (
                 SELECT A.G_id 
                 FROM B
                 INNER JOIN A ON A.P_id = B.P_id
                 WHERE B.Name LIKE '%John%' LIMIT 1
               );

编辑：

这将使您的结果可以按名称搜索，使用GROUP_CONCAT 和 GROUP BY 按照 Everton Agner 的建议正确格式化结果。

Pseudo code (similar to mysql):

SELECT B.name, C.title 
FROM B
INNER JOIN A ON A.P_id = B.P_id
INNER JOIN C ON A.G_id = C.G_id
WHERE A.G_id = (
                 SELECT A.G_id 
                 FROM B
                 INNER JOIN A ON A.P_id = B.P_id
                 WHERE B.Name LIKE '%John%' LIMIT 1
               );

EDIT:

This will make your results searchable by name, use GROUP_CONCAT and GROUP BY as suggested by Everton Agner to correctly format the results.

回复收藏 0 原文

江心雾 2024-12-07 13:06:01

您需要一个聚合函数来处理这种分组。我对 MySQL 不太熟悉，但是使用 group_concat() 函数尝试类似这样的操作：

select
    group_concat(b.Name),
    c.Title
from
    A a
    join B b on b.P_id = a.P_id
    join C c on c.G_id = a.G_id
group by
    c.Title

希望它会向您显示“john，jack”

在此处查看有关聚合函数的文档：http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

-- 编辑

刚刚测试它，它给了我以下输出：

+----------------------+-------+
| group_concat(b.Name) | Title |
+----------------------+-------+
| john,jack            | php   |
| sam                  | sql   |
+----------------------+-------+

I希望这就是你想要的:)

- 编辑（最后一个）

现在我想我明白你想要什么，只需添加 having group_concat(b.Name) like '%john%' 和它'只会给你那些组包含john...更好的选择是array contains函数，但我还没有找到它。

+----------------------+-------+
| group_concat(b.Name) | Title |
+----------------------+-------+
| john,jack            | php   |
+----------------------+-------+

You need an Aggregation Funcion to work with this kind of grouping. I'm not fluent at MySQL, but try something pretty much like this, using the group_concat() function:

select
    group_concat(b.Name),
    c.Title
from
    A a
    join B b on b.P_id = a.P_id
    join C c on c.G_id = a.G_id
group by
    c.Title

Hopefully it'll show you "john,jack"

Check the docs about Aggregation functions here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

-- EDIT

Just tested it, it gave me the following output:

+----------------------+-------+
| group_concat(b.Name) | Title |
+----------------------+-------+
| john,jack            | php   |
| sam                  | sql   |
+----------------------+-------+

I hope is that what you want :)

-- EDIT (the last one)

Now I think I understood what you want, just add having group_concat(b.Name) like '%john%' and it'll give you only the groups that john is included... The better choice would be an array contains function, but I haven't found it.

+----------------------+-------+
| group_concat(b.Name) | Title |
+----------------------+-------+
| john,jack            | php   |
+----------------------+-------+

回复收藏 0 原文

~没有更多了~