代码之家 › 专栏 › 技术社区 › Success Man

如何将值和总值相同的行与mysql query合并?

group-by merge join sql mysql

0

Success Man · 技术社区 · 7 年前

我的查询sql如下:

SELECT a.transaction_number a.date, a.item_number, b.desc, a.variant_code, sum(a.quantity) AS total_qty, a.cost
FROM `items_details` AS a
JOIN `items` AS b ON b.id = a.item_number
WHERE a.item_number = 0101010
GROUP BY a.variant_code
ORDER BY transaction_number, variant_code

结果是这样的:

我想把桌子做成这样:

我该怎么做?

1 回复 | 直到 7 年前

1

Strawberry 7 年前

只是为了好玩。。。

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(colA INT NOT NULL
,colB INT NOT NULL
,colC INT NOT NULL
,PRIMARY KEY(colA,colB,colC)
);

INSERT INTO my_table VALUES
(101,1001,31),
(101,1002,14),
(102,1001,15),
(102,1001,92),
(102,1002,65),
(103,1001,35),
(103,1002,89);

SELECT CASE WHEN @prevA=colA THEN '' ELSE colA END colA
     , CASE WHEN @prevB=colB THEN '' ELSE colB END colB
     , colC
     , @prevA:=colA
     , @prevB:=colB
  FROM my_table x
     , (SELECT @prevA:=null,@prevB:=null) vars
 ORDER
    BY x.colA, x.colB;

+------+------+------+--------------+--------------+
| colA | colB | colC | @prevA:=colA | @prevB:=colB |
+------+------+------+--------------+--------------+
| 101  | 1001 |   31 |          101 |         1001 |
|      | 1002 |   14 |          101 |         1002 |
| 102  | 1001 |   15 |          102 |         1001 |
|      |      |   92 |          102 |         1001 |
|      | 1002 |   65 |          102 |         1002 |
| 103  | 1001 |   35 |          103 |         1001 |
|      | 1002 |   89 |          103 |         1002 |
+------+------+------+--------------+--------------+