复杂的 SQL 查询 :: 显示来自多个表的内联数据

Question

您好，我在寻找解决方案时遇到问题。

我有 3 张桌子：

1. 顾客
   • customer_id
   • 客户名称
2. 关系
   • 订阅 ID
   • customer_id
3. 订阅

一个客户可以与多个订阅有关系，我想像这样显示数据：

customer_id, customer_name, subscription_first, subscription_second, subscription_n 

...全部在一行中。

任何人都可以帮助我吗：？

好的，首先，感谢您的帮助:)

我做了类似的事情及其工作:)

SELECT `main_table`.*, `customer_lastname_table`.`value` AS `customer_lastname`, `customer_firstname_table`.`value` AS `customer_firstname`, IF(main_table.customer_id = 0, 1, 2) AS `type`, `store`.`group_id`, `store`.`website_id`, `subscription_table_one`.`subscription_code`, `subscription_table_two`.`subscription_code` FROM `newsletter_subscriber` AS `main_table`

 LEFT JOIN `customer_entity_varchar` AS `customer_lastname_table` ON customer_lastname_table.entity_id=main_table.customer_id
 AND customer_lastname_table.attribute_id = 7

 LEFT JOIN `customer_entity_varchar` AS `customer_firstname_table` ON customer_firstname_table.entity_id=main_table.customer_id
 AND customer_firstname_table.attribute_id = 5

 INNER JOIN `core_store` AS `store` ON store.store_id = main_table.store_id
 LEFT JOIN `b_newsletter_relations` AS `relation_table` ON relation_table.customer_id=main_table.customer_id
 LEFT JOIN `b_newsletter_subscriptions` AS `subscription_table_one` ON subscription_table_one.subscription_id=relation_table.subscription_id
 LEFT JOIN `b_newsletter_subscriptions` AS `subscription_table_two` ON subscription_table_one.subscription_id=relation_table.subscription_id
 GROUP BY `customer_id`

啊啊，还有一件事，它是通过 PHP 动态实现的。

Answer 1

您可以使用 MySQL 提供的方便的 GROUP_CONCAT 函数。下面是解决方案。

create table customer (customer_id int, customer_name varchar(100));

create table subscriptions (subscription_id int, subscription_name varchar(100));

create table customer_relation (subscription_id int, customer_id int);

insert into customer values (1,'cust1');
insert into customer values (2,'cust2');

insert into subscriptions values (1,'sub1');
insert into subscriptions values (2,'sub2');
insert into subscriptions values (3,'sub3');

insert into customer_relation values (1,1);
insert into customer_relation values (2,1);
insert into customer_relation values (3,1);

insert into customer_relation values (1,2);
insert into customer_relation values (3,2);


SELECT c.customer_id
     , c.customer_name
     , GROUP_CONCAT(s.subscription_name ORDER BY s.subscription_name) subs
  FROM customer c
     , subscriptions s
     , customer_relation x
 WHERE x.subscription_id = s.subscription_id
   AND x.customer_id = c.customer_id
GROUP BY
       c.customer_id
     , c.customer_name;

结果：

customer_id    customer_name    subs
1              cust1            sub1,sub2,sub3
2              cust2            sub1,sub3

Answer 2

如果你能运用原则，这种情况就可以很容易地处理。然而，在这种情况下，学说本身会返回对象，如下所示，

     Array
(
    [0] => Array
        (
            [name] => customer_name
            [id] => customer_id
            [subscription] => Array
                (
                    [0] => subscription_first
                    [1] => subscription_second
                    [2] => subscription_third
                )

        )

    [1] => Array

    (
        [name] => customer_name
        [id] => customer_id
        [subscription] => Array
            (
                [0] => subscription_first
                [1] => subscription_second
                [2] => subscription_third
            )

    )

[2] => Array
    (
        [name] => customer_name
        [id] => customer_id
        [subscription] => Array
            (
                [0] => subscription_first
                [1] => subscription_second
                [2] => subscription_third
            )



    )

)

如果您可以制作这样的数组，那么您进行这样的演示将非常简单。有人可以编写具有多级连接的复杂查询，这会导致更大的瓶颈情况。但我建议使用单个连接检索所有数据，并使用 php 引擎进行解析以获取此类数组并呈现数据。

谢谢

Answer 3

你可以使用联盟。
比如

SELECT * FROM CUSTOMER WHERE customer_id = ... UNION SELECT * FROM Subscriptions WHERE subscription_id IN (SELECT subscription_id WHERE customer_id = ...)

我根本不懂SQL，所以可能会有很多错误，但我认为它可以完成工作。