大家好,分销存行业中。
我有3张表,product 产品表, stock 库存表, warehouse 仓库表,
product表定义了产品的名称p_name,经销价sale_price,建议零售价等信息suggest_retail_price;
stock库存表定义了当前库存stock_now,可用库存stock_logic;
warehouse仓库表定义了仓库的名称w_name等信息。
3表的关联是stock库存表是中间表有pid,wid外键。
product产品表<----stock库存表------>warehouse仓库表我可以查询各仓库的产品库存和该产品价格等信息select pi.product_name, pi.sale_price, pi.suggest_retail_price,
s.stock_now, s.stock_logic, w.ware_name
from sc_tm_product_info pi, sc_tt_stock s, sc_tm_warehouse w
where 1=1
and pi.sc_tm_product_info_id = s.sc_tm_product_info_id
and s.sc_tm_warehouse_id = w.sc_tm_warehouse_id
--and pi.sc_tm_product_info_id = 106317
order by pi.suggest_retail_price desc现在有一个需求,就是我要查出某个产品的总库存,这个时候就不用关联warehouse仓库表了。。select pi.sc_tm_product_info_id, sum(s.stock_now), sum(s.stock_logic)
from sc_tm_product_info pi, sc_tt_stock s
where pi.sc_tm_product_info_id = s.sc_tm_product_info_id
and pi.sc_tm_product_info_id = 106317
group by pi.sc_tm_product_info_id
order by sum(s.stock_now) desc如果现在还要拿到该产品的sale_price, suggest_retail_price呢?怎么写
再进一步,如果还要拿到有该产品库存的仓库的名称,仓库名称用逗号相隔。。怎么写
先谢谢了
我有3张表,product 产品表, stock 库存表, warehouse 仓库表,
product表定义了产品的名称p_name,经销价sale_price,建议零售价等信息suggest_retail_price;
stock库存表定义了当前库存stock_now,可用库存stock_logic;
warehouse仓库表定义了仓库的名称w_name等信息。
3表的关联是stock库存表是中间表有pid,wid外键。
product产品表<----stock库存表------>warehouse仓库表我可以查询各仓库的产品库存和该产品价格等信息select pi.product_name, pi.sale_price, pi.suggest_retail_price,
s.stock_now, s.stock_logic, w.ware_name
from sc_tm_product_info pi, sc_tt_stock s, sc_tm_warehouse w
where 1=1
and pi.sc_tm_product_info_id = s.sc_tm_product_info_id
and s.sc_tm_warehouse_id = w.sc_tm_warehouse_id
--and pi.sc_tm_product_info_id = 106317
order by pi.suggest_retail_price desc现在有一个需求,就是我要查出某个产品的总库存,这个时候就不用关联warehouse仓库表了。。select pi.sc_tm_product_info_id, sum(s.stock_now), sum(s.stock_logic)
from sc_tm_product_info pi, sc_tt_stock s
where pi.sc_tm_product_info_id = s.sc_tm_product_info_id
and pi.sc_tm_product_info_id = 106317
group by pi.sc_tm_product_info_id
order by sum(s.stock_now) desc如果现在还要拿到该产品的sale_price, suggest_retail_price呢?怎么写
再进一步,如果还要拿到有该产品库存的仓库的名称,仓库名称用逗号相隔。。怎么写
先谢谢了
--拿到该产品的sale_price, suggest_retail_price
--用分析函数
select distinct pi.sc_tm_product_info_id,pi.sale_price, pi.suggest_retail_price,
sum(s.stock_now)over(partition by pi.sc_tm_product_info_id),
sum(s.stock_logic)over(partition by pi.sc_tm_product_info_id)
from sc_tm_product_info pi, sc_tt_stock s
where pi.sc_tm_product_info_id = s.sc_tm_product_info_id
and pi.sc_tm_product_info_id = 106317
order by 4 desc--拿到有该产品库存的仓库的名称
select distinct pi.product_name, pi.sale_price, pi.suggest_retail_price,
sum(s.stock_now)over(partition by pi.sc_tm_product_info_id),
sum(s.stock_logic)over(partition by pi.sc_tm_product_info_id),
w.ware_name
from sc_tm_product_info pi, sc_tt_stock s, sc_tm_warehouse w
where 1=1
and pi.sc_tm_product_info_id = s.sc_tm_product_info_id
and s.sc_tm_warehouse_id = w.sc_tm_warehouse_id
--and pi.sc_tm_product_info_id = 106317
order by pi.suggest_retail_price desc
--拿到有该产品库存的仓库的名称
select distinct pi.product_name, pi.sale_price, pi.suggest_retail_price,
sum(s.stock_now)over(partition by pi.sc_tm_product_info_id),
sum(s.stock_logic)over(partition by pi.sc_tm_product_info_id),
wm_concat(w.ware_name)over(partition by pi.sc_tm_product_info_id) --逗号连接
from sc_tm_product_info pi, sc_tt_stock s, sc_tm_warehouse w
where 1=1
and pi.sc_tm_product_info_id = s.sc_tm_product_info_id
and s.sc_tm_warehouse_id = w.sc_tm_warehouse_id
--and pi.sc_tm_product_info_id = 106317
order by pi.suggest_retail_price desc
请问下“wm_concat(w.ware_name)over(partition by pi.sc_tm_product_info_id) --逗号连接
”这个是什么意思?
select distinct pi.sc_tm_product_info_id,pi.sale_price, pi.suggest_retail_price,
sum(s.stock_now)over(partition by pi.sc_tm_product_info_id),
sum(s.stock_logic)over(partition by pi.sc_tm_product_info_id)
from sc_tm_product_info pi, sc_tt_stock s
where pi.sc_tm_product_info_id = s.sc_tm_product_info_id
order by 4 desc现在我要比较stock_now 和 stock_logic 不同的记录
我给上面两个字段加上别名.sum(s.stock_now)over(partition by pi.sc_tm_product_info_id) a,
sum(s.stock_logic)over(partition by pi.sc_tm_product_info_id) b
...
and a <> b这样报错