mysql分时查询并求和

为了防止自己忘记，也为了感谢帮助过我的人

1. 分时查询

a. 分时查询挂镀线 条 产量

SELECT  
        DATE_FORMAT(intime, '%Y-%m-%d %H:00:00') AS time,
        sum(replace(num, '条', '')) as '分时数量'  
FROM factorymanager.warehouse 
where 
    name = '挂镀线' and right(num, 1) = '条' and   
    to_days(now())-3 = to_days(intime)  
    group by time
    order by intime asc 

b. 分时查询滚镀线 Kg产量

SELECT  
        intime  AS time,
        replace(num, 'Kg', '') as '分时数量'  
FROM factorymanager.warehouse 
where 
    name = '滚镀线' and right(num, 2) = 'Kg' and   
    to_days(now())-3 = to_days(intime)  
    group by time
    order by intime asc 

c. 分时查询贴片线 kg 产量

SELECT  
        intime  AS time,
        replace(num, 'Kg', '') as '分时数量'  
FROM factorymanager.warehouse 
where 
    name = '贴片线' and right(num, 2) = 'Kg' and   
    to_days(now())-3 = to_days(intime)  
    group by time
    order by intime asc 

 

2. 按时间查询

a. 查询时间段内滚镀线kg产量

SELECT  
    sum(replace(num, 'Kg', '') )as '时间段内数量'  
FROM factorymanager.warehouse 
where 
    name = '滚镀线' and right(num, 2) = 'Kg' and   
    intime > '2019-08-29 12:00:00' and intime < '2019-09-30 12:00:00'
    order by intime asc 

b. 查询时间段内挂镀线条产量

SELECT  
    sum(replace(num, '条', '') )as '时间段内数量'  
FROM factorymanager.warehouse 
where 
    name = '挂镀线' and right(num, 1) = '条' and   
    intime > '2019-08-29 12:00:00' and intime < '2019-09-30 12:00:00'
    order by intime asc 

c. 查询时间段内贴片线kg产量

SELECT  
    sum(replace(num, 'Kg', '') )as '时间段内数量'  
FROM factorymanager.warehouse 
where 
    name = '贴片线' and right(num, 2) = 'Kg' and   
    intime > '2019-08-29 12:00:00' and intime < '2019-09-30 12:00:00'
    order by intime asc 

 

3. 查询某个员工工作记录

除胶工位比较特殊，暂时不更改表格

SELECT     c.name as '制程名称', 
        e.positionID as '工位名称', 
        c.batch as '产品批号', 
        e.uptime as '操作时间', 
        c.type as '产品型别', 
        c.costumer as '客户名称',
        c.num as '产品数量' ,
        e.employeeName as '工作人员'
        FROM factorymanager.product c, factorymanager.operationrecords e 
        where 
        c.serialID <> '-1-' and  
        ((e.positionID <> '除胶工位' and e.employeeName = '刘必余') or (e.positionID = '除胶工位' and e.partName03 = '刘必余')) and 
        e.uptime between '2019-09-27 18:56' and '2019-09-30 18:56' and locate(CONCAT(c.batch, '#'), CONCAT(e.productBatch,'#')) > 0 order by e.uptime asc

4. 查询某个工位产量

a. 查询除胶工位1的产量,除胶工位比较特殊，一个电脑三个工位

SELECT     c.name as '制程名称', 
        e.positionID as '工位名称', 
        c.batch as '产品批号', 
        e.uptime as '操作时间', 
        c.type as '产品型别', 
        c.costumer as '客户名称',
        c.num as '产品数量' ,
        e.partName03 as '工作人员'
        FROM factorymanager.product c, factorymanager.operationrecords e 
        where 
        c.serialID <> '-1-' and  
        e.positionID = '除胶工位'  and  e.partID01 = 'CJ0001' and
        e.uptime between '2019-09-27 18:56' and '2019-09-30 18:56' and locate(CONCAT(c.batch, '#'), CONCAT(e.productBatch,'#')) > 0 order by e.uptime asc

b. 查询挂镀上料制程操作记录

SELECT     c.name as '制程名称', 
        e.positionID as '工位名称', 
        c.batch as '产品批号', 
        e.uptime as '操作时间', 
        c.type as '产品型别', 
        c.costumer as '客户名称',
        c.num as '产品数量' ,
        e.employeeName as '工作人员'
FROM factorymanager.product c, factorymanager.operationrecords e where 
        c.serialID <> '-1-' and 
        c.name = '挂镀线' and
        e.positionID = '挂镀上料'    and
        e.uptime between '2019-09-27 18:56' and '2019-09-30 18:56' 
        and locate(CONCAT(c.batch, '#'), CONCAT(e.productBatch,'#')) > 0 
        order by e.uptime asc