表数据 job operation deptID
11212 1 200-2
11212 2 210-2
11212 3 220-2
11212 4 210-2
11212 5 240-2
11212 6 210-2
11213 1 200-2
11213 2 210-2
11213 3 220-2
11213 4 230-2
11213 5 220-2
11213 6 240-2
11214 1 200-2
11214 2 210-2
11214 3 220-2
11214 4 230-2
我想要的结果
11212 1 200-2
11212 2 210-2
11212 3 220-2
11212 5 240-2
11213 1 200-2
11213 2 210-2
11213 3 220-2
11213 4 230-2
11213 6 240-2
11214 1 200-2
11214 2 210-2
11214 3 220-2
11214 4 230-2也就是当 Job+deptID相同的时候 我只要工序最小的行 其余数据读出来
可能会用到
select sp.pc_WorkCenter,sp.pc_JobNo,sp.pc_Operation from tPControl as sp join (select min(pc_Operation) as pc_Operation,pc_JobNo from tPControl
where group by pc_JobNo,pc_WorkCenter ) pc
on sp.pc_JobNo=pc.pc_JobNo and sp.pc_Operation=pc.pc_Operation order by sp.pc_JobNo,sp.pc_Operation,sp.pc_WorkCenter
这个语句就是把最小的提取出来
我还要做的就是 还要把其他数据读出来 请问怎么做
11212 1 200-2
11212 2 210-2
11212 3 220-2
11212 4 210-2
11212 5 240-2
11212 6 210-2
11213 1 200-2
11213 2 210-2
11213 3 220-2
11213 4 230-2
11213 5 220-2
11213 6 240-2
11214 1 200-2
11214 2 210-2
11214 3 220-2
11214 4 230-2
我想要的结果
11212 1 200-2
11212 2 210-2
11212 3 220-2
11212 5 240-2
11213 1 200-2
11213 2 210-2
11213 3 220-2
11213 4 230-2
11213 6 240-2
11214 1 200-2
11214 2 210-2
11214 3 220-2
11214 4 230-2也就是当 Job+deptID相同的时候 我只要工序最小的行 其余数据读出来
可能会用到
select sp.pc_WorkCenter,sp.pc_JobNo,sp.pc_Operation from tPControl as sp join (select min(pc_Operation) as pc_Operation,pc_JobNo from tPControl
where group by pc_JobNo,pc_WorkCenter ) pc
on sp.pc_JobNo=pc.pc_JobNo and sp.pc_Operation=pc.pc_Operation order by sp.pc_JobNo,sp.pc_Operation,sp.pc_WorkCenter
这个语句就是把最小的提取出来
我还要做的就是 还要把其他数据读出来 请问怎么做
FROM LI,
(
SELECT CAST(JOB AS VARCHAR) + '-' + DEPTID AS MARK,
MIN(operation) AS OPERATION
FROM LI
GROUP BY CAST(JOB AS VARCHAR) + '-' + DEPTID) LO
WHERE LI.JOB = LEFT(LO.MARK,CHARINDEX('-',LO.MARK) - 1)
AND LI.deptID = RIGHT(LO.MARK,LEN(LO.MARK)- CHARINDEX('-',LO.MARK))
AND LI.operation = LO.operation
11213 3 220-2
也就是当 Job+deptID相同的时候 我只要工序最小的行 其余数据读出来 ,呵呵,你不是要取最小的吗???还是只取小的??
( job int,
operation int,
deptid char(5)
)
insert into r3
select 11212,1,'200-2'union all select 11212,2,'210-2'union all
select 11212,3,'220-2'union all select 11212,4,'210-2'union all
select 11212,5,'240-2'union all select 11212,6,'210-2'union all
select 11213,1,'200-2'union all select 11213,2,'210-2'union all
select 11213,3,'220-2'union all select 11213,4,'230-2'union all
select 11213,5,'220-2'union all select 11213,6,'240-2'union all
select 11214,1,'200-2'union all select 11214,2,'210-2'union all
select 11214,3,'220-2'union all select 11214,4,'230-2'select job,operation,deptid from r3 a where not exists (select * from r3 where a.job=job and deptid=a.deptid and a.operation>operation )job operation deptid
----------- ----------- ------
11212 1 200-2
11212 2 210-2
11212 3 220-2
11212 5 240-2
11213 1 200-2
11213 2 210-2
11213 3 220-2
11213 4 230-2
11213 6 240-2
11214 1 200-2
11214 2 210-2
11214 3 220-2
11214 4 230-2