SQL查询的效率 [英] efficiency of the SQL query

问题描述

如何解决这种情况的最佳方法是什么？

假设你有一个表属性列

物业ID，物业描述，物业类型，物业类型相关ID ....

如果物业类型为销售，则物业类型相关ID来自表格供销售

如果物业类型为租赁物业类型相关ID从桌子出租

what is the best way how to solve this situation?
Let''s say you have a table Property with columns
Property ID, Property Description, Property Type, Property Type Related ID....
If Property Type is Selling then Property Type Related ID is from table for selling
If Property Type is Renting then Property Type Related ID is from table for renting

SELECT
propertyID, 
propertyDescription, 
(CASE WHEN relatedSelling.selling ID IS NOT NULL THEN relatedCOl1
     WHEN relatedRenting.selling ID IS NOT NULL THEN relatedCOl1
END) as RelCol1,
(CASE WHEN relatedSelling.selling ID IS NOT NULL THEN relatedCOl2
     WHEN relatedRenting.selling ID IS NOT NULL THEN relatedCOl2
END) as RelCol2,

.....有没有办法如何获取这些数据而不是每列都有案例？我需要从这些相关表格中选择大约15列（或与这些相关的其他表格）

..... is there a way how to get this data and not having case for each column? there is about 15 columns which I need to have in a select from those related tables(or other tables joined with these related)

FROM Property
LEFT JOIN ON selling WHERE property. propertyTypeRelatedID = selling ID and property.propertyType = ''Selling'' as relatedSelling
LEFT JOIN ON renting WHERE property. propertyTypeRelatedID = renting ID and property.propertyType = ''Renting'' as relatedRenting

推荐答案

使用 union all ：

Use union all :

select col1,...,colN from table1 left join ..sellingtable.. where ...  
union all
select col1,...,colN from table1 left join ..rentingtable.. where ...

你需要为此做动态查询....



检查以下链接...



在存储过程中构建动态SQL [ ^ ]

在SQL Server中执行动态SQL命令 [ ^ ]





问候，

GVPrabu

Hi,

U need to do Dynamic Query for this....

Check the following Links...

Building Dynamic SQL In a Stored Procedure[^]
Execute Dynamic SQL commands in SQL Server[^]


Regards,
GVPrabu

这篇关于SQL查询的效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！