记录一次sql中，查询三表管理，最后一张表是否存在记录

最新推荐文章于 2023-05-16 10:18:04 发布

ArcherSYM

最新推荐文章于 2023-05-16 10:18:04 发布

阅读量160

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/ArcherSYM/article/details/108279897

版权

sql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

SELECT
t.F_ID,
t.F_DocumentNo,
t.F_GenerationDate,
t.F_GenerationMan,
t.F_InquiryDepartment,
t.F_ClosingDate,
t.F_ModeOfDelivery,
t.F_PaymentMethod,
t.F_TaxRate,
t.F_ViewQuote,
t.F_QuotationAuthority,
t.F_Supplier,
t.F_Freight,
t.F_DeliveryAddress,
t.F_Enclosure,
t.F_Remarks,
t.F_State,
t.F_CurrentState
FROM BMS_Base_PurchaseInquiry t
               WHERE 1=1 AND t.F_CurrentState = '发布' AND ( t.F_QuotationAuthority='公开' OR t.F_Supplier like '%System%' )
               AND t.F_ID in ( select a.F_PurchaseInquiryID from BMS_Base_PurchaseInquiryDetails a
               where a.F_ID not in ( select b.F_SubTableID from BMS_Base_PriceList b where b.F_UserId='System' ) )

统计第三张表的记录量

   SELECT
t.F_ID,
t.F_DocumentNo,
t.F_GenerationDate,
t.F_GenerationMan,
t.F_InquiryDepartment,
t.F_ClosingDate,
t.F_ModeOfDelivery,
t.F_PaymentMethod,
t.F_TaxRate,
t.F_ViewQuote,
t.F_QuotationAuthority,
t.F_Supplier,
t.F_Freight,
t.F_DeliveryAddress,
t.F_Enclosure,
t.F_Remarks,
t.F_State,
t.F_CurrentState,
               isnull(m.number,0) as F_PriceState
FROM BMS_Base_PurchaseInquiry t
               left join
               (   select a.F_ID ,count(c.F_SubTableID) as number
               FROM BMS_Base_PurchaseInquiry a
               left join BMS_Base_PurchaseInquiryDetails b
               on b.F_PurchaseInquiryID=a.F_ID
               left join BMS_Base_PriceList c
               on c.F_SubTableID=b.F_ID
               where c.F_UserId='System'
               group by a.F_ID ) m
               on m.F_ID=t.F_ID
               WHERE 1=1 AND t.F_CurrentState = '发布' AND ( t.F_QuotationAuthority='公开' OR t.F_Supplier like '%System%' )