mysql分拣表_Mysql 表格式直观列出

SELECT

COLUMN_NAME 列名,

COLUMN_KEY 键值,

COLUMN_TYPE 数据类型,

DATA_TYPE 字段类型,

CHARACTER_MAXIMUM_LENGTH 长度,

IS_NULLABLE 是否为空,

COLUMN_DEFAULT 默认值,

COLUMN_COMMENT 备注

FROM

INFORMATION_SCHEMA.COLUMNS

where

table_schema ='database'

AND

table_name = 'table';

多表关联统计显示

select k.part_no,

i.description,

i.owner_id,

sum(qty_onhand) qty_onhand,

sum(qty_demand) qty_demand

from (

SELECT m.part_no part_no,

m.qty_onhand qty_onhand,

0 qty_demand

FROM bay_list b, inventory_location i, inventory_part_in_stock m

WHERE i.logistics_company_id = m.logistics_company_id

AND i.warehouse = m.warehouse

AND i.location_no = m.location_no

AND b.logistics_company_id = i.logistics_company_id

AND b.bay_no = i.bay_no

AND b.warehouse = i.warehouse

AND i.lock_inventory = 0 -- 锁定库存(0-释放,1-锁定)

AND b.bay_type IN (1,2,6) -- 库区类型(1.拣货区,2.存储区, 3: 次品区; 4: 待上架; 5: 退货区; 6: 分拣区)

AND m.logistics_company_id = 10000

AND m.warehouse = 'WHC'

UNION ALL

SELECT d.part_no part_no,

0 qty_onhand,

d.quantity qty_demand

from delivery_order h, delivery_order_line d

WHERE h.order_no = d.order_no

AND h.logistics_company_id = d.logistics_company_id

AND h.warehouse = 'WHC'

AND h.logistics_company_id = 10000

AND h.rowstate NOT IN ( 3, 7, 8, 9)

) k, inventory_part i

WHERE k.part_no = i.part_no

AND i.logistics_company_id = 10000

group by k.part_no, i.description, i.owner_id

having sum(qty_onhand) < sum(qty_demand)