MYSQL (select LEFT(goodsid,5) AS a FROM glg_stock)

MYSQL (select LEFT(goodsid,5) AS a FROM glg_stock)

升级所有

update glg_stock set goodsdid = LEFT(goodsid,5)

清空表

truncate table glg_journal

insert into glg_journal(num,card,businessdate,add_time,salesmanid,salesman,billing,stockid,stockname,goodsid,sale,price,count,type,brand,refractive,stuff,model,create_time,details)

SELECT a.num,a.card,a.businessdate,a.add_time,a.salesman AS salesmanid,e.name AS salesman,a.billing,b.stocka AS stockid,f.name AS stockname,b.goodsid,b.sale,b.price,b.count,d.type,d.brand,d.refractive,d.stuff,d.model,a.businessdate,a.details

FROM glg_business a JOIN glg_businesslist b ON a.num = b.num JOIN glg_goods_exp c ON b.goodsid = c.goodsname JOIN glg_base_goods d ON c.goodsid = d.goodsid JOIN glg_base_staff e ON a.salesman = e.staffid JOIN glg_base_stores f ON b.stocka = f.stock

WHERE a.numtype = '零售正式单据' AND a.businessdate between '2024-04-01 00:00:00' and '2024-04-15 23:59:59';

退货

SELECT * FROM glg_business WHERE numtype = '零售退货正式' AND businessdate between '2024-01-01 00:00:00' and '2024-04-30 23:59:59'

流水账

SELECT a.num,a.card,a.businessdate,a.add_time,a.salesman,e.name,a.billing,b.stocka,f.name,b.goodsid,b.sale,b.price,b.count,c.model,c.sph,c.cyl,c.addlight,d.type,d.brand,d.refractive,d.stuff,d.model,a.businessdate,a.details

FROM glg_business a JOIN glg_businesslist b ON a.num = b.num left JOIN glg_goods_exp c ON b.goodsid = c.goodsname JOIN glg_base_goods d ON LEFT(b.goodsid,5) = d.goodsid OR b.goodsid = d.goodsid JOIN glg_base_staff e ON a.salesman = e.staffid JOIN glg_base_stores f ON b.stocka = f.stock

WHERE a.numtype = '零售正式单据' AND a.businessdate between '2024-04-01' AND '2024-04-01'

ORDER BY num,businessdate