dongxing4643
dongxing4643
2013-10-09 15:09

在MySQL中,如何从一个表中获取2列,从一行中的其他表中获取2行作为列?

已采纳

I know this is quite complicated, but I sincerely hope someone will check this out. I made short version (to better understand the problem) and full version (with original SQL)

Short version:

[TABLE A] [TABLE B]
|1|a|b|   |1|x
|2|c|d|   |1|y
|3| | |   |2|z
|5| | |   |2|v
          |4|w

How can I make MySQL query to get rows like that:

1|a|b|x|y
2|c|d|z|v

2 columns from A and 2 rows from B as columns, only with keys 1 and 2, no empty results

Subquery?

Full version:

I tried to get from Prestashop db in one row:

  • product id
  • ean13 code
  • upc code
  • feature with id 24
  • feature with id 25

It's easy to get id_product, ean13 and upc, as it's one row in ps_product table. To get features I used subqueries (JOIN didn't work out).

So, I selected id_product, ean13, upc, (subquery1) as code1, (subquery2) as code2. Then I needed to throw out empty rows. But couldn't just put code1 or code2 in WHERE. To make it work I had to put everything in subquery.

This code WORKS, but it is terribly ugly and I bet this should be done differently.

How can I make it BETTER?

SELECT * FROM(
    SELECT 
        p.id_product as idp, p.ean13 as ean13, p.upc as upc, (
            SELECT
                fvl.value
            FROM
                `ps_feature_product` fp
            LEFT JOIN
                `ps_feature_value_lang` fvl ON (fp.id_feature_value = fvl.id_feature_value)
            WHERE fp.id_feature = 24 AND fp.id_product = idp
        ) AS code1, (
            SELECT
                fvl.value
            FROM
                `ps_feature_product` fp
            LEFT JOIN
                `ps_feature_value_lang` fvl ON (fp.id_feature_value = fvl.id_feature_value)
            WHERE fp.id_feature = 25 AND fp.id_product = idp
        ) AS code2,
        m.name
    FROM 
        `ps_product` p 
    LEFT JOIN 
        `ps_manufacturer` m ON (p.id_manufacturer = m.id_manufacturer)
) mainq
WHERE 
    ean13 != '' OR upc != '' OR code1 IS NOT NULL OR code2 IS NOT NULL
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • doujieluo5875 doujieluo5875 8年前
    create table tablea 
    ( id int,
      col1 varchar(1),
      col2 varchar(1));
    
    create table tableb 
    ( id int,
      feature int,
      cola varchar(1));
    
    insert into tablea (id, col1, col2)
    select 1,'a','b'  union
    select 2,'c','d'  union
    select 3,null,null  union
    select 5,null,null;
    
    
    insert into tableb (id, feature, cola)
    select 1,24,'x'  union
    select 1,25,'y' union
    select 2,24,'z' union
    select 2,25,'v' union
    select 4,24,'w';
    
    select a.id, a.col1, a.col2, b1.cola b1a, b2.cola b2a
    from tablea a
    inner join tableb b1 on (b1.id = a.id and b1.feature = 24)
    inner join tableb b2 on (b2.id = a.id and b2.feature = 25);
    

    SQLFiddle here.

    点赞 1 评论 复制链接分享
  • douna3367 douna3367 8年前

    Big thanks to everyone for the answers. Mark's answer was first, simplest and works perfectly in my case. The query runs several times faster than mine, with subqueries. Thanks, Mark!

    Just a few words why I needed that:

    It's a part of integration component for Prestashop and wholesale exchange platform. There are 4 product code systems that wholesalers use on the platform (ean13, upc and 2 other systems). Those 2 other product codes are added as product feature in Prestashop. There are thousands of products on the shop and hundreds of thousands of products on the platform. Which is why speed is crucial.

    Here is the code for full version of my question. Maybe someone will find this helpful.

    Query to get Prestashop product codes and certain features in one row:

    SELECT 
        p.id_product, p.ean13, p.upc, fvl1.value as code1, fvl2.value as code2
    FROM `ps_product` p 
    LEFT JOIN 
        `ps_feature_product` fp1 ON (p.id_product = fp1.id_product and fp1.id_feature = 24)
    LEFT JOIN 
        `ps_feature_value_lang` fvl1 ON (fvl1.id_feature_value = fp1.id_feature_value)
    LEFT JOIN 
        `ps_feature_product` fp2 ON (p.id_product = fp2.id_product and fp2.id_feature = 25)
    LEFT JOIN 
        `ps_feature_value_lang` fvl2 ON (fvl2.id_feature_value = fp2.id_feature_value)
    WHERE 
        ean13 != '' OR upc != '' OR fvl1.value IS NOT NULL OR fvl2.value IS NOT NULL;
    
    点赞 评论 复制链接分享
  • doufeng5059 doufeng5059 8年前

    This is not a simple question because it's not a standard query, by the way if you can make use of views you can do the following procedure. Assuming you're starting from this tables:

    CREATE TABLE `A` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `firstA` char(1) NOT NULL DEFAULT '',
      `secondA` char(1) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    );
    
    CREATE TABLE `B` (
      `id` int(11) unsigned NOT NULL,
      `firstB` char(1) NOT NULL DEFAULT ''
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `A` (`id`, `firstA`, `secondA`)
    VALUES (1, 'a', 'b'), (2, 'c', 'd');
    
    INSERT INTO `B` (`id`, `firstB`)
    VALUES (1, 'x'), (1, 'y'), (2, 'z'), (2, 'v'), (4, 'w');
    

    First create a view that joins the two tables:

    create or replace view C_join as
    select A.firstA, A.secondA, B.firstB
    from A
    join B on B.id=A.id;
    

    Create the view that groups the rows in table B:

    create or replace view d_group_concat as
    select firstA, secondA, group_concat(firstB) groupconcat
    from c_join
    group by firstA, secondA
    

    Create the view that does what you need:

    create or replace view e_result as
    select firstA, secondA, SUBSTRING_INDEX(groupconcat,',',1) firstB, SUBSTRING_INDEX(SUBSTRING_INDEX(groupconcat,',',2),',',-1) secondB
    from d_group_concat
    

    And that's all. Hope this helps you.

    If you can't create views, this could be the query:

    select firstA, secondA, SUBSTRING_INDEX(groupconcat,',',1) firstB, SUBSTRING_INDEX(SUBSTRING_INDEX(groupconcat,',',2),',',-1) secondB
    from (
            select firstA, secondA, group_concat(firstB) groupconcat
            from (
                    select A.firstA, A.secondA, B.firstB
                    from A
                    join B on B.id=A.id
            ) c_join
            group by firstA, secondA
    ) d_group_concat
    
    点赞 评论 复制链接分享
  • dprh34164 dprh34164 8年前

    What you want to do is called a Pivot Query. MySQL has no native support for pivot queries, though other RDBMSen do.

    You can simulate a pivot query with derived columns, but you must specify each derived column. That is, it is impossible in MySQL itself to have the number of columns match rows of another table. This has to be known ahead of time.

    It would be much easier to query the results as rows and then use PHP to do the aggregation into columns. For example:

    while ($row = $result->fetch()) {
        if (!isset($table[$row->id])) {
            $table[$row->id] = array();
        }
        $table[$row->id][] = $row->feature;
    
    点赞 评论 复制链接分享

为你推荐