w****w
发帖数: 521 | 1 select CustomerID
from (
select a.CustomerID,a.OrderDate,a.SalesOrderID,COUNT(b.OrderDate) Cnt
from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderHeader] b
on a.CustomerID=b.CustomerID
where DATEDIFF(day,a.OrderDate,b.OrderDate) between 0 and 10
group by a.CustomerID,a.OrderDate,a.SalesOrderID
) c
group by c.CustomerID
having MAX(Cnt)>=5;
The key is for each order, count the number of orders in next 10 days.
OrderID here is for order identification, otherwise o... 阅读全帖 |
|
w****w
发帖数: 521 | 2 select CustomerID
from (
select a.CustomerID,a.OrderDate,a.SalesOrderID,COUNT(b.OrderDate) Cnt
from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderHeader] b
on a.CustomerID=b.CustomerID
where DATEDIFF(day,a.OrderDate,b.OrderDate) between 0 and 10
group by a.CustomerID,a.OrderDate,a.SalesOrderID
) c
group by c.CustomerID
having MAX(Cnt)>=5;
The key is for each order, count the number of orders in next 10 days.
OrderID here is for order identification, otherwise o... 阅读全帖 |
|
b*****e
发帖数: 364 | 3 SELECT a.ID, a.ORDERID, MIN(a.orderdate-b.orderdate) as DateDifference,
COUNT(b.orderID)+1 as OrderSequence
FROM Table a
JOIN Table b
ON a.ID=b.ID
WHERE a.orderdate>b.orderDate
GROUP BY a.ID, a.ORDERID; |
|
c*****d
发帖数: 6045 | 4 select o1.CustomerID , COUNT(*)
from orders o1 join orders o2
on o1.CustomerID = o2.CustomerID
where o1. OrderDate > o2. OrderDate
and o1. OrderDate < o2. OrderDate+ 10
group by o1.CustomerID
having COUNT(*) > 5; |
|
m******u
发帖数: 12400 | 5 是不是要distinct一下。不然会重复选出一些customerid吧。select list 中的count
（*）可以去掉。
发信人: coolbid (Dreams bring hopes), 信区: Database
标 题: Re: 请教：找出10天内下了超过5次单的customers
发信站: BBS 未名空间站 (Tue Oct 6 16:04:11 2015, 美东)
select o1.CustomerID , COUNT(*)
from orders o1 join orders o2
on o1.CustomerID = o2.CustomerID
where o1. OrderDate > o2. OrderDate
and o1. OrderDate < o2. OrderDate+ 10
group by o1.CustomerID
having COUNT(*) > 5; |
|
z0
发帖数: 71 | 6 select a.CustomerID ,
a.OrderDate ,
a.OrderID
from your_table a
join your_table b
on a.CustomerID = b.CustomerID
and a.OrderDate between b.OrderDate - 10 and b.OrderDate
group by 1,2,3
having count(1) >= 10 ; |
|
c*****d
发帖数: 6045 | 7 select o1.CustomerID , COUNT(*)
from orders o1 join orders o2
on o1.CustomerID = o2.CustomerID
where o1. OrderDate > o2. OrderDate
and o1. OrderDate < o2. OrderDate+ 10
group by o1.CustomerID
having COUNT(*) > 5; |
|
m******u
发帖数: 12400 | 8 是不是要distinct一下。不然会重复选出一些customerid吧。select list 中的count
（*）可以去掉。
发信人: coolbid (Dreams bring hopes), 信区: Database
标 题: Re: 请教：找出10天内下了超过5次单的customers
发信站: BBS 未名空间站 (Tue Oct 6 16:04:11 2015, 美东)
select o1.CustomerID , COUNT(*)
from orders o1 join orders o2
on o1.CustomerID = o2.CustomerID
where o1. OrderDate > o2. OrderDate
and o1. OrderDate < o2. OrderDate+ 10
group by o1.CustomerID
having COUNT(*) > 5; |
|
z0
发帖数: 71 | 9 select a.CustomerID ,
a.OrderDate ,
a.OrderID
from your_table a
join your_table b
on a.CustomerID = b.CustomerID
and a.OrderDate between b.OrderDate - 10 and b.OrderDate
group by 1,2,3
having count(1) >= 10 ; |
|
A*******g
发帖数: 607 | 10 总结一下,在advanctureworks下验证过：
select distinct Customerid from
(
Select ss1.customerid, ss1.salesorderid，count(*) as CountNumber
from
sales.SalesOrderHeader as ss1
join
sales.SalesOrderHeader as ss2
on ss1.CustomerID = ss2.CustomerID
and ss1.OrderDate <= ss2.OrderDate
and ss1.OrderDate >= ss2.orderDate - 10
group by ss1.customerid, ss1.salesorderid
having count(*) > =5
order by 1
) as result |
|
B*****g
发帖数: 34098 | 11 SELECT id, orderdate, ROW_NUMBER OVER (PARTITION BY id ORDER BY orderdate) A
S ordersequenceID FROM table |
|
m**********2
发帖数: 2252 | 12 有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之前的最后一次。
包子谢！ |
|
m******u
发帖数: 12400 | 13 上贴贴上的例子，漏了header。分别是：customerid， o1.orderdate， o2.orderdate
。可以看出没有连续10天超出5个order。 |
|
s**********o
发帖数: 14359 | 14 还有一个办法，找到ORDER>=5次的CUSTOMER_ID LIST
对于每一个order 超过5次的CUSTOMER的ORDER进行排序
最多ORDER多少次MAX
用CURSOR对每一个ORDER 超过5次的CUSTOMER_ID
从5 LOOP到MAX
如果 orderdate 5-1, 6-2,7-3,8-4，一直LOOP到MAX- (MAX-4) 如果有<10的
跳出LOOP ADD CUSTOMER_ID to final list
NEXT CUSTOMER
ORDER_SEQ MAXORDER CUSTOMER_ID ORDERDATE
1 5 123 1/1/1900
2 5 123 1/2/1900
3 5 123 2/2/1900
4 5 123 3/2/1900
5 ... 阅读全帖 |
|
m******u
发帖数: 12400 | 15 上贴贴上的例子，漏了header。分别是：customerid， o1.orderdate， o2.orderdate
。可以看出没有连续10天超出5个order。 |
|
s**********o
发帖数: 14359 | 16 还有一个办法，找到ORDER>=5次的CUSTOMER_ID LIST
对于每一个order 超过5次的CUSTOMER的ORDER进行排序
最多ORDER多少次MAX
用CURSOR对每一个ORDER 超过5次的CUSTOMER_ID
从5 LOOP到MAX
如果 orderdate 5-1, 6-2,7-3,8-4，一直LOOP到MAX- (MAX-4) 如果有<10的
跳出LOOP ADD CUSTOMER_ID to final list
NEXT CUSTOMER
ORDER_SEQ MAXORDER CUSTOMER_ID ORDERDATE
1 5 123 1/1/1900
2 5 123 1/2/1900
3 5 123 2/2/1900
4 5 123 3/2/1900
5 ... 阅读全帖 |
|
y****r
发帖数: 2 | 17 proc datasets;delete all;run;quit;
%macro try();
%do i=1 %to 9;
data temp&i;
set order040&i.;
orderdate=2014040&i.;
run;
proc datasets;
append base=all data=temp&i. force;
quit;
%end;
%do j=10 %to 15;
data temp&j;
set order04&j.;
orderdate=201404&j.;
run;
proc datasets;
append base=all data=temp&j. force;
quit;
%end;
%mend;
%try(); |
|
e****e
发帖数: 2010 | 18 Bumper给Refund了，Shopper 5% Discount也给了， 还以为Discount泡汤了。
OrderDate Store PurchaseAmount DiscountPayment
06/30/2010 Apple Store $290.00 Paid $14.50
俺比较穷，前10位给包子。
包子已发， Enjoy。 等以后脱贫致富再多发，谢谢 |
|
p**f
发帖数: 3549 | 19 就是我5月份的时候卖过一个NC，结果此人说freeze up之类的，还说开不了机，想换个
新的。我手头自然是没有多的啦，我也不是神医。第一次卖东西，有几个疑问：
1）他5月份买的(具体日期：May 16th)，都过了两个月+，amazon的政策让客户退？
2）我可以让他联系BN厂家吗。。
大家遇到这种，是怎么处理的？我要跟Amazon解释什么吗？原文如下。。
Hi I had orderd a Barnes & Noble NOOK color from you in May of this year and
shortly after receiving it, it started to freeze up and now I can't get it
to turn on at all. I have only used it a handful of times and am very disap
pointed with it. I have never dropped it and have always used it with care.
Can you tel... 阅读全帖 |
|
s**m
发帖数: 1564 | 20 【 以下文字转载自 shopping 讨论区 】
发信人: slim (小时候胖), 信区: shopping
标 题: Can Verizon iPhone 5 from Target be used in China?
发信站: BBS 未名空间站 (Fri Nov 2 19:03:45 2012, 美东)
I pre-orderd a Verizon iPhone 5 (w/o contract at full retail price) at
Target. Now it is available for pick up and It's for a relative in China.
But I notice people in fleamarket board mainly wants iphone 5 from apple
store.
So, Can Verizon iPhone 5 from Target be used in China? What is the
difference between iphone 5 from apple store and ... 阅读全帖 |
|
s**m
发帖数: 1564 | 21 【 以下文字转载自 shopping 讨论区 】
发信人: slim (小时候胖), 信区: shopping
标 题: Can Verizon iPhone 5 from Target be used in China?
发信站: BBS 未名空间站 (Fri Nov 2 19:03:45 2012, 美东)
I pre-orderd a Verizon iPhone 5 (w/o contract at full retail price) at
Target. Now it is available for pick up and It's for a relative in China.
But I notice people in fleamarket board mainly wants iphone 5 from apple
store.
So, Can Verizon iPhone 5 from Target be used in China? What is the
difference between iphone 5 from apple store and ... 阅读全帖 |
|
s**m
发帖数: 1564 | 22 【 以下文字转载自 shopping 讨论区 】
发信人: slim (小时候胖), 信区: shopping
标 题: Can Verizon iPhone 5 from Target be used in China?
发信站: BBS 未名空间站 (Fri Nov 2 19:03:45 2012, 美东)
I pre-orderd a Verizon iPhone 5 (w/o contract at full retail price) at
Target. Now it is available for pick up and It's for a relative in China.
But I notice people in fleamarket board mainly wants iphone 5 from apple
store.
So, Can Verizon iPhone 5 from Target be used in China? What is the
difference between iphone 5 from apple store and ... 阅读全帖 |
|
s**********n
发帖数: 2601 | 23 typicall there is no way you can close a loan within 3 weeks. you need a
month at least. Contact your attorney and see if he/she has requested an
extension on both mortgage commitment date and closing date. Have you orderd
the appraisal? Has the seller's attorney ordered title commitment? Do you
know what are the conditions are? |
|
z**********g
发帖数: 350 | 24 我们7月2号申请的，已经一个多月了。本来7月26号close的，这都过了两个星期了。问
题是这种拖沓的情况正常吗？
orderd |
|
p****t
发帖数: 5814 | 25 haha, got 100 amex gift card, ONLY used one card, orderd a gift card from walmart.com, very good luck, bad luck is
I almost have no baozi:
quote:
Congratulations! You just received the following gift for shopping at
Walmart.com, courtesy of The Gift Chain from American Express:
$100 American Express Gift Card
Your Gift Card will be mailed within three weeks after you provide your
shipping information. Just click on the link below by no later than
11:59:59 PM CT on 2/29/12 to redeem your gift: |
|
a****a
发帖数: 289 | 26 How do you know you can get $25 american express,i just orderd $100
gift card online, got an email said:
Dear
Thank you for ordering from Costco.com. We have received the order and,
after processing it, will send it to the fulfillment facility. Please keep
this email for your records.
is this successed or failed? |
|
a****a
发帖数: 289 | 27 How do you know you can get $25 american express,i just orderd $100
gift card online, got an email said:
Dear
Thank you for ordering from Costco.com. We have received the order and,
after processing it, will send it to the fulfillment facility. Please keep
this email for your records.
is this successed or failed? |
|
f********r
发帖数: 12 | 28 orderd 2 ..came with pinless Sunny gift cards....bad luck :( |
|
w******i
发帖数: 1941 | 29 There are amaZon sticker on box. Can't return to local and normal local
store don't carry value box. I orderd girl pull up instead of boys. And I
have boy. Also want to know if can get exchange for free. |
|
p********2
发帖数: 9939 | 30 帮我鉴定一下是啥。我没有在它家买过东西。它家系统出问题了吗？
P*********[email protected] to me
%%ITEMDETAIL%%
Thank you for shopping at gymboree.com. Your order has been received and is
being processed. Please see below for details regarding your order:
Order Summary
Rewards #: %%REWARDSNUMBER%%
Order Details For Order #%%ORDERNUMBER%%
Ordered On:%%ORDERDATE%%
Ordered By:
%%ORDEREDBY%%
Shipping Address:
%%SHIPMENT%% Payment Method:
%%PAYMENTMETHOD%%
Subtotal Of Items: $64.95
Less Promotional Discounts: -... 阅读全帖 |
|
H*V
发帖数: 2770 | 31 orderd and claimed easy rebate, let's see what will happen next |
|
s**m
发帖数: 1564 | 32 I pre-orderd a Verizon iPhone 5 (w/o contract at full retail price) at
Target. Now it is available for pick up and It's for a relative in China.
But I notice people in fleamarket board mainly wants iphone 5 from apple
store.
So, Can Verizon iPhone 5 from Target be used in China? What is the
difference between iphone 5 from apple store and from other store?
thanks |
|
s**m
发帖数: 1564 | 33 I pre-orderd a Verizon iPhone 5 (w/o contract at full retail price) at
Target. Now it is available for pick up and It's for a relative in China.
But I notice people in fleamarket board mainly wants iphone 5 from apple
store.
So, Can Verizon iPhone 5 from Target be used in China? What is the
difference between iphone 5 from apple store and from other store?
thanks |
|
h********y
发帖数: 598 | 34 Thank you for your answer. |
|
s****t
发帖数: 461 | 35 too bad you already orderded.
I want to buy one as well.
anyone else wants one? |
|
B*******a
发帖数: 410 | 36 Thank you so much!
I orderd atomic M100 boots online and am still waiting.
With doing reaearch online and looking into my skis and binding, I got
several conclusions to mount bindings onto integrated system. I am wondering
if I am right.
(1) set bindings to my boots size, for me it is 27.5
(2) slide bindings into rails built on skis
(3) with boots on the bindings, adjust forword presure. For my bindings(
marker brand), there are no indicating windows for forword presure. Instead,
there are screw |
|
i****e
发帖数: 913 | 37 Xbox One Ordered from Rakuten $75 points
TitanFall Pre-orderd from Kmart $25 points
共省$100. |
|
b***y
发帖数: 554 | 38 orderd the burner, but the media is still too expensive at $6 a piece. |
|
i********u
发帖数: 70 | 39 hirts garden is not good????
i ordered via amazon . hirts seem to be a big nursary in ohio,
those i orderd are all small trees in a 3 inch pot, take forever to blossom. |
|
m**********2
发帖数: 2252 | 40 有个table，有3个columns。一个是客户ID,一个orderID，一个orderdate。每一个
order就有一个orderID。比如客户ID 500，会有orderID 201，302，809. 客户ID 600
，会有orderID 211，298，344。
现在我想在这个table加一个column，ordersequenceID，比如客户ID 500，根据
orderID的大小，如果orderID是201，就是1,orderID是302，就是2,orderID是809，就
是3. 同样，比如比如客户ID 600，根据orderID的大小，如果orderID是211，就是1,
orderID是298，就是2,orderID是344，就是3。
这个应该怎样做？
谢谢。 |
|
|
e***e
发帖数: 1040 | 42 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那个可以自己改一下,懒得写了 |
|
i****a
发帖数: 36252 | 43 In SQL 2008, can can I generate an xml file with mixed elements in it?
For example:
Alice Smith
123 Maple Street
Mill Valley
CA
90952
Robert Smith
8 Oak Avenue
Old Town
PA
... 阅读全帖 |
|
y****9
发帖数: 144 | 44 1. Why 20 min is not good enough？If this is a one time job, I would put
more emphasis on correctness of implementation than speed
2. If speed is a concern, I am thinking of in SQL server, clustered index is
preferred to heap table. So PK is usually your clusterd index, so if you
can inset into the table with data ordered by (A, B, C), may be it can be
faster. i.e create the table structure with clustered index , then insert
with orderd records.
just my 2 cents. |
|
y****9
发帖数: 144 | 45 1. Why 20 min is not good enough？If this is a one time job, I would put
more emphasis on correctness of implementation than speed
2. If speed is a concern, I am thinking of in SQL server, clustered index is
preferred to heap table. So PK is usually your clusterd index, so if you
can inset into the table with data ordered by (A, B, C), may be it can be
faster. i.e create the table structure with clustered index , then insert
with orderd records.
just my 2 cents. |
|
l**n
发帖数: 264 | 46 MySQL下有表格：
CustomerID OrderDate
101 2014-12-11
102 2014-12-13
101 2014-12-14
103 2014-12-15
... ...
如果想找出m天内下了超过k次单的顾客，能不能不用sub query实现？ |
|
w****n
发帖数: 266 | 47 select custid
from yourtable
where sysdate-orderdate》m
group by custid
having count(custid)》k
tested in oracle |
|
B*****g
发帖数: 34098 | 48 苦逼的大师group by时丢了一个
orderdate |
|
B*****g
发帖数: 34098 | 49 group by o1.CustomerID, o1.orderdate |
|
c*****d
发帖数: 6045 | 50 确实少写了orderdate, 另外最后having count(*)不是5，是 5-1 = 4
select distinct o2.Customer_ID
from orders o1, orders o2
where o1.Customer_ID = o2.Customer_ID
and o1.order_date > o2.order_date
and o1.order_date <= o2.order_date + 10
group by o2.Customer_ID, o2.order_date
having COUNT(*) >= 4 |
|
