数据如下(这个是我写了一个比较复杂sql语句才得到的,并不是一个直接的table数据):
customerid name machine selldate (4个栏位)
26 A C5350501 2005-1-2
26 A C5350905 2006-6-30
50 B S9630501 2002-1-2
50 B T14800604 2004-5-8
50 B PN1200056 2004-9-16
50 B RMF453056 2005-6-9
99 C S9630631 2005-9-1
88 C T10800668 2006-1-1
.......................................如上,customerid表示客户id,name表示客户名称,machine表示客户购买的机器编号,selldate表示购买日期,该数据系列中每个客户最少有两台2006-6-30以前购买的机器,而且我已经按照order by customerid,selldate排序了.现在问题是:
我要得到在2006年上半年重复购买机器的老客户
也就是说:
要搜出至少一台机器在2006-1-1以前购买而且还至少有一台机器是在2006-1-1至2006-6-30之间购买的客户.
例如上述数据中应该搜出
A,C两家客户.
请大家赐教,该如何做到??
customerid name machine selldate (4个栏位)
26 A C5350501 2005-1-2
26 A C5350905 2006-6-30
50 B S9630501 2002-1-2
50 B T14800604 2004-5-8
50 B PN1200056 2004-9-16
50 B RMF453056 2005-6-9
99 C S9630631 2005-9-1
88 C T10800668 2006-1-1
.......................................如上,customerid表示客户id,name表示客户名称,machine表示客户购买的机器编号,selldate表示购买日期,该数据系列中每个客户最少有两台2006-6-30以前购买的机器,而且我已经按照order by customerid,selldate排序了.现在问题是:
我要得到在2006年上半年重复购买机器的老客户
也就是说:
要搜出至少一台机器在2006-1-1以前购买而且还至少有一台机器是在2006-1-1至2006-6-30之间购买的客户.
例如上述数据中应该搜出
A,C两家客户.
请大家赐教,该如何做到??
我用的是mysql 5.0.0
好像必须是5.0.3以上的版本才可以PROCEDURE
请大家指教啊!
这样不行吗?
例如:
select * from (select * from (你的结果集) where selldate <2006-1-1) as t where t.selldate between 2006-1-1 and 2006-6-30 类似这样的不行吗?
如果能用PROCEDURE的话就没必要这么长了.
from ym_sell_reg a,ym_sell_info b,ym_customer c,ym_machine_stockpile d
where c.customerid=d.customerid and b.sellId = a.sellId
and b.machStockId = d.machStockId
and c.customerid in
(select f.customerid
from
(select m.customerid
from
(select distinct c.customerid
from ym_sell_reg a,ym_sell_info b,ym_customer c,ym_machine_stockpile d
where c.customerid=d.customerid and b.sellId = a.sellId
and b.machStockId = d.machStockId
and c.customerid in
(select f.customerid
from
(select a.name,a.customerid,count(MachStockID) as NumberMachine
from ym_customer a,ym_machine_stockpile c
where a.cityid=1 and a.deleteflag='N'
and a.customerid=c.customerid
group by a.customerid having NumberMachine>=2
order by a.customerid) f)
and a.selldate<'2006-1-1'
order by c.customerid,a.selldate) m
where exists
(select 1 from
(select distinct(c.customerid),c.name
from ym_sell_reg a,ym_sell_info b,ym_customer c,ym_machine_stockpile d
where c.customerid=d.customerid and b.sellId = a.sellId
and b.machStockId = d.machStockId
and c.customerid in(select f.customerid
from (select a.name,a.customerid,count(MachStockID) as NumberMachine
from ym_customer a,ym_machine_stockpile c
where a.cityid=1 and a.deleteflag='N'
and a.customerid=c.customerid
group by a.customerid having NumberMachine>=2
order by a.customerid) f)
and a.selldate between '2006-1-1' and '2006-6-30'
order by c.customerid,a.selldate) n
where m.customerid=n.customerid)) f)
and a.selldate between '2006-1-1' and '2006-6-30'
order by c.customerid,a.selldate把我的sql放在这里做个记录,顺便给分!^-^
类似如下语句即可实现:select * from (你的SQL语句) as mytable where selldate <='2006-1-1' and selldate >='2006-6-30'