今天面试笔试了一道SQL面试题，狠简单
有表Test
CREATE TABLE test
(
date datetime ,
type1 VARCHAR(2) ,
type2 VARCHAR(2) ,
account1 INT ,
account2 INT
)
(
date datetime ,
type1 VARCHAR(2) ,
type2 VARCHAR(2) ,
account1 INT ,
account2 INT
)
-----------------------------------
date type1 type2 account1 account2
2010-02-21 A1 B1 12 14
2010-02-21 A1 B2 12 19
2010-02-21 A1 B1 14 19
2010-02-21 A2 B3 15 12
2010-02-21 A2 B1 17 14
2010-02-21 A3 B2 18 14
2010-02-21 A2 B1 12 11
一条语句查询出
--------------------------------------
date type account1 account2
2010-02-21 A1 38 0
2010-02-21 A2 44 0
2010-02-21 A3 18 0
2010-02-21 B1 0 58
2010-02-21 B2 0 33
2010-02-21 B3 0 12
-----------------------------------------
/*
date type1 type2 account1 account2
2010-02-21 A1 B1 12 14
2010-02-21 A1 B2 12 19
2010-02-21 A1 B1 14 19
2010-02-21 A2 B3 15 12
2010-02-21 A2 B1 17 14
2010-02-21 A3 B2 18 14
2010-02-21 A2 B1 12 11
一条语句查询出
--------------------------------------
date type account1 account2
2010-02-21 A1 38 0
2010-02-21 A2 44 0
2010-02-21 A3 18 0
2010-02-21 B1 0 58
2010-02-21 B2 0 33
2010-02-21 B3 0 12
-----------------------------------------
/*
INSERT INTO test
SELECT '2010-02-21','A1','B1',12,14
UNION
SELECT '2010-02-21','A1','B2',12,19
UNION
SELECT '2010-02-21','A1','B1',14,19
UNION
SELECT '2010-02-21','A2','B3',15,12
UNION
SELECT '2010-02-21','A2','B1',17,14
UNION
SELECT '2010-02-21','A3','B2',18,14
UNION
SELECT '2010-02-21','A2','B1',12,11
SELECT * FROM test
*/
SELECT date,[type1] AS 'type',SUM(account1) AS 'account1',0 AS 'account2' FROM test
GROUP BY date,[type1]
union
SELECT date,[type2],0 ,SUM(account2) FROM test
GROUP BY date,[type2]
