SQL Groupby与新列创建

Question

我需要帮助，只是卡在这里。

我有匹配表。

id product_id property_id
1   1          1
2   1          2
3   1          3
4   1          4
5   2          1
6   2          2
7   2          3
8   3          1
9   3          2
10  3          3
11  3          4
12  3          5

由于SQL查询，我需要拥有的是：

product_id 1 2 3 4    5
1          1 2 3 4    null
2          1 2 3 null null
3          1 2 3 4    5

预先感谢。

附加。到目前为止，我尝试了另一种方法，但是太复杂了。我交叉连接的过滤属性获得结果。

SELECT t1.id, t1.product_id, t1.property_id as pr_id1, t2.property_id as pr_id2 
FROM
    (SELECT * FROM matches WHERE (property_id = 25 OR property_id = 39)) t1
CROSS JOIN
    (SELECT * FROM matches WHERE (property_id = 29)) t2
ON t1.product_id = t2.product_id

Answer 1

select 
product_id, 
case when sum(case when property_id = 1 then 1 else 0 end )>0 then 1 end '1',
case when sum(case when property_id = 2 then 1 else 0 end )>0 then 2 end '2',
case when sum(case when property_id = 3 then 1 else 0 end )>0 then 3 end '3',
case when sum(case when property_id = 4 then 1 else 0 end )>0 then 4 end '4',
case when sum(case when property_id = 5 then 1 else 0 end )>0 then 5 end '5'
from 
 t
group by product_id

http://sqlfiddle.com/#！9/ce02aa/3/0

Answer 2

谢谢大家的帮助，这给了我几个提示。我找到了与Crossjoins一起做到这一点的另一种方法。基本上，我与具有属性/属性对的交叉加入。

SELECT t1.id,
t1.subproduct_id,
t1.property_id as pr_id1,
t2.property_id as pr_id2,
t3.property_id as pr_id3,
FROM
(SELECT * FROM subproduct_attributes WHERE (property_id = 25 OR property_id = 30)) t1
CROSS JOIN
(SELECT * FROM subproduct_attributes WHERE (property_id = 28)) t2
ON t1.subproduct_id = t2.subproduct_id
CROSS JOIN
(SELECT * FROM subproduct_attributes WHERE (property_id = 27)) t3
ON t1.subproduct_id = t3.subproduct_id

这是Laravel实施的代码。

    $query = $this->builder->Join('subproduct_attributes AS first', 'first.subproduct_id', '=', 'subproducts.id')
                            ->select('subproducts.*',
                                    DB::raw('first.subproduct_id AS sub_id'),
                                    DB::raw('first.property_id AS prop_id'));
    $previous_table = 'first';
    $i = 1;
    foreach ($array as $slug => $attribute) {
        $keys = [];
        foreach ($attribute as $property_key => $value) {
                            // Quick check for property existence
            if (AttributeProperty::find($property_key)) {
                $keys[] = $property_key;
            }
        }
        // if it is first iteration do not crossjoin
        if ($i == 1) {
            $query->whereIn('first.property_id', $keys);
        } else {
            $query->crossJoin("subproduct_attributes AS $slug", function ($join) use ($keys, $slug, $previous_table) {
                $join->on("$slug.subproduct_id", '=', "$previous_table.subproduct_id")
                        ->whereIn("$slug.property_id", $keys);
            });;
            $previous_table = $slug;
        }
        $i++;
    }