query: in sql server 2005 - 未名空间MITBBS历史存档

query: in sql server 2005# Database - 数据库

x*e2007-08-26 07:08

1 楼

you have a table called order history.
there are 3 columns in the table:
order_id (unique, indexed)
customer_ID (not unique, indexed)
order_date.(not null)
your boss asked you to find out customers who ordered within last 30 days
and how many orders they made during this timeframe as a second column.
"group by with where clause, .." you said to yourself
You figured out the query successfully and gave it to you boss.
Here come his another question:
"I also want to know the customers who have orde

c*e2007-08-26 07:08

2 楼

put a case statement in the sum
sum(if condition satisfied then whatever else null end)

【在 x***e 的大作中提到】

: you have a table called order history.
: there are 3 columns in the table:
: order_id (unique, indexed)
: customer_ID (not unique, indexed)
: order_date.(not null)
: your boss asked you to find out customers who ordered within last 30 days
: and how many orders they made during this timeframe as a second column.
: "group by with where clause, .." you said to yourself
: You figured out the query successfully and gave it to you boss.
: Here come his another question:

n*n2007-08-26 07:08

3 楼

我怎末觉得他的意思是先找60内的
然后再在这些人里挑30天内？

【在 x***e 的大作中提到】

: you have a table called order history.
: there are 3 columns in the table:
: order_id (unique, indexed)
: customer_ID (not unique, indexed)
: order_date.(not null)
: your boss asked you to find out customers who ordered within last 30 days
: and how many orders they made during this timeframe as a second column.
: "group by with where clause, .." you said to yourself
: You figured out the query successfully and gave it to you boss.
: Here come his another question:

k*e2007-08-26 07:08

4 楼

这位同学，请独立完成，不要在这里问家庭作业......

【在 x***e 的大作中提到】

: you have a table called order history.
: there are 3 columns in the table:
: order_id (unique, indexed)
: customer_ID (not unique, indexed)
: order_date.(not null)
: your boss asked you to find out customers who ordered within last 30 days
: and how many orders they made during this timeframe as a second column.
: "group by with where clause, .." you said to yourself
: You figured out the query successfully and gave it to you boss.
: Here come his another question:

k*e2007-08-26 07:08

5 楼

CASE WHEN ... THEN .... END
SELECT CustomerID,SUM(CASE WHEN <30 days THEN 1 ELSE 0 END, count(*)
FROM table WHERE <60 days group by CustomerID

【在 c*******e 的大作中提到】

: put a case statement in the sum
: sum(if condition satisfied then whatever else null end)

x*e2007-08-26 07:08

6 楼

Actually, that is an interview question I got.
I provided the same answer as you guys.
But it seems that the A3 manager is not very satisfied.
I am thinking maybe we need to use pivot?

【在 k********e 的大作中提到】

: CASE WHEN ... THEN .... END
: SELECT CustomerID,SUM(CASE WHEN <30 days THEN 1 ELSE 0 END, count(*)
: FROM table WHERE <60 days group by CustomerID

c*e2007-08-26 07:08

7 楼

分别求,然后做个outer join?

【在 x***e 的大作中提到】

: Actually, that is an interview question I got.
: I provided the same answer as you guys.
: But it seems that the A3 manager is not very satisfied.
: I am thinking maybe we need to use pivot?

t*g2007-08-26 07:08

8 楼

hmm...不考虑efficiency先，
第一个好作，过去30天记录按customer_id groupby 一下，得view v1
把条件变一下，过去60天记录按customer_id groupby 一下得view v2
v1 right out join v2

【在 x***e 的大作中提到】

: you have a table called order history.
: there are 3 columns in the table:
: order_id (unique, indexed)
: customer_ID (not unique, indexed)
: order_date.(not null)
: your boss asked you to find out customers who ordered within last 30 days
: and how many orders they made during this timeframe as a second column.
: "group by with where clause, .." you said to yourself
: You figured out the query successfully and gave it to you boss.
: Here come his another question:

k*e2007-08-26 07:08

9 楼

did he emphasis it's sql server 2005?
pivot is not standard. It is only available on sql server 2005.

【在 x***e 的大作中提到】

: Actually, that is an interview question I got.
: I provided the same answer as you guys.
: But it seems that the A3 manager is not very satisfied.
: I am thinking maybe we need to use pivot?

k*e2007-08-26 07:08

10 楼

效率奇低。
不过兴许面世A3只懂这个，看不懂case when... haha

【在 c*******e 的大作中提到】

: 分别求,然后做个outer join?