db2sql语句问题
表1
编号 金额1 金额2 件数 日期
001 100 400 1 2012-1-1
001 600 2012-1-1
002 300 300 2 2012-1-1
003 300 300 2 2012-1-2
002 300 300 2 2012-1-2
003 300 300 2 2012-1-2
……
想要统计如下成样式，应该怎么写sql啊？
注：日期统计不跨月，按月按日期区间筛选。
编号 1号（按日期统计记录数据） 2号 3号 …… 31号
001 2 0 0 …… 31号
002 1 1 0 …… 31号
003 0 2 0 …… 31号
[解决办法]
select id,
sum(case when date='2012-01-01' then num end),
sum(case when date='2012-01-02' then num end),
......//依次类推
from (
select id,date,count(*) as num
from t1
group by id,date
)
group by id
[解决办法]
用存储过程，或者拼SQL。
[解决办法]
参考一下：
CREATE PROCEDURE DB2ADMIN.GET_COUNT_MONTH(IN YM VARCHAR(7),IN DAYS INTEGER,OUT osql VARCHAR(11000))
RESULT SETS 2
LANGUAGE SQL
P: BEGIN
DECLARE mycount INTEGER;
DECLARE countvar VARCHAR(2);
DECLARE mysql VARCHAR(11000);
DECLARE headsql VARCHAR(100);
DECLARE subsql VARCHAR(10000);
DECLARE tailsql VARCHAR(300);
DECLARE stmt STATEMENT;
DECLARE mcur CURSOR WITH HOLD WITH RETURN TO CALLER FOR stmt;
SET mycount = 1;
SET headsql = 'SELECT TEMP.NUM,';
SET subsql= CONCAT('SUM(CASE WHEN TEMP.DATE=''',CONCAT(CONCAT(YM,'-01'),''' THEN TEMP.COUNTS ELSE 0 END) AS D01'));
WHILE mycount < DAYS DO
SET mycount = mycount + 1;
IF mycount < 10 THEN
SET countvar = CONCAT('0',RTRIM(CHAR(mycount)));
END IF;
IF mycount >= 10 THEN
SET countvar = RTRIM(CHAR(mycount));
END IF;
SET subsql = CONCAT(subsql,CONCAT(',',CONCAT('SUM(CASE WHEN TEMP.DATE=''',CONCAT(CONCAT(YM,CONCAT('-',countvar)),CONCAT(''' THEN TEMP.COUNTS ELSE 0 END) AS D',countvar)))));
END WHILE;
SET tailsql = ' FROM (SELECT NUM,DATE,COUNT(*) AS COUNTS FROM DB2ADMIN.MOTHAMOUNT GROUP BY NUM,DATE) TEMP GROUP BY TEMP.NUM ';
SET mysql = CONCAT(CONCAT(headsql,subsql),tailsql);
SET osql = mysql;
PREPARE stmt FROM mysql;
OPEN mcur;
END P
CALL DB2ADMIN.GET_COUNT_MONTH('2012-01',31,?);