优化具有内部查询的 sql 查询

Question

我真的很抱歉，但刚刚开始研究 SQL，我之前在 SQL 上做过一些工作，但现在只是更新、删除和插入，我确实需要优化以下查询，7 个项目需要 7 秒。

一个表包含行项目，另一个表包含供应商报价，还有一个仓库信息表。

我确实需要显示具有最佳 3 个供应商报价的行项目(报价 1- 报价 1 名称 - 报价 1 评论(以及仓库信息

您能否帮助我，并教我一种快速查询的方法。

谢谢

declare @usd nvarchar(10)
declare @gbp nvarchar(10)
declare @euro nvarchar(10)
set @usd=(select top 1 KUR from _APPA_WEB_KUR WHERE CURRENCY = 'USD'  ORDER BY INCKEYNO DESC)
set @gbp=(select top 1 KUR from _APPA_WEB_KUR WHERE CURRENCY = 'GBP'  ORDER BY INCKEYNO DESC)
set @euro=(select top 1 KUR from _APPA_WEB_KUR WHERE CURRENCY = 'EURO'  ORDER BY INCKEYNO DESC)
SELECT   
    item.INCKEYNO, COMMENT, item.SIRA, item.[FILE_NUMBER], [REFERANCE_NO], 
    [GROUP_ID], [IMPA_CODE], [DESCRIPTION], [UNIT], [QTTY], 
    item.[CREATED_BY], item.[DATE], removed,
    (SELECT TOP 1 (isnull
     ((SELECT   cast(sum(STHAR_GCMIK) AS int)FROM   TBLSTHAR
    WHERE   STHAR_GCKOD = 'G' AND TBLSTHAR.STOK_KODU LIKE ('%' + item.IMPA_CODE + '%') AND sthar_htur != 'L' AND SUBE_KODU = '4'), 0) - isnull
    ((SELECT   cast(sum(STHAR_GCMIK) AS int)
    FROM   TBLSTHAR
  WHERE   STHAR_GCKOD = 'C' AND TBLSTHAR.STOK_KODU LIKE ('%' + item.IMPA_CODE + '%') AND sthar_htur != 'L' AND SUBE_KODU = '4'), 0)) AS bakiye
    FROM   TBLSTHAR) AS depo4,
      (SELECT   TOP 1 (isnull
     ((SELECT   cast(sum(STHAR_GCMIK) AS int)
 FROM  TBLSTHAR  WHERE   STHAR_GCKOD = 'G' AND TBLSTHAR.STOK_KODU LIKE ('%' + item.IMPA_CODE + '%') AND sthar_htur != 'L' AND SUBE_KODU = '2'), 0) 
                                 - isnull ((SELECT   cast(sum(STHAR_GCMIK) AS int)  FROM    TBLSTHAR WHERE    STHAR_GCKOD = 'C' AND 
                                TBLSTHAR.STOK_KODU LIKE ('%' + item.IMPA_CODE + '%') AND sthar_htur != 'L' AND SUBE_KODU = '2'), 0)) AS bakiye
    FROM   TBLSTHAR) AS depo2,
      (SELECT   TOP 1 ([SECILI_FIYAT])
    FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
    WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO
    ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS OFFER1,
      (SELECT   TOP 1 SUPPLIER_ID
    FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
    WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO
    ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS order1_supplier,
      (SELECT   TOP 1 COMMENT
    FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
    WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO
    ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS order1_comment,
      (SELECT   dbo.trk(cari_isim)
    FROM   TBLCASABIT
    WHERE   CARI_KOD = (SELECT   SUPPLIER_ID
    FROM   (SELECT   SUPPLIER_ID, PRICE, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
    FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
    WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
    WHERE   DT.Salary_Order = 1)) AS offer1cari,
    (SELECT   [SECILI_FIYAT]
 FROM   (SELECT   SECILI_FIYAT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
 FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
 WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE   DT.Salary_Order = 2) AS OFFER2,
    (SELECT   SUPPLIER_ID
 FROM   (SELECT   SUPPLIER_ID, SECILI_FIYAT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
 FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
 WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE   DT.Salary_Order = 2) AS order2_supplier,
    (SELECT   dbo.trk(cari_isim)
 FROM   TBLCASABIT
 WHERE   CARI_KOD =(SELECT   SUPPLIER_ID
      FROM   (SELECT   SUPPLIER_ID, PRICE, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
      FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
      WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
 WHERE   DT.Salary_Order = 2)) AS offer2cari,
    (SELECT   COMMENT
 FROM   (SELECT   COMMENT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
 FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
 WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE   DT.Salary_Order = 2) AS order2_comment,
    (SELECT   [SECILI_FIYAT]
 FROM   (SELECT   [SECILI_FIYAT], vat, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
 FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
 WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE   DT.Salary_Order = 3) AS OFFER3,
    (SELECT   SUPPLIER_ID
 FROM   (SELECT   SUPPLIER_ID, SECILI_FIYAT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
 FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
 WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE   DT.Salary_Order = 3) AS order3_supplier,
    (SELECT   dbo.trk(cari_isim)
 FROM   TBLCASABIT
 WHERE   CARI_KOD =(SELECT   SUPPLIER_ID
      FROM   (SELECT   SUPPLIER_ID, SECILI_FIYAT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
      FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
      WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
 WHERE   DT.Salary_Order = 3)) AS offer3cari,
    (SELECT   COMMENT
 FROM   (SELECT   COMMENT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
 FROM   [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
 WHERE   FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE   DT.Salary_Order = 3) AS order3_comment, @euro AS EURO, @gbp AS GBP, @usd AS USD, mas.CURRENCY, 
'0' depo4_fiyat,'0' depo2_fiyat, item.price, item.supplier
FROM   [dbo].[_APPA_WEB_PURCHASE_OFFER_LINE_ITEMS_2] item   LEFT OUTER JOIN
  dbo._APPA_WEB_PURCHASE_OFFER_MASTER AS mas ON mas.FILE_NUMBER = item.FILE_NUMBER 
WHERE    (item.removed != 1 OR
  item.removed IS NULL)

Answer 1

你的代码很难阅读。我尽力准确地重构，但我没有任何办法检查。所以这里是：

;WITH line_items AS (
      -- Read all the lines that we will need
       SELECT *
       FROM [dbo].[_APPA_WEB_PURCHASE_OFFER_LINE_ITEMS_2] item
       WHERE COALESCE(item.removed,0) != 1
),
suppliers AS (
      -- Get a ranked list of supplier, offers and comments
       SELECT v.COMMENT,
            v.SUPPLIER_ID,
            v.[SECILI_FIYAT],
            ROW_NUMBER() OVER(ORDER BY CAST(isnull(v.SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order,
            v.FILE_NUMBER,
            v.IMPA_CODE,
            v.REFERANCE_NO,
            dbo.trk(b.cari_isim) AS cari
       FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW] v
          INNER JOIN line_items item
             ON v.FILE_NUMBER = item.file_number
               AND v.IMPA_CODE = item.IMPA_CODE
               AND v.REFERANCE_NO = item.REFERANCE_NO
          INNER JOIN TBLCASABIT b
             ON b.CARI_KOD = v.SUPPLIER_ID
       WHERE v.PRICE IS NOT NULL
),
 t_STHAR AS (
       SELECT     
            CAST(    ISNULL(SUM(CASE WHEN st.SUBE_KODU = '2' AND st.STHAR_GCKOD = 'G' THEN st.STHAR_GCMIK ELSE 0 END),0) - 
                    ISNULL(SUM(CASE WHEN st.SUBE_KODU = '2' AND st.STHAR_GCKOD = 'C' THEN st.STHAR_GCMIK ELSE 0 END),0)  AS INT) AS depo2,
            CAST(    ISNULL(SUM(CASE WHEN st.SUBE_KODU = '4' AND st.STHAR_GCKOD = 'G' THEN st.STHAR_GCMIK ELSE 0 END),0) - 
                    ISNULL(SUM(CASE WHEN st.SUBE_KODU = '4' AND st.STHAR_GCKOD = 'C' THEN st.STHAR_GCMIK ELSE 0 END),0)  AS INT) AS depo4
       FROM TBLSTHAR
       WHERE STHAR_HTUR != 'L'
       AND EXISTS (
          SELECT 1
          FROM line_items
          WHERE STOK_KODU LIKE('%'+item.IMPA_CODE+'%')
       )
)
SELECT item.INCKEYNO,
       COMMENT,
       item.SIRA,
       item.[FILE_NUMBER],
       [REFERANCE_NO],
       [GROUP_ID],
       [IMPA_CODE],
       [DESCRIPTION],
       [UNIT],
       [QTTY],
       item.[CREATED_BY],
       item.[DATE],
       removed,
      st.depo2,
      st.depo4,
      -- Use (JOIN, MAX, GROUP BY) method to transpose rows to columns
      MAX(CASE WHEN s.Salary_Order = 1 THEN s.[SECILI_FIYAT] END) AS OFFER1,
      MAX(CASE WHEN s.Salary_Order = 1 THEN s.SUPPLIER_ID END) AS order1_supplier,
      MAX(CASE WHEN s.Salary_Order = 1 THEN s.cari END) AS offer1cari,
      MAX(CASE WHEN s.Salary_Order = 1 THEN s.COMMENT END) AS order1_comment,
      MAX(CASE WHEN s.Salary_Order = 2 THEN s.[SECILI_FIYAT] END) AS OFFER1,
      MAX(CASE WHEN s.Salary_Order = 2 THEN s.SUPPLIER_ID END) AS order2_supplier,
      MAX(CASE WHEN s.Salary_Order = 2 THEN s.cari END) AS offer2cari,
      MAX(CASE WHEN s.Salary_Order = 2 THEN s.COMMENT END) AS order2_comment,
      MAX(CASE WHEN s.Salary_Order = 3 THEN s.[SECILI_FIYAT] END) AS OFFER3,
      MAX(CASE WHEN s.Salary_Order = 3 THEN s.SUPPLIER_ID END) AS order3_supplier,
      MAX(CASE WHEN s.Salary_Order = 3 THEN s.cari END) AS offer3cari, ---
      MAX(CASE WHEN s.Salary_Order = 3 THEN s.COMMENT END) AS order3_comment,
       @euro AS EURO,
       @gbp AS GBP,
       @usd AS USD,
       mas.CURRENCY,
       '0' depo4_fiyat,
       '0' depo2_fiyat,
       item.price,
       item.supplier
FROM line_items item
    LEFT OUTER JOIN dbo._APPA_WEB_PURCHASE_OFFER_MASTER mas
       ON mas.FILE_NUMBER = item.FILE_NUMBER
    LEFT JOIN suppliers s
       ON item.FILE_NUMBER = s.FILE_NUMBER
       AND item.IMPA_CODE = s.IMPA_CODE
       AND item.REFERANCE_NO = s.REFERANCE_NO
    CROSS APPLY t_STHAR st
GROUP BY item.INCKEYNO,
       COMMENT,
       item.SIRA,
       item.[FILE_NUMBER],
       [REFERANCE_NO],
       [GROUP_ID],
       [IMPA_CODE],
       [DESCRIPTION],
       [UNIT],
       [QTTY],
       item.[CREATED_BY],
       item.[DATE],
       removed,
      st.depo2,
      st.depo4,
       mas.CURRENCY,
       item.price,
       item.supplier
;

Answer 2

我确实有一个包含供应商报价的表格

supplier_id ， file_no ， referance_no， item_id，评论

我有一个包含行项目信息的主表;

item_id，file_no，referance_no...等

我确实需要获得最佳 3 个供应商报价(按价格排序(和供应商信息supplier_id、价格、评论，我确实需要将这 9 个单元格添加到我的主表中

我想看到我的观点，比如;

item_id, file_no, referance_no ...+ 
offer1_price, offer1_comment, offer1_supplier_id,
offer2_price, offer2_comment, offer2_supplier_id,
offer3_price, offer3_comment, offer3_supplier_id,

钥匙是item_id，file_no和referance_no