我將此查詢與連接一起使用以從一個視圖中選擇特定值：
SELECT DISTINCT
qir.[Portfolio Company Key] AS portfolio_company_id,
qir.[Quarter Date Key] AS quarter_date_id,
realized_value.Value AS realized_value,
unrealized_value.Value AS unrealized_value,
total_fair_value.Value as total_fair_value,
multiple.Value as multiple,
gross_irr_percentage.Value as gross_irr_percentage,
multiple_used_in_valuation.Value as multiple_used_in_valuation,
net_financial_debt.Value as net_financial_debt,
net_financial_debt_ebitda.Value as net_financial_debt_ebitda,
enterprise_value.Value as enterprise_value,
fund_investment_cost.Value as fund_investment_cost
FROM [dbo].[vQIRData] qir
LEFT JOIN [dbo].[vQIRData] realized_value
ON qir.[Portfolio Company Key] = realized_value.[Portfolio Company Key]
AND qir.[Quarter Date Key] = realized_value.[Quarter Date Key]
AND realized_value.[MeasureType] = 'Fair Market Valuation'
AND realized_value.[Measure Name] = 'Realized Value'
LEFT JOIN [dbo].[vQIRData] unrealized_value
ON qir.[Portfolio Company Key] = unrealized_value.[Portfolio Company Key]
AND qir.[Quarter Date Key] = unrealized_value.[Quarter Date Key]
AND unrealized_value.[MeasureType] = 'Fair Market Valuation'
AND unrealized_value.[Measure Name] = 'Unrealized Value'
LEFT JOIN [dbo].[vQIRData] total_fair_value
ON qir.[Portfolio Company Key] = total_fair_value.[Portfolio Company Key]
AND qir.[Quarter Date Key] = total_fair_value.[Quarter Date Key]
AND total_fair_value.[MeasureType] = 'Fair Market Valuation'
AND total_fair_value.[Measure Name] = 'Total Fair Value'
LEFT JOIN [dbo].[vQIRData] multiple
ON qir.[Portfolio Company Key] = multiple.[Portfolio Company Key]
AND qir.[Quarter Date Key] = multiple.[Quarter Date Key]
AND multiple.[MeasureType] = 'Fair Market Valuation'
AND multiple.[Measure Name] = 'Multiple'
LEFT JOIN [dbo].[vQIRData] gross_irr_percentage
ON qir.[Portfolio Company Key] = gross_irr_percentage.[Portfolio Company Key]
AND qir.[Quarter Date Key] = gross_irr_percentage.[Quarter Date Key]
AND gross_irr_percentage.[MeasureType] = 'Fair Market Valuation'
AND gross_irr_percentage.[Measure Name] = 'Gross IRR%'
LEFT JOIN [dbo].[vQIRData] multiple_used_in_valuation
ON qir.[Portfolio Company Key] = multiple_used_in_valuation.[Portfolio Company Key]
AND qir.[Quarter Date Key] = multiple_used_in_valuation.[Quarter Date Key]
AND multiple_used_in_valuation.[MeasureType] = 'Fair Market Valuation'
AND multiple_used_in_valuation.[Measure Name] = 'Multiple used in valuation'
LEFT JOIN [dbo].[vQIRData] net_financial_debt
ON qir.[Portfolio Company Key] = net_financial_debt.[Portfolio Company Key]
AND qir.[Quarter Date Key] = net_financial_debt.[Quarter Date Key]
AND net_financial_debt.[MeasureType] = 'Fair Market Valuation'
AND net_financial_debt.[Measure Name] = 'Net Financial Debt'
LEFT JOIN [dbo].[vQIRData] net_financial_debt_ebitda
ON qir.[Portfolio Company Key] = net_financial_debt_ebitda.[Portfolio Company Key]
AND qir.[Quarter Date Key] = net_financial_debt_ebitda.[Quarter Date Key]
AND net_financial_debt_ebitda.[MeasureType] = 'Fair Market Valuation'
AND net_financial_debt_ebitda.[Measure Name] = 'Net Financial Debt / EBITDA'
LEFT JOIN [dbo].[vQIRData] enterprise_value
ON qir.[Portfolio Company Key] = enterprise_value.[Portfolio Company Key]
AND qir.[Quarter Date Key] = enterprise_value.[Quarter Date Key]
AND enterprise_value.[MeasureType] = 'Fair Market Valuation'
AND enterprise_value.[Measure Name] = 'EV'
LEFT JOIN [dbo].[vQIRData] fund_investment_cost
ON qir.[Portfolio Company Key] = fund_investment_cost.[Portfolio Company Key]
AND qir.[Quarter Date Key] = fund_investment_cost.[Quarter Date Key]
AND fund_investment_cost.[MeasureType] = 'Fair Market Valuation'
AND fund_investment_cost.[Measure Name] = 'Fund Investment Cost'
WHERE qir.[MeasureType] = 'Fair Market Valuation'
ORDER BY portfolio_company_id
一切都是從一個視圖中選擇的。我正在創建具有不同命名的新列。我無權訪問該視圖。我只能從那里選擇值。此查詢正在正確選擇值，但我想對其進行優化，使其運行得更快。
有沒有辦法優化它的執行時間？
uj5u.com熱心網友回復：
只需使用PIVOT...
SELECT
pvt.*
FROM
(
SELECT
[Portfolio Company Key] AS portfolio_company_id,
[Quarter Date Key] AS quarter_date_id,
[Measure Name],
[Value]
FROM
[dbo].[vQIRData]
WHERE
[MeasureType] = 'Fair Market Valuation'
)
AS qir
PIVOT
(
MAX([Value]) FOR [Measure Name] IN (
[Realized Value],
[Unrealized Value],
[Total Fair Value],
etc,
etc
)
)
AS pvt
ORDER BY
pvt.portfolio_company_id
uj5u.com熱心網友回復：
修復@Alexy的回答，因為他似乎拒絕這樣做......
SELECT
[Portfolio Company Key] AS portfolio_company_id,
[Quarter Date Key] AS quarter_date_id,
MAX(IIF([Measure Name] = 'Realized Value', Value, NULL)) AS realized_value,
MAX(IIF([Measure Name] = 'Unrealized Value',
Value, NULL)) AS unrealized_value,
MAX(IIF([Measure Name] = 'Total Fair Value', Value, NULL)) as total_fair_value,
MAX(IIF([Measure Name] = 'Multiple', Value, NULL)) as multiple,
MAX(IIF([Measure Name] = 'Gross IRR%', Value, NULL)) as gross_irr_percentage,
MAX(IIF([Measure Name] = 'Multiple used in valuation', Value, NULL)) as multiple_used_in_valuation,
MAX(IIF([Measure Name] = 'Net Financial Debt', Value, NULL)) as net_financial_debt,
MAX(IIF([Measure Name] = 'Net Financial Debt / EBITDA', Value, NULL)) as net_financial_debt_ebitda,
MAX(IIF([Measure Name] = 'EV', Value, NULL)) as enterprise_value,
MAX(IIF([Measure Name] = 'Fund Investment Cost', Value, NULL)) as fund_investment_cost
FROM
[dbo].[vQIRData]
WHERE
[MeasureType] = 'Fair Market Valuation'
GROUP BY
[Portfolio Company Key],
[Quarter Date Key]
ORDER BY
portfolio_company_id
筆記; DISTINCT被洗掉，并且邏輯在功能上與 using 相同PIVOT，但重復性略高，同時對于更復雜的情況通常也更靈活。
uj5u.com熱心網友回復：
您根本不需要JOIN，因為您按單個條件過濾視圖并根據列值WHERE qir.[MeasureType] = 'Fair Market Valuation'檢索具有不同名稱的相同列 ( )值。所以你可以使用簡單的條件，比如：ValueMeasure Name
SELECT DISTINCT
[Portfolio Company Key] AS portfolio_company_id,
[Quarter Date Key] AS quarter_date_id,
MAX(IIF([Measure Name] = 'Realized Value', Value, NULL)) AS realized_value,
MAX(IIF([Measure Name] = 'Unrealized Value', Value, NULL)) AS unrealized_value,
MAX(IIF([Measure Name] = 'Total Fair Value', Value, NULL)) as total_fair_value,
MAX(IIF([Measure Name] = 'Multiple', Value, NULL)) as multiple,
MAX(IIF([Measure Name] = 'Gross IRR%', Value, NULL)) as gross_irr_percentage,
MAX(IIF([Measure Name] = 'Multiple used in valuation', Value, NULL)) as multiple_used_in_valuation,
MAX(IIF([Measure Name] = 'Net Financial Debt', Value, NULL)) as net_financial_debt,
MAX(IIF([Measure Name] = 'Net Financial Debt / EBITDA', Value, NULL)) as net_financial_debt_ebitda,
MAX(IIF([Measure Name] = 'EV', Value, NULL)) as enterprise_value,
MAX(IIF([Measure Name] = 'Fund Investment Cost', Value, NULL)) as fund_investment_cost
FROM [dbo].[vQIRData]
WHERE [MeasureType] = 'Fair Market Valuation'
GROUP BY [Portfolio Company Key], [Quarter Date Key]
ORDER BY portfolio_company_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/486975.html