m**********2
发帖数: 2252 | 1 有2个table
table1:
tablID, CustomerID, inquery code,inquerydate
34, 12345, A, 1/1/2010
35, 12346, A, 1/3/2010
36, 12345, B, 1/5/2010
37, 12346, B, 1/8/2010
38, 12345, C, 1/20/2010
39, 12346, C, 1/24/2010
......
table2:
orderID, CustomerID, orderdate
2001, 12345, 1/15/2010
2002, 12346, 1/12/2010
我现在想做个report，如下结果：
customerID, inquery code，inquerydate
12345, B
12346, B
就是说，找出的inquery code在orderdate之前的最后一次。
包子谢！ | e***e
发帖数: 1040 | 2 SELECT CustomerID, Inquery_code FROM table1
WHERE Inquerydate IN (SELECT max(inquerydate) FROM table1,table2 WHERE
table1.CustomerID = table2.CustomerID AND table1.inquerydate
orderdate GROUP BY table1.CustomerID)
包子 please,当然date那个可以自己改一下,懒得写了
【在 m**********2 的大作中提到】
: 有2个table
: table1:
: tablID, CustomerID, inquery code,inquerydate
: 34, 12345, A, 1/1/2010
: 35, 12346, A, 1/3/2010
: 36, 12345, B, 1/5/2010
: 37, 12346, B, 1/8/2010
: 38, 12345, C, 1/20/2010
: 39, 12346, C, 1/24/2010
: ......
| m**********2
发帖数: 2252 | 3 恩，谢谢二姐。包子已发。明天回公司试一下，不行再找你。。。。
【在 e***e 的大作中提到】
: SELECT CustomerID, Inquery_code FROM table1
: WHERE Inquerydate IN (SELECT max(inquerydate) FROM table1,table2 WHERE
: table1.CustomerID = table2.CustomerID AND table1.inquerydate: orderdate GROUP BY table1.CustomerID)
: 包子 please,当然date那个可以自己改一下,懒得写了
| B*****g
发帖数: 34098 | 4 看了“最”字就知道要用partition by
【在 m**********2 的大作中提到】
: 有2个table
: table1:
: tablID, CustomerID, inquery code,inquerydate
: 34, 12345, A, 1/1/2010
: 35, 12346, A, 1/3/2010
: 36, 12345, B, 1/5/2010
: 37, 12346, B, 1/8/2010
: 38, 12345, C, 1/20/2010
: 39, 12346, C, 1/24/2010
: ......
| m**********2
发帖数: 2252 | 5 给个具体的code? 包子送上。谢。
【在 B*****g 的大作中提到】
: 看了“最”字就知道要用partition by
| B*****g
发帖数: 34098 | 6 你太懒了。
select ...
from
(select ..., rank() over (partition by t1.ID order by t1.date desc) rn
from t1, t2
where t1.id = t2.id and t1.date <= t2.date)
where rn = 1
【在 m**********2 的大作中提到】
: 给个具体的code? 包子送上。谢。
| e***e
发帖数: 1040 | 7 Well, the difference between group by and partition by is that group by is
an aggregate function while partition by is analytical. In this specific
case, we just need to know the max for every customerID (of course, subject
to certain constraints) instead of listing every tuple partitioned by
customerID. While using partition is correct, it is not necessary. | m**********2
发帖数: 2252 | 8 同意。你的code比北京的好用。。。
subject
【在 e***e 的大作中提到】
: Well, the difference between group by and partition by is that group by is
: an aggregate function while partition by is analytical. In this specific
: case, we just need to know the max for every customerID (of course, subject
: to certain constraints) instead of listing every tuple partitioned by
: customerID. While using partition is correct, it is not necessary.
| B*****g
发帖数: 34098 | 9 hehe.
is
【在 m**********2 的大作中提到】
: 同意。你的code比北京的好用。。。
:
: subject
|
|
