Mysql 表格式直观列出

2019独角兽企业重金招聘Python工程师标准>>>

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) 

 

转载于:https://my.oschina.net/u/3556610/blog/2120261