我有以下表格结构
id item_code type qty
1 1011 Purchase 20
2 1011 Purchase 30
3 1011 Sales -2
4 1011 Purchase 10
5 1011 Sales -7
6 1011 Sales -10
7 1011 Purchase 13
8 1011 Purchase 7
我如何加入表格,以便我可以按照以下模式获取数据
qty_p qty_s
20 -2
30 -7
10 -10
13 null
7 null
这样做的动机是为了在单独的列中获得买卖数据
最佳答案
我认为您需要在此处使用变量,以便可以将其加入记录。
SELECT a.qty PurchaseQuantity,
b.qty SalesQuantity
FROM
(
SELECT @rank1 := @rank1 + 1 Rank1,
a.qty
FROM tableName a, (SELECT @rank1 := 0) b
WHERE a.type = 'Purchase'
ORDER BY a.ID
) a
LEFT JOIN
(
SELECT @rank2 := @rank2 + 1 Rank2,
a.qty
FROM tableName a, (SELECT @rank2 := 0) b
WHERE a.type = 'Sales'
ORDER BY a.ID
) b ON a.Rank1 = b.Rank2
ORDER BY a.Rank1
SQLFiddle Demo