问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

sql的两个表进行关联怎样按条件显示

发布网友 发布时间：2022-04-07 17:10

我来回答

共2个回答

懂视网 时间：2022-04-07 21:31


and charindex(p.MachineNo,e.MachineNo)>0
and p.BCCode=e.BCCode
and e.ShowOEE=1
and ((p.CurrDate between e.startDate and e.EndDate) or (p.CurrDate>e.startDate and e.EndDate is null))


where p.CurrDate between @DateBegin and @DateEnd
and DispatchNo<>‘‘
and (@EmpID = ‘‘ or e.EmpID = @EmpID or e.EmpName like @EmpID +‘%‘)
order by p.CurrDate, p.BCCode, p.MachineNo



update a set a.PlanProductTime=b.PlanProductTime
from #DailyTableEmp a
inner join #PlanTime b on a.MachineNo=b.MachineNo and a.BCCode=b.BCCode

update #DailyTableEmp set SocketRate=case when StandSocketNum=0 then 0 else SocketNum/(StandSocketNum*1.0) end--出数率
, BadRate=case when ProductedNum=0 then 0 else BadNum/(ProductedNum*1.0) end--不良率
, EfficiencyRate=case when TheoreticalQty=0 then 0 else ProductedNum/(TheoreticalQty*1.0) end--机台效率(有效率)
--, Availability=case when DispatchTime-NoPlanTime=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime) end--有效开机率(表现性，有效性)
, Availability=case when DispatchTime-NoPlanTime-PermissionTime-DaiRen=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime-DaiRen) end--有效开机率(表现性，有效性)
where Flag=2

update #DailyTableEmp set OEE=EfficiencyRate*Availability*(1-BadRate)--机台效率*有效开机率*良率
where Flag=2

--派工单层的OEE导入完成
--==================================================================================================================================

insert into #PlanTime
select distinct MachineNo, PlanProductTime, BCCode from #DailyTableEmp where CurrDate between @DateBegin and @DateEnd

insert into #DailyTableEmp(MachineNo,EmpGroupNO, EmpID, CurrDate, BCCode, StandSocketNum, SocketNum, TZ_StandEmp, ActualEmp, DispatchTime, RealMacTime, NoPlanTime, HuanMo, HuanLiao, HuanDan, JiQiGuZhang, MoJuGuZhang, FuSheGuZhang, DaiLiao, WuDingDan, QiTa, DaiRen, MacIdleTime, PermissionTime, PlanOutputQty, ActualOutputQty, PackageNum, TheoreticalQty, ProductedNum, BadNum, Flag)
select MachineNo, EmpGroupNO, EmpID, CurrDate, BCCode, SUM(StandSocketNum), SUM(SocketNum), SUM(TZ_StandEmp), SUM(ActualEmp), SUM(DispatchTime/ProductNum), SUM(RealMacTime/ProductNum), SUM(NoPlanTime/ProductNum), SUM(HuanMo/ProductNum), SUM(HuanLiao/ProductNum), SUM(HuanDan/ProductNum), SUM(JiQiGuZhang/ProductNum), SUM(MoJuGuZhang/ProductNum), SUM(FuSheGuZhang/ProductNum), SUM(DaiLiao/ProductNum), SUM(WuDingDan/ProductNum), SUM(QiTa/ProductNum), SUM(DaiRen/ProductNum), SUM(MacIdleTime/ProductNum), SUM(PermissionTime/ProductNum), SUM(PlanOutputQty), SUM(ActualOutputQty), SUM(PackageNum), SUM(TheoreticalQty), SUM(ProductedNum), SUM(BadNum),2
from #DailyTableEmp
where Flag in(0,1)
group by MachineNo,EmpGroupNO, EmpID, CurrDate, BCCode


update a set a.PlanProductTime=b.PlanProductTime from #DailyTableEmp a inner join #PlanTime b on a.MachineNo=b.MachineNo and a.BCCode=b.BCCode

