AtraQ查询语句

 1.

select t_operat.et_stocpf,t_operat.operat_nscomp,t_operat.nommodif,t_operat.datemodif,LEFT(t_operat.heuremodif,6) AS timeHms,t_operat.operat_comp_no_passage,et_refcmp,t_lotuse.ET_REF_COMP,t_lotuse.ET_LOTCMP_NOLOT,t_lotuse.et_lotcmp_no_etiquette,t_modeop.et_prod_poste,modeop_libelle,et_refakf_comp,qualpf_libelle,CNTNER_NUMCONT
from t_operat,t_stocpf,t_simo,t_modeop,t_nomen,t_qualpf,t_cntner,t_packpf,t_lotuse
where t_operat.et_stocpf in(SELECT id_stocpf from t_stocpf where datemodif='20230314' and et_refcmp='661380800A')
and t_stocpf.id_stocpf=t_operat.et_stocpf
and t_operat.et_simo=t_simo.id_simo
and t_simo.et_modeop=t_modeop.id_modeop
and t_simo.et_nomen=t_nomen.id_nomen
and t_stocpf.et_qualpf=t_qualpf.id_qualpf
and t_packpf.et_stocpf=t_operat.et_stocpf
and t_packpf.et_cntner=t_cntner.id_cntner
and t_stocpf.id_stocpf=t_lotuse.et_stocpf
order by datemodif,timeHms

增加了零件，但是同一个内部号零件号会重复，需优化

SELECT

    t_operat.et_stocpf,

    t_operat.operat_nscomp,

    t_operat.nommodif,

    t_operat.datemodif,

    LEFT(t_operat.heuremodif, 6) AS timeHms,

    t_operat.operat_comp_no_passage,

    t_stocpf.et_refcmp,

    t_lotuse.ET_REF_COMP,

    t_lotuse.ET_LOTCMP_NOLOT,

    t_lotuse.et_lotcmp_no_etiquette,

    t_modeop.et_prod_poste,

    t_modeop.modeop_libelle,

    t_nomen.et_refakf_comp,

    t_qualpf.qualpf_libelle,

    t_cntner.CNTNER_NUMCONT

FROM t_operat

JOIN t_stocpf ON t_stocpf.id_stocpf = t_operat.et_stocpf

    AND t_stocpf.et_refcmp = '663748900A' AND t_stocpf.datemodif > '20250807'

JOIN t_simo ON t_operat.et_simo = t_simo.id_simo

JOIN t_modeop ON t_simo.et_modeop = t_modeop.id_modeop

JOIN t_nomen ON t_simo.et_nomen = t_nomen.id_nomen

JOIN t_qualpf ON t_stocpf.et_qualpf = t_qualpf.id_qualpf

JOIN t_packpf ON t_packpf.et_stocpf = t_operat.et_stocpf

JOIN t_cntner ON t_packpf.et_cntner = t_cntner.id_cntner

JOIN t_lotuse ON t_stocpf.id_stocpf = t_lotuse.et_stocpf

ORDER BY t_operat.datemodif, timeHms;

 

以上查询，IN 子句的子查询可以被改写成 JOIN。这通常能让 SQL Server 更好地执行查询计划。

排列了 JOIN 顺序，并直接在 t_stocpf 的 ON 条件中加入了筛选条件。这样 SQL Server 可以在进行连接时就对数据进行过滤，而不是先连接所有数据再过滤。

2. 3个月生产参数查询

select t_stocpf.et_refcmp,t_carath.carath_libelle,CARTRA_VALEUR,t_carath.et_unite,t_cartra.DATEMODIF from t_cartra,t_stocpf,t_carath where t_cartra.NOMMODIF='PRODUCTION2017'
and t_cartra.DATEMODIF BETWEEN '20230622' and '20230822'
and t_stocpf.id_stocpf=t_cartra.et_stocpf
and t_carath.id_carath=t_cartra.ET_CARATH

3.总成件号查询

select et_refakf_se,datemodif from t_nomen where et_typtra='U' and et_refakf_comp is null and et_refakf_se not like 'P%'

