二、查询语句存储

一、单表查询（）

select * from wm_bl_batch_dtl
where  tenant_num_id = ? 
and data_sign = ?     
and cancelsign = 'N' 

　　

[{"NAME": "TENANT_NUM_ID", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "TRUE"},
{"NAME": "DATA_SIGN", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "TRUE"}]

　　

select * from wm_bl_batch_dtl
where  tenant_num_id = ? 
and data_sign = ?    
[and item_num_id=?]
[and barcode=?] 
and cancelsign  = 'N' 

[{"NAME": "TENANT_NUM_ID", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "TRUE"},
{"NAME": "DATA_SIGN", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "TRUE"},
{"NAME": "item_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
{"NAME": "barcode", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},]

　　

 

二、两表联查

SELECT a.*,b.item_num_id,b.item_name,b.barcode,b.qty,b.itemid,b.cost_amount,b.tax_rate,b.tax_amount from wm_bl_stockadjusthz_hdr  as a LEFT JOIN wm_bl_stockadjusthz_dtl as b ON a.reserved_no=b.reserved_no
where  a.tenant_num_id = ?  
and a.data_sign = ?       
[and a.reserved_no=?]
[and a.sub_unit_num_id=?]
[and a.batch_supply_unit_num_id=?]
[and a.div_num_id=?]
[and date_format(a.just_date,'%Y-%m-%d') >=?]
[and date_format(a.just_date,'%Y-%m-%d') <=?]
[and a.so_from_type=?]
[and a.type_num_id=?]
[and date_format(a.account_date,'%Y-%m-%d') >=?]
[and date_format(a.account_date,'%Y-%m-%d') <=?]
[and b.barcode=?]
[and b.item_num_id=?]
[and a.status_num_id=?]
[and a.create_user_id=?]
[and a.if_costadjust=?]
and a.cancelsign = 'N'  order by a.last_updtme DESC ,a.create_dtme DESC

　　

[{"NAME": "TENANT_NUM_ID", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "TRUE"},
{"NAME": "DATA_SIGN", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "TRUE"},

{"NAME": "reserved_no", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
{"NAME": "sub_unit_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
{"NAME": "supply_unit_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "div_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "just_dates", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "just_dated", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "so_from_type", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},

{"NAME": "type_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
{"NAME": "account_dates", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
{"NAME": "account_dated", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "barcode", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "item_num_id", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "status_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "create_user_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
{"NAME": "if_costadjust", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
]	

　　

 三、left 加group 加where 

需要注意having和where的用法区别：

1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。

2.where肯定在group by 之前

3.where后的条件表达式里不允许使用聚合函数，而having可以。

 

	SELECT
	a.pick_num_id,
	a.wlbc_num_id,
	a.status_num_id,
	a.print_sign,
	b.qty,
	b.pick_qty,
	a.create_dtme,
	a.type_num_id,
	count(DISTINCT(b.cust_sub_unit_num_id)) AS cust_sub_unit_num_id
FROM
	wm_bl_pick_hdr AS a
LEFT JOIN wm_bl_pick_loc_so_dtl AS b ON a.pick_num_id = b.pick_num_id
LEFT JOIN wm_bl_loc_pick_dtl   AS c ON b.pick_num_id = c.pick_num_id
where  a.tenant_num_id = ? 
and a.data_sign = ?      
[and a.pick_num_id=?]
[and a.wlbc_num_id=?]
[and a.type_num_id=?]
[and a.status_num_id=?]
[and a.print_sign=?]
[and a.cust_sub_unit_num_id=?]
[and date_format(a.create_dtme,'%Y-%m-%d') >=?]   //这里不需要s
[and date_format(a.create_dtme,'%Y-%m-%d') <=?]   //这里不需要d
and a.cancelsign = 'N'  
GROUP BY
	a.pick_num_id,
	a.wlbc_num_id,
	a.status_num_id,
	a.print_sign,
	b.qty,
	b.pick_qty,
	a.type_num_id,
	a.create_dtme
order by a.create_dtme DESC













[{"NAME": "TENANT_NUM_ID", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "TRUE"},
{"NAME": "DATA_SIGN", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "TRUE"},
 
{"NAME": "pick_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
{"NAME": "wlbc_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
{"NAME": "type_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "status_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "print_sign", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "cust_sub_unit_num_id", "TYPE": "NUMBER", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "create_dtmes", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "false"},
{"NAME": "create_dtmed", "TYPE": "STRING", "PARAMSOURCE": "INPUT", "MUSTHAVE": "FALSE"},
]  

　　

 

 三、跨表

查出的数据

select b.itemid,b.item_num_id,b.item_name,b.div_num_id,b.pty_num_3,b.style_desc,b.weight_unit_num_id  
from  mdms_p_product_basic b
where  b.tenant_num_id = ? 
and b.data_sign = ? 
and b.item_num_id = ?  
and b.cancelsign = 'N' 
limit 1



子值

select b.item_num_id,b.series
from scm_bl_return_blacklist b
where b.tenant_num_id = ?
and b.data_sign = ?
and b.cancelsign = 'N'


YKERP-SCM-0060