需求：
一段时间内，购买两次以及以上的且金额大于500用户
表：tb_aj001(订单表：订单号，顾客编号),tb_aj002(订单详情：订单号，订单金额，订单状态，订单时间)
两张表关联查询出顾客编号、以及他的总订单金额，搞了半天、很纠结！
------解决方案--------------------
这种设计方式第一次见，呵呵，订单时间最好是放在订单第一层吧
- SQL code
--tryselect a.顾客编号,sum(b.订单金额) 订单金额from tb_aj001 a,tb_aj002 bwhere a.订单号=b.订单号--and b.订单时间 between ... and ...--and b.订单状态=...having sum(b.订单金额)>500and count(a.订单号)>1group by a.顾客编号;
------解决方案--------------------
- SQL code
WITH t1 AS(SELECT 'D001' oderno,'C01' cuno FROM dual UNION ALLSELECT 'D002','C02' FROM dualUNION ALL SELECT 'D003','C01' FROM dual),t2 AS(SELECT 'D001' oderno,90 odamt,'Finish' status,'20120601' txdat FROM dualUNION ALL SELECT 'D002',600,'Finish','20120602' FROM dualUNION ALL SELECT 'D003',450,'Finish','20120602' FROM dual)SELECT t1.cuno,Sum(t2.odamt)odamt FROM t1,t2 WHERE t1.oderno=t2.oderno GROUP BY t1.cuno HAVING Count(t1.cuno)>1 AND Sum(t2.odamt)>500