update #DailyTableEmp set SocketRate=case when StandSocketNum=0 then 0 else SocketNum/(StandSocketNum*1.0) end--出数率
, BadRate=case when ProductedNum=0 then 0 else BadNum/(ProductedNum*1.0) end--不良率
, EfficiencyRate=case when TheoreticalQty=0 then 0 else ProductedNum/(TheoreticalQty*1.0) end--机台效率(有效率)
--, Availability=case when DispatchTime-NoPlanTime=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime) end--有效开机率(表现性，有效性)
, Availability=case when DispatchTime-NoPlanTime-PermissionTime-DaiRen=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime-DaiRen) end--有效开机率(表现性，有效性)
where Flag=2
update #DailyTableEmp set OEE=EfficiencyRate*Availability*(1-BadRate)--机台效率*有效开机率*良率
where Flag=2
--按机器编号层的OEE至此计算完成
--补充需求2013-05-27要求将所有派工单的产品名称用分号隔开对应到每台机器
--declare @rEmpGroupNO varchar(50), @rEmpNO varchar(50)
--declare cur_Daily cursor for
--select MachineNo, BCCode, ProductName, EmpGroupNO, EmpId from #DailyTableEmp where Flag in(0,1) and isnull(ProductName,‘‘)<>‘‘
--open cur_Daily
--fetch next from cur_Daily into @MachineNo, @BCCode, @ProductName, @rEmpGroupNO, @rEmpNO
--while @@FETCH_STATUS=0
--begin
-- print @MachineNo+ @BCCode+@ProductName
-- update #DailyTableEmp set ProductName=isnull(ProductName,‘‘)+@ProductName+‘; ‘
-- where Flag=2 and MachineNo=@MachineNo and BCCode=@BCCode and EmpGroupNO = @rEmpGroupNO and EmpID = @rEmpNO

-- fetch next from cur_Daily into @MachineNo, @BCCode, @ProductName, @rEmpGroupNO, @rEmpNO
--end
--close cur_Daily
--deallocate cur_Daily
--==================================================================================================================================

insert into #DailyTableEmp(EmpGroupNO,EmpID,CurrDate, BCCode, StandSocketNum, SocketNum, TZ_StandEmp, ActualEmp, DispatchTime, PlanProductTime, RealMacTime, NoPlanTime, HuanMo, HuanLiao, HuanDan, JiQiGuZhang, MoJuGuZhang, FuSheGuZhang, DaiLiao, WuDingDan, QiTa, DaiRen, MacIdleTime, PermissionTime, PlanOutputQty, ActualOutputQty, PackageNum, TheoreticalQty, ProductedNum, BadNum,Area, Flag)
select a.EmpGroupNO, a.EmpID, CurrDate, BCCode, SUM(StandSocketNum), SUM(SocketNum), SUM(TZ_StandEmp), SUM(ActualEmp), SUM(DispatchTime), SUM(PlanProductTime), SUM(RealMacTime), SUM(NoPlanTime), SUM(HuanMo), SUM(HuanLiao), SUM(HuanDan), SUM(JiQiGuZhang), SUM(MoJuGuZhang), SUM(FuSheGuZhang), SUM(DaiLiao), SUM(WuDingDan), SUM(QiTa), SUM(DaiRen), SUM(MacIdleTime), SUM(PermissionTime), SUM(PlanOutputQty), SUM(ActualOutputQty), SUM(PackageNum), SUM(TheoreticalQty), SUM(ProductedNum), SUM(BadNum),
‘‘, 3
from #DailyTableEmp a inner join MachineMstr b on a.MachineNo=b.Machine_Code
where Flag=2
group by a.EmpGroupNO, a.EmpID, CurrDate, BCCode


insert into #DailyTableEmp(EmpGroupNO,EmpID,CurrDate, BCCode, StandSocketNum, SocketNum, TZ_StandEmp, ActualEmp, DispatchTime, PlanProductTime, RealMacTime, NoPlanTime, HuanMo, HuanLiao, HuanDan, JiQiGuZhang, MoJuGuZhang, FuSheGuZhang, DaiLiao, WuDingDan, QiTa, DaiRen, MacIdleTime, PermissionTime, PlanOutputQty, ActualOutputQty, PackageNum, TheoreticalQty, ProductedNum, BadNum, Flag)
select ‘汇总‘,EmpID,‘‘, ‘‘, SUM(StandSocketNum), SUM(SocketNum), SUM(TZ_StandEmp), SUM(ActualEmp), SUM(DispatchTime), SUM(PlanProductTime), SUM(RealMacTime), SUM(NoPlanTime), SUM(HuanMo), SUM(HuanLiao), SUM(HuanDan), SUM(JiQiGuZhang), SUM(MoJuGuZhang), SUM(FuSheGuZhang), SUM(DaiLiao), SUM(WuDingDan), SUM(QiTa), SUM(DaiRen), SUM(MacIdleTime), SUM(PermissionTime), SUM(PlanOutputQty), SUM(ActualOutputQty), SUM(PackageNum), SUM(TheoreticalQty), SUM(ProductedNum), SUM(BadNum),3
from #DailyTableEmp a inner join MachineMstr b on a.MachineNo=b.Machine_Code
where Flag=2
group by EmpGroupNO, a.EmpID

