mysql - SQL GROUP BY(多个组)

Question

编辑:似乎在 sqlfiddle 中工作......一定是其他地方发生的错误。谢谢大家。

<小时/>

http://sqlfiddle.com/#!2/f867c/1

<小时/>

我真的很纠结于 SQL 中的计数和分组。

重要的表是 transaction_items ——如果有人以每辆 100 英镑的价格购买 4 辆相同的自行车，那么就会在 transaction_items 中插入四行，每行一行数量(这样做是有原因的，我不需要 quantity 列。

我需要做的是从 transaction_items 获取所有记录，其中 charge_id = '12345' 并按 transaction_items.usersid 对它们进行分组AND transaction_items.item_code - 最终给我一个组列表，每个组都与一个唯一的用户和一个唯一的产品相关。因此，如果用户的购物车中有多种产品，则他们可以拥有多个组。如果用户 1 购买了 2 x 商品 1 和 4 x 商品 2，则用户 1 将有两组，一组用于商品 1，计数为 2，一组用于商品 2，计数为 4。目前，我似乎只能得到一组。

所以如果我在 transaction_items 中有这个:

|  id  |  charge_id  |  usersid  |  item_code  |
|   1  |      12345  |       12  |         66  |
|   2  |      12345  |       12  |         66  |
|   3  |      66666  |       11  |         66  |
|   4  |      12345  |       12  |         66  |
|   5  |      12345  |        6  |         22  |
|   6  |      77777  |       10  |         66  |
|   7  |      12345  |       12  |         66  |
|   8  |      44444  |       23  |         66  |
|   9  |      12345  |        6  |         22  |

我想首先按 charge_id AND usersid AND item_code 对数据进行分组，我想取回此数据:

Array
(

    // The array should contain arrays for each pair of usersid's and item_codes, that all have the same charge_id

    [0] => Array
        (
            //This is a unique group for usersid and item_code

            [charge_id] => '12345'
            [usersid] => '12' 
            [item_code] => '66'
            [num_of_records] => '4'          
        )
    [1] => Array
        (
            //This is a unique group for usersid and item_code

            [charge_id] => '12345'
            [usersid] => '6'
            [item_code] => '22'
            [num_of_records] => '2'          
        )
)

所以我希望得到一个数组:

1. 对具有相同 charge_id、userid 和 item_code 的所有记录进行分组
2. 以可用格式返回数据，并带有计数(就像数量一样)
3. 应仅对每个组进行计数(数量)，而不是对所有记录进行计数(数量)

我现在得到的似乎只是一个产品ID，一个用户ID，而是反射(reflect)所有用户和项目的记录总数，这与我的目标完全相反! (下面的虚拟输出反射(reflect)了上面示例表的使用):

Array
(
    [0] => Array
        (
            [charge_id] => '12345'
            [usersid] => '12'
            [item_code] => '66'
            [num_of_records] => '6'          
        )
)

我现在的查询:

$items = $db->query("
                SELECT 
                    count(transaction_items.id) as num_of_records,
                    transaction_items.*
                FROM 
                    transaction_items 
                WHERE
                    transaction_items.charge_id = :charge
                AND
                    transaction_items.lmsid = :lmsid
                GROUP
                BY
                    transaction_items.usersid,
                    transaction_items.charge_id,
                    transaction_items");

我错过了什么？

谢谢!

<小时/>

编辑和更新

这一切都取决于 WHERE 子句。当我删除它时，查询工作正常，但会收集 items 表中的所有记录，而我只想要 charge_id = '12345' 的记录。因此，我认为这个问题的主题严格与 GROUP BY 和 WHERE 的组合使用以及为什么我没有得到我期望的结果有关。

为了简化问题，现在的查询是:

$items = $db->query("SELECT 
                        count(transaction_items.id) as num_of_records,
                        item_code, 
                        usersid
                    FROM 
                        transaction_items
                    WHERE
                        charge_id = '12345'
                    GROUP
                    BY
                        usersid,
                        charge_id,
                        item_code
                        ");

Answer 1

不确定我是否明白 - 这是你的意思吗？

CREATE TABLE  transaction_items
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,charge_id  INT NOT NULL
,usersid  INT NOT NULL
,item_code INT NOT NULL
);

INSERT INTO transaction_items VALUES
(1  ,12345  ,12  ,66),
(2  ,12345  ,12  ,66),
(3  ,66666  ,11  ,66),
(4  ,12345  ,12  ,66),
(5 ,12345 , 6 ,22),
(6 ,77777 ,10 ,66),
(7 ,12345 ,12 ,66),
(8 ,44444 ,23 ,66),
(9 ,12345 , 6 ,22);

SELECT charge_id
     , usersid
     , item_code
     , COUNT(*) 
  FROM transaction_items 
 GROUP 
    BY charge_id
     , usersid
     , item_code;
+-----------+---------+-----------+----------+
| charge_id | usersid | item_code | COUNT(*) |
+-----------+---------+-----------+----------+
|     12345 |       6 |        22 |        2 |
|     12345 |      12 |        66 |        4 |
|     44444 |      23 |        66 |        1 |
|     66666 |      11 |        66 |        1 |
|     77777 |      10 |        66 |        1 |
+-----------+---------+-----------+----------+

编辑:我仍然很困惑......

SELECT charge_id
     , usersid
     , item_code
     , COUNT(*)
  FROM transaction_items
 WHERE charge_id = 12345
 GROUP
    BY charge_id
     , usersid
     , item_code;
+-----------+---------+-----------+----------+
| charge_id | usersid | item_code | COUNT(*) |
+-----------+---------+-----------+----------+
|     12345 |       6 |        22 |        2 |
|     12345 |      12 |        66 |        4 |
+-----------+---------+-----------+----------+

!?!?!