mysql - 如何使用 View 对 sql 中的 n_n 关系进行非规范化

Question

我有两张 table

account
id | desc

Prices
id | desc

table account 存储用户信息，而 table prices 根据服务类型存储多个价格。现在，我需要指定适用于每个帐户的价格。我想显示一个包含价格的结果和一个额外的列，该列告诉(以列表的形式)应用该服务的帐户...

我在想

CREATE TABLE `account` (
    `id_account`            smallint(2) unsigned PRIMARY KEY AUTO_INCREMENT,
    `user`              VARCHAR(55) ,
    `pass`                  VARCHAR(55) ,
    `descr`           VARCHAR(250)      
); 
INSERT INTO account VALUES 
  (1,'67395'    ,  'pass1','DrHeL'),
  (2,'12316'    ,  'pass2','DeHrL'),
  (3,'92316'    ,  'pass3','EfL');

 CREATE TABLE `prices`(
 `id_price` smallint(2) unsigned PRIMARY KEY AUTO_INCREMENT,
 `service`          VARCHAR(40), 
 `cost_1_1Kg`       double ,
 `cost_4_1Kg`       double ,
 `cost_8_1Kg`       double 
 );
INSERT INTO prices VALUES 
  (1,'laundry',  1.50, 2.00,5.00),
  (2,'walk.'  ,  2.50, 3.00,4.00);

CREATE TABLE `account_prices` (
  `id_account` smallint(2) unsigned NOT NULL,
  `id_price`  smallint(2) unsigned NOT NULL,
  `descr` VARCHAR(250)
) ;  

INSERT INTO account_prices VALUES 
(1,1,'apply SERVICE WITH ID 1 AND SERVICE WITH ID 2'),
(2,1,'apply SERVICE WITH ID 1 AND SERVICE WITH ID 2'),
(3,1,'apply SERVICE WITH ID 1 AND SERVICE WITH ID 2'),
(1,2,'apply SERVICE WITH ID 1 AND SERVICE WITH ID 2'),
(2,2,'apply SERVICE WITH ID 1 AND SERVICE WITH ID 2'),
(3,2,'apply SERVICE WITH ID 1 AND SERVICE WITH ID 2');

这给了我

ID_ACCOUNT  ID_PRICE    DESCR   USER    PASS    SERVICE COST_1_1KG  COST_4_1KG  COST_8_1KG
1           1           apply SERVICE WITH ID 1 AND SERVICE WITH ID 2   67395   pass1   laundry 1.5 2   5
2           1           apply SERVICE WITH ID 1 AND SERVICE WITH ID 2   12316   pass2   laundry 1.5 2   5
3           1           apply SERVICE WITH ID 1 AND SERVICE WITH ID 2   92316   pass3   laundry 1.5 2   5
1           2           apply SERVICE WITH ID 1 AND SERVICE WITH ID 2   67395   pass1   walk.   2.5 3   4
2           2           apply SERVICE WITH ID 1 AND SERVICE WITH ID 2   12316   pass2   walk.   2.5 3   4
3           2           apply SERVICE WITH ID 1 AND SERVICE WITH ID 2   92316   pass3   walk.   2.5 3   4

不过我想这样想:

ID_PRICE    SERVICE     COST_1_1KG  COST_4_1KG  COST_8_1KG descr
1           laundry     1.5         2           5          acount 1, account 2, account 3
2           walk.       2.5         3           4          acount 1, account 2, account 3

怎么做？

请看对应的fiddle:

http://sqlfiddle.com/#!2/16f05/3

Answer 1

您可以使用此查询获得所需的输出:

select ap.id_price
     , p.service
     , p.cost_1_1Kg
     , p.cost_4_1Kg
     , cost_8_1Kg
     , group_concat(
         concat('account ', ap.id_account)
         order by ap.id_account
         separator ', '
     ) as descr
from account_prices ap
     inner join prices p using (id_price)
group by ap.id_price

结果:

| ID_PRICE | SERVICE | COST_1_1KG | COST_4_1KG | COST_8_1KG |                           DESCR |
|----------|---------|------------|------------|------------|---------------------------------|
|        1 | laundry |        1.5 |          2 |          5 | account 1, account 2, account 3 |
|        2 |   walk. |        2.5 |          3 |          4 | account 1, account 2, account 3 |

Check updated SQL fiddle

---

这是如何工作的:

1. 您只需加入account_prices与 prices获取您需要的所有数据。
2. 获取account x东西，连接 "account "值为 id_account使用 concat()功能
3. 最后，要获得一组串联的值，请使用 group_concat()功能。它像任何其他聚合函数一样工作，但它不是执行操作(如 sum() 或 count() )，而是连接列(或表达式)的值。您可以定义您想要的输出顺序和自定义分隔符(默认分隔符是 , 。

希望对你有帮助。