22,209
社区成员
发帖
与我相关
我的任务
分享
select distinct a.Com_ID,a.Com_Name,a.Com_Delegate,a.Com_ComCode,a.Pro_Receipts,
Com_IsCetifYezz,Com_IsCetifYzz,Com_IsCetifWghcd,Com_IsCetifghcp,Com_IsCetifCk,Com_IsCetifZm,
Com_IsCetifHaccp,Com_IsCetifIso,Com_IsCetifGreen,Com_ShowOrder,
s1.V_Text As Com_QYXZ, --企业性质
s2.V_Text As Com_AuditorStatus --核查状态
From Wat_ComInfo a
Left Join sys_FieldValue s1 on s1.V_Code = a.Com_QYXZ_Dic and s1.V_F_Key = 'ComQYXZKey' --企业性质
Left Join sys_FieldValue s2 on s2.V_Code = a.Com_AuditorStatus_Dic and s2.V_F_Key = 'ComAuditorStatusKey' --核查状态
Left Join Wat_FarmCertificate f on f.Com_ID = a.Com_ID --养殖证
Left Join Wat_ProComRelation c on c.Com_ID = a.Com_ID --产品与企业关系表
left join Wat_ProductInfo d on d.Pro_ID=c.Pro_ID
Where Com_NewOldStatus_Dic='1'
and Com_AreaCode in(
select f.AreaCode from Wat_OrgRole f
join Wat_OrgInfo e on e.Org_ID=f.Org_ID
join org_user g on e.Org_Name=g.U_UnitName
where g.UserID='1')
SELECT DISTINCT
a.Com_ID ,
a.Com_Name ,
a.Com_Delegate ,
a.Com_ComCode ,
a.Pro_Receipts ,
Com_IsCetifYezz ,
Com_IsCetifYzz ,
Com_IsCetifWghcd ,
Com_IsCetifghcp , --请说明这个字段属于哪张表.比如 a.Com_IsCetifghcp?
Com_IsCetifCk ,
Com_IsCetifZm ,
Com_IsCetifHaccp ,
Com_IsCetifIso ,
Com_IsCetifGreen ,
Com_ShowOrder ,
s1.V_Text AS Com_QYXZ , --企业性质
s2.V_Text AS Com_AuditorStatus --核查状态
FROM Wat_ComInfo a
LEFT JOIN sys_FieldValue s1
ON s1.V_Code = a.Com_QYXZ_Dic
AND s1.V_F_Key = 'ComQYXZKey' --企业性质
LEFT JOIN sys_FieldValue s2
ON s2.V_Code = a.Com_AuditorStatus_Dic
AND s2.V_F_Key = 'ComAuditorStatusKey' --核查状态
LEFT JOIN Wat_FarmCertificate f
ON f.Com_ID = a.Com_ID --养殖证,请说出表中的数据量?
LEFT JOIN Wat_ProComRelation c
ON c.Com_ID = a.Com_ID --产品与企业关系表
LEFT JOIN Wat_ProductInfo d
ON d.Pro_ID = c.Pro_ID
WHERE Com_NewOldStatus_Dic = '1'
AND Com_AreaCode IN --请说明这个字段属于哪张表.比如 a.Com_AreaCode?
(
SELECT f.AreaCode
FROM Wat_OrgRole f
JOIN Wat_OrgInfo e
ON e.Org_ID = f.Org_ID
JOIN org_user g
ON e.Org_Name = g.U_UnitName
WHERE g.UserID = '1'
)