update #DailyTableEmp set SocketRate=case when StandSocketNum=0 then 0 else SocketNum/(StandSocketNum*1.0) end--出数率
, BadRate=case when ProductedNum=0 then 0 else BadNum/(ProductedNum*1.0) end--不良率
, EfficiencyRate=case when TheoreticalQty=0 then 0 else ProductedNum/(TheoreticalQty*1.0) end--机台效率(有效率)
, Availability=case when DispatchTime-NoPlanTime-PermissionTime-DaiRen=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime-DaiRen) end--有效开机率(表现性，有效性)
--, Availability=case when PlanProductTime=0 then 0 else RealMacTime/(PlanProductTime-PermissionTime) end--有效开机率(表现性，有效性)
where Flag=3
update #DailyTableEmp set OEE=EfficiencyRate*Availability*(1-BadRate)--机台效率*有效开机率*良率
where Flag=3
--按人员层级的OEE至此计算完成

update #DailyTableEmp set StandSocketNum=null,SocketNum=null,SocketRate=null,TZ_StandEmp=null,ActualEmp=null,DispatchTime=null,
PlanProductTime=null,EfficiencyRate=null,Availability=null,BadNum=null,BadRate=null where EmpGroupNO=‘汇总‘
--==================================================================================================================================


if @GroupType=‘Detail‘
begin
select t.MachineNo,t.EmpID,CurrDate,case when t.BCCode=‘A‘ then ‘白班‘ when t.BCCode=‘B‘ then ‘晚班‘ else ‘‘ end as BCCode,DispatchNo,TZ_CustomerNo,ProductName,ProductNo,TZ_MONO,DispatchNum,StandSocketNum,SocketNum
,convert(varchar(10), CAST(SocketRate*100 as decimal(10,0)))+‘%‘ as SocketRate,StandCycle,AverageCycle
,convert(varchar(10), CAST(CycleDiffRate*100 as decimal(10,0)))+‘%‘ as CycleDiffRate,TZ_StandEmp,ActualEmp,DispatchTime, PlanProductTime,RealMacTime,NoPlanTime,HuanMo,HuanLiao,HuanDan,JiQiGuZhang,MoJuGuZhang,FuSheGuZhang,DaiLiao,WuDingDan,QiTa,DaiRen,MacIdleTime,PermissionTime,PlanOutputQty,ActualOutputQty,PackageNum,TheoreticalQty
, EfficiencyRate,Availability = isnull(Availability,0),BadNum,BadRate,OEE = ISNULL(OEE,0)
, EmpGroupName =(select top 1 EmpGroupName from MESAlarm.dbo.AlarmEmpGroup g where g.EmpGroupNo = t.EmpGroupNO)
, EmployeeName = e.EmpName
from #DailyTableEmp t
left join Kenta_EmpOEEConfig e on t.EmpID = e.EmpID
where t.Flag=2 and t.BCCode like ‘%‘+@BC+‘%‘
order by t.CurrDate, t.MachineNo, t.BCCode, t.DispatchNo
end
else if @GroupType=‘Sum‘
begin
select t.EmpGroupNO,case when t.EmpGroupNO=‘汇总‘ then ‘‘ else t.EmpID end EmpID,t.EmpID EmpID2, CurrDate, case when t.BCCode=‘A‘ then ‘白班‘ when t.BCCode=‘B‘ then ‘晚班‘ else ‘‘ end as BCCode,DispatchNo,TZ_CustomerNo,ProductName,ProductNo,TZ_MONO,DispatchNum,StandSocketNum,SocketNum
,convert(varchar(10), CAST(SocketRate*100 as decimal(10,0)))+‘%‘ as SocketRate,StandCycle,AverageCycle
,convert(varchar(10), CAST(CycleDiffRate*100 as decimal(10,0)))+‘%‘ as CycleDiffRate,TZ_StandEmp,ActualEmp,DispatchTime, PlanProductTime,RealMacTime,NoPlanTime,HuanMo,HuanLiao,HuanDan,JiQiGuZhang,MoJuGuZhang,FuSheGuZhang,DaiLiao,WuDingDan,QiTa,DaiRen,MacIdleTime,PermissionTime,PlanOutputQty,ActualOutputQty,PackageNum,TheoreticalQty
,EfficiencyRate,Availability,BadNum,case when t.EmpGroupNO=‘汇总‘ then ‘汇总‘ else convert(varchar(10),BadRate) end BadRate,OEE,
dbo.GetMachineArea(e.MachineNo) Area,case when t.EmpGroupNO=‘汇总‘ then ‘‘ else t.EmpGroupNo end EmpGroupName
, EmployeeName = case when t.EmpGroupNO=‘汇总‘ then ‘‘ else e.EmpName end
from #DailyTableEmp t
left join Kenta_EmpOEEConfig e on t.EmpID = e.EmpID
and ((t.CurrDate between e.startDate and e.EndDate) or (t.CurrDate>e.startDate and e.EndDate is null))
where t.Flag=3 and t.BCCode like ‘%‘+@BC+‘%‘
order by EmpID2, case when t.EmpGroupNO=‘汇总‘ then 2 else 1 end,t.CurrDate, t.MachineNo, BCCode, DispatchNo
end

