sql query question - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

sql query question

sql query question# Database - 数据库

c*n2008-08-01 07:08

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*g2008-08-01 07:08

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*n2008-08-01 07:08

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*g2008-08-01 07:08

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*n2008-08-01 07:08

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*g2008-08-01 07:08

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*c2008-08-01 07:08

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*n2008-08-01 07:08

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*n2008-08-01 07:08

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*e2008-08-01 07:08

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;