c***n
发帖数: 921 | 1 Table A: 3 attributes (id, qty, date) e.g. row (a001, 50, 200705)
Now want to produce a table B which contains 3 attributes (id, sum1, sum2)
sum1= sum(qty) between 200705 and 200804
sum2 = sum(qty) between 200605 and 200704
Is there any smart method to get table B? |
B*****g
发帖数: 34098 | 2 http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.
db2.udb.doc/admin/r0005646.htm
D
【在 c***n 的大作中提到】
: Table A: 3 attributes (id, qty, date) e.g. row (a001, 50, 200705)
: Now want to produce a table B which contains 3 attributes (id, sum1, sum2)
: sum1= sum(qty) between 200705 and 200804
: sum2 = sum(qty) between 200605 and 200704
: Is there any smart method to get table B?
|
c***n
发帖数: 921 | 3 怎么用case 呢? 我也考虑过, 可是不会用.请多提示一下.
【在 B*****g 的大作中提到】
: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.
: db2.udb.doc/admin/r0005646.htm
:
: D
|
B*****g
发帖数: 34098 | 4 select id,
sum(case when date between 200705 and 200804 then qty else 0),
sum(case when date between 200605 and 200704 then qty else 0)
from table a
【在 c***n 的大作中提到】
: 怎么用case 呢? 我也考虑过, 可是不会用.请多提示一下.
|
c***n
发帖数: 921 | 5 这个我试验了,可是不行. 让我下周再试试.
不过我写的是 sum(case....) as newName
【在 B*****g 的大作中提到】
: select id,
: sum(case when date between 200705 and 200804 then qty else 0),
: sum(case when date between 200605 and 200704 then qty else 0)
: from table a
|
B*****g
发帖数: 34098 | 6 select id,
sum(case when date between 200705 and 200804 then qty else 0 end),
sum(case when date between 200605 and 200704 then qty else 0 end)
from table a
【在 c***n 的大作中提到】
: 这个我试验了,可是不行. 让我下周再试试.
: 不过我写的是 sum(case....) as newName
|
s*****c
发帖数: 24 | 7 select id,
sum(case when date between 200705 and 200804 then qty else 0 end),
sum(case when date between 200605 and 200704 then qty else 0 end)
from table a
group by id; |
c***n
发帖数: 921 | 8 200705 是 date, 不是id.
id qty date
a01 50 200601
a01 55 200604
a01 60 200605
a01 50 200701
a01 55 200702
a01 60 200704
a01 45 200805
a01 55 200806
a02 50 200603
a02 55 200604
a02 60 200608
a02 50 200703
a02 55 200704
a02 60 200705
a02 45 200801
a02 60 200804
a02 55 200806
a03 10 200807
期望得到
id sum1(200605-200704 sum) sum2(200705-200804 sum)
a01 225 null (or 0)
a |
c***n
发帖数: 921 | 9 运行成功了.
【在 s*****c 的大作中提到】
: select id,
: sum(case when date between 200705 and 200804 then qty else 0 end),
: sum(case when date between 200605 and 200704 then qty else 0 end)
: from table a
: group by id;
|
b*****e
发帖数: 364 | 10 This one is the right answer.
【在 s*****c 的大作中提到】
: select id,
: sum(case when date between 200705 and 200804 then qty else 0 end),
: sum(case when date between 200605 and 200704 then qty else 0 end)
: from table a
: group by id;
|
