SQL扁平化& 动态查询问题

Question

首先，我不确定这是否是处理此问题的最佳方法……完全接受替代解决方案。

其次，我觉得我错过了明显的......但我仍然错过它，所以不要骄傲地问！

更新：.NET 3.5环境与SQL 2005，所以像动态linq这样的事情是可能的，尽管我总是倾向于认为任何类型的动态（动态构建）查询都有点笨拙。 PITA 维持。

更新2：针对northpole，伪代码/书面文字逻辑/sql/linq/C#都可以接受（！）...更多的是概念上的“什么是好的方法”而不是我需要的代码类似的问题。

给定一个看起来像这样的“鞋子”表：

  ShoeID   PropertyName    PropertyValue
  1        COLOR           RED   
  2        COLOR           RED   
  2        SIZE            11
  3        COLOR           RED   
  3        SIZE            11   
  3        MANUFACTURER    GUCCI

我需要一种方法来查询鞋子，使得

COLOR=RED 返回

  1
  2
  3

COLOR=RED，SIZE=11 返回

  2
  3

COLOR=RED 和 SIZE=11，并且 MANUFACTURER=GUCCI 返回

  3

At设计时，我不知道可能有多少个不同的属性，也不知道可能有多少个查询参数......

希望这是有道理的......如果没有，请相应地评论我会再试一次。

Answer 1

因此，这是否是最好的方法取决于很多因素。 例如，您是否需要支持可能具有不同（不兼容）属性的不同类别的实体（例如鞋子与连衣裙）？ 或者您预计将拥有的实体数量是多少（对于 10K 来说相当有效的实体对于 100M 来说就不起作用）？ 或者您需要多久处理一次此类查询以及它们需要执行得如何？

对此最常见的两种思想流派是 EAV 模型，该模型或多或少减少您拥有的内容和基于列的方法，其中实体的属性（颜色、大小、等等...）每个都映射到一个单独的列。 每种方法都有其优点和缺点，其中最大的是前者的灵活性/性能以及后者动态更改表结构的必要性。

如果您确实使用现有模型，我建议将您的属性名称移至单独的表中，并更改“鞋子”表以将 FK 移至该表。 然后，您可以在 (property_id, shoe_id) 上创建索引并生成查询，如下所示：

SELECT shoe_id FROM shoes S_1 [, shoes S_2, ..., shoes S_X]
 WHERE S_1.property_id = 3 /* FK for 'color' */
  /* the following 3 lines will be repeated for each 'property' you need to query on */
   AND S_X.property_id = 4 /* FK for 'size' */
   AND S_X.shoe_id = S_1.shoe_id
   AND S_X.property_value = 'RED'

只要您有更多信息，该查询应该可以很好地执行合理或者属性分布不太均匀并且鞋子数量不是巨大。

Answer 2

如果我理解正确的话，您希望能够创建一个查询，该查询可以具有基于任意数量字段的条件，而这些字段是预先未知的。 这很难……

虽然大多数 RDBMS 确实提供某种“动态 SQL”功能，但这些方法往往要么使用起来很麻烦，要么很慢，或者两者兼而有之。

当然，您也可以在客户端代码中将 SQL 语句连接在一起，无论是 C#、Java、PHP 还是其他语言 - 但这同样会很麻烦，容易受到 SQL 注入攻击，而且通常相当笨拙。

此外，如果您的查询与一个请求不同，则 RDBMS 将无法缓存您的任何查询计划，并且正确建立索引以获得良好的查询性能往好里说是具有挑战性，往坏了说是不可能的。

因此，虽然我完全理解这个要求（并且几乎每天都必须与自己抗争），但它确实效果不佳。 我宁愿尝试确定最常用的搜索及其标准，并确保它们工作正常且快速。 限制用户的灵活性以获得不错的查询性能。 一个经典的权衡——你可以完全灵活，但性能却很糟糕，或者你可以让频繁的查询运行得很快——但失去一些灵活性。

您可能会有所收获的一个领域是在文本字段中搜索自由文本时 - SQL Server 等 RDBMS 支持全文搜索的概念，这为您提供了一定的灵活性和良好的性能。 如果您有很多文本字段，请检查一下。

马克

Answer 3

动态sql会帮助你。 比如：

ALTER PROCEDURE [dbo].[sp_GetFilteredRecords]
(
...
@ConditionList nvarchar(MAX) = null
)
AS
BEGIN
  DECLARE @sql        nvarchar(MAX), 
          @paramlist  nvarchar(MAX)

  SELECT @sql = 'select ... '
  ...
  SELECT @sql = @sql + ' where 1=1 '
  ...
  SELECT @sql = @sql + ' ' + @ConditionList + ' '


  SELECT @paramlist = ...

  EXEC sp_executesql @sql, @paramlist

当然，由于工具的不同，它可能会略有不同。

鲍里斯。