avatar
sql query question# Database - 数据库
c*n
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?
avatar
B*g
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?

avatar
B*g
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 呢? 我也考虑过, 可是不会用.请多提示一下.
avatar
c*n
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

avatar
B*g
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

avatar
s*c
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;
avatar
c*n
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
avatar
c*n
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;

avatar
b*e
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;

相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。