需求，原型图如下：
最后，做出来的效果如图：
需求是，做个列表功能，名字叫第一次订单信息统计表。列出客户的第一次下单的信息（下单包括订单，生产单，出货单）。
当接到这个需求的时候，蛮高兴的，因为好久没有接这么简单的需求了。然而我还是太年轻了。仔细一想，客户编号，客户名称，一个表，这个简单。下订单日期，下订单号，两个表（橱柜订单表，门板订单表，这两个表的字段），客户有可能在橱柜下单，也有可能在门板下单，但不管怎么样，在两个表中要找出最早的下单。
下订单金额（门板报价表，橱柜报价表两个表中的一个字段，两个订单表与两个报价表关联的字段是订单号），找出最早的订单之后，要找出它对应的订单金额。
比如在橱柜订单表找到了最早的订单，那么根据关联的是订单号，在橱柜报价表找到相应的下订单金额的值，
如果在门板订单表找到了最早的订单，那么根据关联的是订单号，在门板报价表找到相应的下订单金额的值。
那么问题来了，找出的最早订单怎么知道是在橱柜订单表还是在门板订单表。这个让我纠结了一下。
后面的下生产单日期，下生产单单号（三个表，橱柜生产单表，门板生产单表，单门板生产单表），下生产单金额（一样也在门板报价表，橱柜报价表，三个生产单表与两个报价表关联的字段是订单号，门板和单门板都属于门板，但这两个表的字段各不相同，具体不说了）。
出货日期，出货订单号（两个表，门板出库表，单门板出库表），出货金额（门板报价表，橱柜报价表，跟出库表关联的是订单号）。
列出来的这些表，刚好是10个表，后续需求增加了，有新的表，这个暂且不说。
客户表跟订单表，生产单表，出货表，这三个关联字段是客户编号，
订单表，生产单表，出货表，这三个跟报价表关联的都是订单号，
订单表要找出最早日期的，找出来之后，要找出对应的金额。
生产单表要找出最早日期的，找出来之后，要找出对应的金额。
出货表表要找出最早日期的，找出来之后，要找出对应的金额。
说了一大堆，终于把老板提出的需求讲完了。
简单一句话，假如客户先后下了订单A,B,C三个，接着先后下了B,C两个生产单，最后下了C出货单，那么列表出来的，订单是A，生产单是B，出货单是C，另外显示对应的金额。一句话概括了需求。一开始想着用SQL查出来，不过，那样会很麻烦，SQL不好写，因为，假如找出最早的订单，怎么才能知道这个订单是橱柜的，还是门板的，因为还要找出对应的报价金额。前面也说了，
比如在橱柜订单表找到了最早的订单，那么根据关联的是订单号，在橱柜报价表找到相应的下订单金额的值，
如果在门板订单表找到了最早的订单，那么根据关联的是订单号，在门板报价表找到相应的下订单金额的值。
用if来判断吗？每个都这样，会很冗余。事后证明，确实是，SQL会更长。
问了个同事，同事让我查出想要的字段，然后再构建对象，再用后台代码实现。我一听，哭笑不得。这得多复杂啊。
觉得这个方法不可行，行不通。还是通过SQL找出来。于是分割三部分，
先找出订单日期，订单号和订单金额，接着找出下生产单日期，下生产单号，下生产单金额，最后是出货表。
只要找出最早的订单日期，这个部分搞定了，那么剩下两个的依葫芦画瓢。
中途写SQL花了点时间，费脑细胞，没有写if这样的东西，问了另外一个同事，他教我用row_number ()函数，最后写出来了。
select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
cus_ord_quo_pro_stk.created_date_time,cus_ord_quo_pro_stk.ord_no1,cus_ord_quo_pro_stk.actual_amount1
from (
select row_number () over(partition by c.customer_no order by order_quo.created_date_time) ranknum,
c.customer_no,c.short_name,order_quo.created_date_time,order_quo.ord_no1,order_quo.actual_amount1
from customer c left join
(select ord.customer_no,ord.created_date_time,ord.order_no ord_no1,quo_order.order_no,quo_order.actual_amount actual_amount1 from
(
select d.customer_no,d.created_date_time,d.order_no from dp_order d where d.frozen = false
union
select s.customer_no,s.created_date_time,s.order_no from split_order s
)ord inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
inner join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on ord.order_no = quo_order.order_no ) order_quo
on c.customer_no = order_quo.customer_no
) cus_ord_quo_pro_stk where ranknum =1
查询出来的结果如下：
这个是第一部分的SQL语句
接着依葫芦画瓢，写第二个SQL语句
select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
cus_ord_quo_pro_stk.make_date,cus_ord_quo_pro_stk.ord_no2,cus_ord_quo_pro_stk.actual_amount2
from (
select row_number () over(partition by c.customer_no order by production_quo.make_date) ranknum,
c.customer_no,c.short_name,production_quo.make_date,production_quo.ord_no2,production_quo.actual_amount2
from customer c left join
(select production.customer_no,production.make_date,production.order_no ord_no2,quo_order.actual_amount actual_amount2 from
(
select g.ref_order_no,g.customer_no,g.make_date,g.order_no from general_dp_pp_order g where g.frozen = false
union
select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from prod_process_cabinet_order p
union
select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from prod_process_door_panel_order pr
)production inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
left join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on production.ref_order_no = quo_order.order_no ) production_quo
on c.customer_no = production_quo.customer_no
) cus_ord_quo_pro_stk where ranknum =1
查询出来的结果如下：
最后是第三个SQL的部分
select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
cus_ord_quo_pro_stk.stock_out_date,cus_ord_quo_pro_stk.ord_no,cus_ord_quo_pro_stk.actual_amount from (
select row_number () over(partition by c.customer_no order by stock_quo.stock_out_date) ranknum,
c.customer_no,c.short_name,stock_quo.stock_out_date,stock_quo.ord_no,stock_quo.actual_amount
from customer c left join
(select stock.customer_no,stock.stock_out_date,stock.order_no ord_no,quo_order.actual_amount from
(
select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no from gene_dp_stock_out_order g_stock where g_stock.frozen = false
union
select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock
)stock inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
left join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on stock.order_no = quo_order.order_no ) stock_quo
on c.customer_no = stock_quo.customer_no
) cus_ord_quo_pro_stk where ranknum =1
查询出来的结果如下：
最后，把这3个SQL语句拼接起来，弄成一条SQL
SQL:
select cus_ord_quo_pro_stk.customer_no,cus_ord_quo_pro_stk.short_name,
cus_ord_quo_pro_stk.created_date_time,cus_ord_quo_pro_stk.ord_no1,cus_ord_quo_pro_stk.actual_amount1,
cus_ord_quo_pro_stk.make_date,cus_ord_quo_pro_stk.ord_no2,cus_ord_quo_pro_stk.actual_amount2,
cus_ord_quo_pro_stk.stock_out_date,cus_ord_quo_pro_stk.ord_no,cus_ord_quo_pro_stk.actual_amount from (
select row_number () over(partition by c.customer_no order by order_quo.created_date_time) ranknum,
c.customer_no,c.short_name,order_quo.created_date_time,order_quo.ord_no1,order_quo.actual_amount1,
production_quo.make_date,production_quo.ord_no2,production_quo.actual_amount2,stock_quo.stock_out_date,stock_quo.ord_no,stock_quo.actual_amount
from customer c left join
(select ord.customer_no,ord.created_date_time,ord.order_no ord_no1,quo_order.order_no,quo_order.actual_amount actual_amount1 from
(
select d.customer_no,d.created_date_time,d.order_no from dp_order d where d.frozen = false
union
select s.customer_no,s.created_date_time,s.order_no from split_order s
)ord inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
inner join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on ord.order_no = quo_order.order_no ) order_quo
on c.customer_no = order_quo.customer_no
left join
(select production.customer_no,production.make_date,production.order_no ord_no2,quo_order.actual_amount actual_amount2 from
(
select g.ref_order_no,g.customer_no,g.make_date,g.order_no from general_dp_pp_order g where g.frozen = false
union
select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from prod_process_cabinet_order p
union
select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from prod_process_door_panel_order pr
)production inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
left join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on production.ref_order_no = quo_order.order_no ) production_quo
on c.customer_no = production_quo.customer_no
left join
(select stock.customer_no,stock.stock_out_date,stock.order_no ord_no,quo_order.actual_amount from
(
select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no from gene_dp_stock_out_order g_stock where g_stock.frozen = false
union
select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock
)stock inner join
(select qu.order_no,quo.actual_amount
from quotation_unified_order qu
left join quotation_unified_order_quotation_unified_bodies quo
on qu.id = quo.quotation_unified_order_id
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo) quo_order
on stock.order_no = quo_order.order_no ) stock_quo
on c.customer_no = stock_quo.customer_no
) cus_ord_quo_pro_stk where ranknum =1
sql有点长，没事，看得懂，不会很难。
老大看了我写的SQL语句，有点惊讶，那么长，而且看起来有点乱。他教我怎么优化SQL，
优化后的SQL:
select row_number () over(partition by c.customer_no order by first_order.created_date_time,first_production.make_date,first_stock.stock_out_date) ranknum,
c.customer_no,c.short_name,first_order.created_date_time,first_order.ord_no,first_order.actual_amount,
first_production.make_date,first_production.ord_no,first_production.actual_amount,
first_stock.stock_out_date,first_stock.ord_no,first_stock.actual_amount
from customer c
left join
(
select * from(
select row_number () over(partition by order_quo.customer_no order by order_quo.created_date_time) ranknum,
order_quo.customer_no, order_quo.created_date_time,order_quo.ord_no,order_quo.actual_amount
from (
(select d.customer_no,d.created_date_time,d.order_no ord_no from dp_order d where d.frozen = false
union
select s.customer_no,s.created_date_time,s.order_no from split_order s
union
select f.customer_no,f.order_date,f.order_no from first_order_history_data f
)ord
inner join
(select qu.order_no,qu.actual_amount
from quotation_unified_order qu
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
union
select f.order_no,f.order_amount from first_order_history_data f
)quo_order
on ord.ord_no = quo_order.order_no) order_quo
) aa
where ranknum=1
) first_order on c.customer_no = first_order.customer_no
left join (
select * from(
select row_number () over(partition by prod_quo.customer_no order by prod_quo.make_date) ranknum,
prod_quo.customer_no, prod_quo.make_date,prod_quo.ord_no, prod_quo.actual_amount
from (
(
select g.ref_order_no,g.customer_no,g.make_date,g.order_no ord_no from general_dp_pp_order g where g.frozen = false
union
select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from prod_process_cabinet_order p
union
select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from prod_process_door_panel_order pr
union
select f.order_no,f.customer_no,f.production_date,f.production_no from first_order_history_data f
)production
inner join
(select qu.order_no,qu.actual_amount
from quotation_unified_order qu
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
union
select f.order_no,f.order_amount from first_order_history_data f
) production_quo on production.ref_order_no = production_quo.order_no ) prod_quo
) bb
where ranknum=1
) first_production on c.customer_no = first_production.customer_no
left join(
select * from(
select row_number () over(partition by stk_quo.customer_no order by stk_quo.stock_out_date) ranknum,
stk_quo.customer_no, stk_quo.stock_out_date,stk_quo.ord_no,stk_quo.actual_amount
from (
(
select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no ord_no from gene_dp_stock_out_order g_stock where g_stock.frozen = false
union
select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock
union
select f.customer_no,f.stock_date,f.stock_no from first_order_history_data f
)stock
inner join
(select qu.order_no,qu.actual_amount
from quotation_unified_order qu
union
select dqo.order_no,dqo.final_amount from dp_quotation_order dqo
union
select f.order_no,f.order_amount from first_order_history_data f
) stock_quo on stock.ord_no = stock_quo.order_no ) stk_quo
) cc
where ranknum=1
) first_stock on c.customer_no = first_stock.customer_no
虽然看起来更长了，但SQL语句更优美了，看起来更好看了
另外，老大叫我在Repository层把这个SQL拆成三部分，订单，生产单，出货单，这三个部分放在常量里。一个主体，拼接三个常量。
Repository层代码：
常量部分：
static final String FIRST_ORDER = "select * from( "
+ "select row_number () over(partition by order_quo.customer_no order by "
+ "order_quo.created_date_time) ranknum, "
+ "order_quo.customer_no, order_quo.created_date_time,order_quo.ord_no ord_no1, "
+ "order_quo.actual_amount actual_amount1 from ( "
+ "(select d.customer_no,d.created_date_time,d.order_no ord_no from dp_order d where d.frozen = false "
+ "union select s.customer_no,s.created_date_time,s.order_no from split_order s "
+ "union select f.customer_no,f.order_date,f.order_no from first_order_history_data f "
+ ")ord inner join (select qu.order_no,qu.actual_amount " + "from quotation_unified_order qu union "
+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
+ "select f.order_no,f.order_amount from first_order_history_data f )quo_order "
+ "on ord.ord_no = quo_order.order_no) order_quo ) aa where ranknum=1 ";
static final String FIRST_PRODUCTION = "select * from( "
+ "select row_number () over(partition by prod_quo.customer_no order by "
+ "prod_quo.make_date) ranknum, "
+ "prod_quo.customer_no, prod_quo.make_date,prod_quo.ref_order_no, "
+ "prod_quo.actual_amount actual_amount2 from ( ( "
+ "select g.ref_order_no,g.customer_no,g.make_date,g.order_no ord_no from "
+ "general_dp_pp_order g where g.frozen = false union "
+ "select p.ref_order_no,p.customer_no,p.document_make_date,p.order_no from "
+ "prod_process_cabinet_order p union "
+ "select pr.ref_order_no,pr.customer_no,pr.document_make_date,pr.order_no from "
+ "prod_process_door_panel_order pr union "
+ "select f.order_no,f.customer_no,f.production_date,f.production_no from "
+ "first_order_history_data f "
+ ")production inner join (select qu.order_no,qu.actual_amount "
+ "from quotation_unified_order qu union "
+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
+ "select f.order_no,f.order_amount from first_order_history_data f "
+ ") production_quo on production.ref_order_no = production_quo.order_no) prod_quo ) bb "
+ "where ranknum=1 ";
static final String FIRST_STOCK = "select * from( "
+ "select row_number () over(partition by stk_quo.customer_no order by "
+ "stk_quo.stock_out_date) ranknum, "
+ "stk_quo.customer_no, stk_quo.stock_out_date,stk_quo.ord_no ord_no3, "
+ "stk_quo.actual_amount actual_amount3 from ( ( "
+ "select g_stock.customer_no,g_stock.stock_out_date,g_stock.order_no ord_no from "
+ "gene_dp_stock_out_order g_stock where g_stock.frozen = false union "
+ "select s_stock.customer_no,s_stock.stock_out_date,s_stock.order_no from stock_out_order s_stock "
+ "union select f.customer_no,f.stock_date,f.stock_no from first_order_history_data f "
+ ")stock inner join (select qu.order_no,qu.actual_amount "
+ "from quotation_unified_order qu union "
+ "select dqo.order_no,dqo.final_amount from dp_quotation_order dqo union "
+ "select f.order_no,f.order_amount from first_order_history_data f "
+ ") stock_quo on stock.ord_no = stock_quo.order_no ) stk_quo ) cc where ranknum=1 ";
主体拼接常量：
@Query(value = "select row_number () over(partition by c.customer_no order by first_order.created_date_time,"
+ "first_production.make_date,first_stock.stock_out_date) ranknum, "
+ "c.customer_no,c.short_name,first_order.created_date_time,first_order.ord_no1,"
+ "first_order.actual_amount1, "
+ "first_production.make_date,first_production.ref_order_no,first_production.actual_amount2, "
+ " first_stock.stock_out_date,first_stock.ord_no3,first_stock.actual_amount3 "
+ " from customer c left join ( " + FIRST_ORDER
+ ") first_order on c.customer_no = first_order.customer_no left join ( " + FIRST_PRODUCTION
+ ") first_production on c.customer_no = first_production.customer_no left join( " + FIRST_STOCK
+ ") first_stock on c.customer_no = first_stock.customer_no "
+ "where c.short_name like %:shortName% limit :pageSize offset :pageOffset", nativeQuery = true)
List<Object[]> findAllByPageWithShortName(@Param(value = "shortName") String shortName,
@Param(value = "pageSize") Integer pageSize, @Param(value = "pageOffset") Integer pageOffset);
这样就好看多了。如果不拆分的话，看起来就一大堆，时间长了，我再倒回去看，我都能看晕，更何况别人，后期维护也很麻烦。
实体类代码，用来显示列表数据：
public class FirstOrderReport {
private String customerNo;
private String customerName;
private Date orderDate;
private String orderNo;
private String orderAmount;
private Date productionDate;
private String productionNo;
private String productionAmount;
private Date stockDate;
private String stockNo;
private String stockAmount;
//getter、setter...
}
service层代码，业务逻辑
@Transactional
@Service
public class FirstOrderReportServiceImpl implements FirstOrderReportService {
@Autowired
private CustomerRepository customerRepository;
@Override
//分页+查询列表查询
public Page<FirstOrderReport> getFirstOrderReports(String customerName, Integer pageIndex, Integer pageSize) {
Pageable pageable = new PageRequest(pageIndex, pageSize);
List<FirstOrderReport> firstorderReports = customerRepository
.findAllByPageWithShortName(customerName, pageSize, pageSize * pageIndex).stream()
.map(firstorder -> genFirstorderReport(firstorder)).collect(Collectors.toList());
Long totalCount = customerRepository.getCountByShortName(customerName);
return new PageImpl<FirstOrderReport>(firstorderReports, pageable, totalCount);
}
//对查询出来的数据构建出实体类对象
private FirstOrderReport genFirstorderReport(Object[] firstorder) {
FirstOrderReport firstorderReport = new FirstOrderReport();
if (firstorder != null && firstorder.length > 0) {
firstorderReport.setCustomerNo(firstorder[1].toString());
firstorderReport.setCustomerName(firstorder[2].toString());
firstorderReport.setOrderDate(
firstorder[3] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[3].toString()));
firstorderReport.setOrderNo(firstorder[4] == null ? "" : firstorder[4].toString());
firstorderReport.setOrderAmount(firstorder[5] == null ? "" : firstorder[5].toString());
firstorderReport.setProductionDate(
firstorder[6] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[6].toString()));
firstorderReport.setProductionNo(firstorder[7] == null ? "" : firstorder[7].toString());
firstorderReport.setProductionAmount(firstorder[8] == null ? "" : firstorder[8].toString());
firstorderReport.setStockDate(
firstorder[9] == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", firstorder[9].toString()));
firstorderReport.setStockNo(firstorder[10] == null ? "" : firstorder[10].toString());
firstorderReport.setStockAmount(firstorder[11] == null ? "" : firstorder[11].toString());
}
return firstorderReport;
}
}
controller层代码，这个不多说
@RestController
@RequestMapping("api/marketingmanagement/first-order-report")
public class FirstOrderReportController extends ExceptionResponse {
@Autowired
private FirstOrderReportService firstOrderReportService;
@RequestMapping(value = "/gene-first-order-reports", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
public Page<FirstOrderReport> getFirstorderReports(@RequestParam String customerName,
@RequestParam Integer pageIndex, @RequestParam Integer pageSize) {
return firstOrderReportService.getFirstOrderReports(customerName.trim(), pageIndex, pageSize);
}
}
终于弄完了，效果如图
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