and et_refakf_se not like 'L%'
and et_refakf_se not like 'T%'
and et_refakf_se not like 'X%'
and et_refakf_se not like '%TG%'
and et_refakf_se not like '%SA%'
and et_refakf_se not like '%SP'
and et_refakf_se not like '%TEST'
and et_refakf_se not like 'RE%'
and et_refakf_se not like '%GP%'
and et_refakf_se not like '0%'
and et_refakf_se not like '1P%'
and et_refakf_se like '6%'
and et_refakf_se not like '%SUV%'
and nomen_fin_validite is null order by datemodif

3.根据family name查该family下所有件号

select id_refcmp,refcmp_nom,et_refakf,et_fampf,fampf_nom from t_refcmp, t_fampf where t_refcmp.et_fampf=t_fampf.id_fampf
and t_fampf.fampf_nom in('U1','U4','U6','U7','TG')

4.根据件号查询其所在的family

select id_refcmp,et_fampf,fampf_nom,fampf_libelle from t_refcmp,t_fampf where id_refcmp in(
'665585400A','635823400D','642191100A','650019700AH0','650019700A','652509800A','646830800A','643403700B','655315900A','656302100A','646830800AE0','656253400A'
) and t_refcmp.et_fampf=t_fampf.id_fampf

5.查VW标签更改记录

select * from t_cliref where et_carcli in(100,514) and et_refcmp in
(
select et_refakf from t_refcmp, t_fampf where t_refcmp.et_fampf=t_fampf.id_fampf
and t_fampf.fampf_nom in('U1','U4','U6','U7','TG') and et_refakf not like '6%'
) and datemodif='20231208' and cliref_fin_validite is null

6.查询某一天某产线所有产品按时间生产记录，时间转化为标准格式，记录批次信息以便于分析换型

select id_stocpf,et_refcmp,refcmp_nom,concat(
left(st.heuremodif, 2), ':',
substring(st.heuremodif, 3, 2), ':',
substring(st.heuremodif, 5, 2)
) AS timeHms,
et_lotpf,qualpf_libelle from t_stocpf st,t_qualpf q,t_refcmp cmp where st.et_qualpf=q.id_qualpf and
st.et_refcmp=cmp.id_refcmp and st.datemodif='20240725' order by st.heuremodif

7.查找某件号某天的保存过程参数

select '655455300B' as Part_number, et_stocpf,cartra_valeur,t_carath.et_unite,t_carath.carath_libelle,t_cartra.datemodif,LEFT(t_cartra.heuremodif,6) AS TimeHMS from t_cartra,t_carath
where et_stocpf in(select ID_STOCPF from t_stocpf where et_refcmp='655455300B' and stocpf_line='3117' and DATEMODIF='20241009')
and t_cartra.et_carath=t_carath.id_carath

或

select t_stocpf.et_refcmp,et_stocpf,cartra_valeur,t_carath.et_unite,t_carath.carath_libelle,t_cartra.datemodif,LEFT(t_cartra.heuremodif,6) AS TimeHMS from t_stocpf,t_cartra,t_carath
where et_stocpf in(select ID_STOCPF from t_stocpf where stocpf_line='5020' and DATEMODIF='20241031')
and t_cartra.et_carath=t_carath.id_carath
and carath_libelle like 'st05-2%'
and t_stocpf.id_stocpf=t_cartra.et_stocpf
order by ET_STOCPF

8.查询interval涉及的件号，规则，线号及范围

select id_refcmp,refcmp_nom,t_refcmp.et_fampf,fabricable,fampf_nom,t_tranch.et_prod,tranch_no_start,tranch_taille,cliref_valeur from t_refcmp,t_fampf,t_tranch,t_cliref

where t_refcmp.et_fampf in

(

select et_fampf from t_tranch where et_prod='9981'

) and t_refcmp.et_fampf=t_fampf.id_fampf

and t_refcmp.et_fampf=t_tranch.et_fampf

and t_refcmp.id_refcmp=t_cliref.et_refcmp

and t_cliref.et_carcli=100

and t_cliref.cliref_fin_validite is null