我有像下面这样的桌子。考虑一下查询
select invoice_mth, inv_amt from table xdetails
where mobile_number=9080808080
表中的数据
mobile_number invoice_mth inv_amt
9080808080 2010-10 20
9080808080 2010-11 30
9080808080 2010-12 40
我必须像下面这样显示表中的数据。
我要发票月分开每个月和amt分开。
MOBILE_NUMBER inv_m1 inv_m2 inv_m3 amt1 amt2 amt3
------- ----------------------------------------------------------
9080808080 2010-10 2010-11 2010-12 20 30 40
为了像上面那样显示数据,我要做什么?
发布于 2015-03-17 08:18:43
您可以使用标准的枢轴查询:
SQL> SELECT * FROM t;
MOBILE_NUMBER INVOICE INV_AMT
------------- ------- ----------
9080808080 2010-10 20
9080808080 2010-11 30
9080808080 2010-12 40
SQL>
SQL> SELECT *
2 FROM
3 (SELECT mobile_number, invoice_mth, inv_amt FROM t
4 ) PIVOT (MIN(invoice_mth) AS inv_mth,
5 SUM(inv_amt) AS inv_amt
6 FOR (invoice_mth) IN ('2010-10' AS m1, '2010-11' AS m2, '2010-12' AS m3))
7 ORDER BY mobile_number;
MOBILE_NUMBER M1_INV_ M1_INV_AMT M2_INV_ M2_INV_AMT M3_INV_ M3_INV_AMT
------------- ------- ---------- ------- ---------- ------- ----------
9080808080 2010-10 20 2010-11 30 2010-12 40
SQL>
发布于 2015-11-06 01:09:42
如果希望输出中有固定数量的列:
SQL Fiddle
Oracle 11g R2架构设置
CREATE TABLE TABLE_NAME ( mobile_number, invoice_mth, inv_amt ) AS
SELECT 9080808080, '2010-10', 20 FROM DUAL
UNION ALL SELECT 9080808080, '2010-11', 30 FROM DUAL
UNION ALL SELECT 9080808080, '2010-12', 40 FROM DUAL;
查询1
SELECT mobile_number,
MAX( CASE RN WHEN 1 THEN invoice_mth END ) AS inv_m1,
MAX( CASE RN WHEN 2 THEN invoice_mth END ) AS inv_m2,
MAX( CASE RN WHEN 3 THEN invoice_mth END ) AS inv_m3,
MAX( CASE RN WHEN 1 THEN inv_amt END ) AS amt1,
MAX( CASE RN WHEN 2 THEN inv_amt END ) AS amt2,
MAX( CASE RN WHEN 3 THEN inv_amt END ) AS amt3
FROM (
SELECT t.*,
ROW_NUMBER() OVER ( PARTITION BY mobile_number ORDER BY invoice_mth ASC ) AS rn
FROM TABLE_NAME t
)
GROUP BY mobile_number
结果
| MOBILE_NUMBER | INV_M1 | INV_M2 | INV_M3 | AMT1 | AMT2 | AMT3 |
|---------------|---------|---------|---------|------|------|------|
| 9080808080 | 2010-10 | 2010-11 | 2010-12 | 20 | 30 | 40 |
https://stackoverflow.com/questions/29093922
复制相似问题