drop table #DailyTableEmp


GO

 

表与表特定条件下的关联SQL

标签：

热心网友 时间：2022-04-07 18:39

多个条件用where……and……连接即可，举例如下：

1、创建测试表，插入数据：


create table dept (did int,dname varchar(20)) --创建dept系别表 insert into dept values (1,'计算机系')insert into dept values (2,'数学系')insert into dept values (3,'物理系') create table student(sid int,sname varchar(10),sage int,ssex varchar(4),sdept int) --创建学生表  insert into student values (1,'张三',20,'男',1)insert into student values (2,'李四',21,'男',1)insert into student values (3,'王五',19,'女',1)insert into student values (4,'赵六',21,'男',2)insert into student values (5,'孙七',18,'女',2)insert into student values (6,'杨八',21,'男',3)insert into student values (7,'刘九',20,'女',4) /*2张表通过dept表中的did和student表中的sdept字段关联*/

2、要求查询计算机系并且是女生，年龄小于20岁的人的信息。可用如下语句：


select b.* from dept a,student b where a.did=b.sdept and b.ssex='女' and b.sage<20 and a.dname='计算机系'

如图，其中红线标注的地方就是多条件连接的方法：

追问我的数据库的表已经建立了，而且和窗体进行连接了。现在想在第一个下拉框显示部位，然后第二个下拉框显示相应的特征

声明声明：本网页内容为用户发布，旨在传播知识，不代表本网认同其观点，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。E-MAIL:11247931@qq.com
如何查被录取到的专业 怎样查被录取的专业 录取后怎样查询录取的专业 录取专业怎么查 已被录取怎么查专业 ghs网络语什么意思_ghs网络语意思出处含义介绍 纸箱企业管理软件 ghs什么意思网络(ghs什么意思网络用语) 《喜羊羊与灰太狼》大结局 0与任何数相加都得原数吗? 俏魅妍六胜肽怎么样? 猴头菇包饺子 有魅妍牌子的护肤品吗? 猴头菇能做饺子馅吗 我想问下，我是还呗借的中银消费金融，自动还款失败了，现在还怎么主动还款？？ 怎么查询不动产办理受理编号 时尚优雅的小黑裙,低调温婉又魅力十足,你是否会借鉴这样的穿法? 0p8207手机突然关机开不了机 怎么用猴头菇包饺子 淡紫色的长裙,时尚优雅,高级大方,这样的穿搭你爱了吗? 怎样设置关机 诸葛青和王也最后谁死了 时尚漂亮的连衣裙,既有品位还优雅高档,你知道怎么穿搭吗? 我有一部0P手机只有指纹解锁了,没有密码该怎么取消 通过不动产登记证明号怎么查询房屋信息??? 九鼎记诸葛青最后怎么样了 请问我的0P手机掉水里了，我马上捞了出来，然后开机，摔干了，我又能 简约又不失高级的休闲风格,精致又减龄,优雅时尚的穿搭你爱了吗? 为什么不能提前还款 九鼎记诸葛青死了? 《一人之下》也结局是什么? 优雅时尚的穿搭,不仅修身显瘦还显气质,你学会了 吗? 山海易贷怎么样,山海易贷好不好 诸葛亮的后代都有谁?最后结局如何? 海晖易贷怎么样? 猴头菇白菜馅儿饺子怎么做 《一人之下(异人)》有哪些细思极恐的细节? 融360易贷哪个推广效果好 文根英 林秀晶 李妍姬的电影分别有哪些?要全哦 鲜猴头菇包饺子和包子怎么做 星姿妍口红好用吗?跟完美日记相比呢 一人之下临时工结局 猴头菇怎么料理好吃? 送李愿归盘谷序句式 《一人之下》唐妙兴双眼蓝光面带邪笑,他已经失去理智了吗? 魅芝妍注册过商标吗?还有哪些分类可以注册? 《一人之下》:双杰丁嶋安被低估的原因,他的真正实力有多强? 《一人之下》中的刮骨刀夏禾,究竟是怎样的一个人? 余玲玲的基本事迹 我吃西红柿的小说模式是什么?
懂视 51dongshi.com 版权所有
Copyright © 2019-2024