T-SQL查询垂直表结构

Question

我在做电子商务项目，现在必须为产品列表页面构建筛选器。我的表格如下。

产品

id title      | description           | Etc.
-- ---------- | --------------------- | -----------
1  Product  1 | Product 1 description | xxx
2  Product  2 | Product 2 description | xxx
3  Product  3 | Product 3 description | xxx
4  Product  4 | Product 4 description | xxx
5  Product  5 | Product 5 description | xxx

规格

id title      | Etc.
-- ---------- | ------
1  Color      | xxx
2  Display    | xxx

产品规格

id          | productId   | specificationId | value
----------- | ----------- | --------------- | -----
1           | 1           | 1               | Red
2           | 1           | 2               | LED
3           | 2           | 1               | Red
4           | 2           | 2               | OLED
5           | 3           | 1               | Blue
6           | 3           | 2               | LED
7           | 4           | 1               | Blue
8           | 4           | 2               | OLED

电子商务的用户必须能够同时过滤多个选项。我的意思是，用户可能想搜索"(红色或蓝色)和OLED"电视。

我尝试过一些东西，但我写不出正确的存储过程。我想我卡在这里了，需要一些帮助。

编辑：

经过一些回答，我需要在这里更新一些额外的信息。

规格是动态的。因此，过滤器也是动态的。我使用一个名为allowFilter的位列来生成过滤器。因此，我不能使用强类型参数，如@color或@display。

用户可能不使用过滤器。或者他们可能使用一个或多个过滤器。您可以在此处找到我正在处理的查询：

ALTER PROCEDURE [dbo].[ProductsGetAll]
@categoryId int,
@brandIds varchar(max),
@specIds varchar(max),
@specValues varchar(max),
@pageNo int,
@pageSize int,
@status smallint,
@search varchar(255),
@sortOrder smallint
as
/*
TODO: Modify query to use sortOrder
*/
select * into #products
from
(
    select ROW_NUMBER() OVER (order by p.sortOrder) as rowId,p.*
    from Products p left join ProductSpecifications ps on ps.productId = p.id
    where
    (@status = -1
        or (@status = -2 and (p.status = 0 or p.status = 1))
        or (p.status = @status)
    )
    and (@categoryId = -1 or p.categoryId = @categoryId)
    and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
    and (
        @search = ''
        or p.title like '%' + @search + '%'
        or p.description like '%' + @search + '%'
        or p.detail like '%' + @search + '%'
    )
    and (@specIds = ''
        or (
            ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
            and ps.value in (@specValues)
        )
    )
) x
where
(rowId > @pageSize * (@pageNo - 1) and rowId <= @pageSize * @pageNo)

select * from #products
select * from Categories where id in (select categoryId from #products)
select * from Brands where id in (select brandId from #products)

select count(p.id)
from Products p left join ProductSpecifications ps on ps.productId = p.id
where 
(@status = -1
    or (@status = -2 and (p.status = 0 or p.status = 1))
    or (p.status = @status)
)
and (@categoryId = -1 or p.categoryId = @categoryId)
and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
and (
    @search = ''
    or p.title like '%' + @search + '%'
    or p.description like '%' + @search + '%'
    or p.detail like '%' + @search + '%'
)
and (@specIds = ''
    or (
        ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
        and ps.value in (@specValues)
    )
)

drop table #products

我的问题涉及以下部分：

and (@specIds = ''
        or (
            ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
            and ps.value in (@specValues)
        )
    )

我可以完全改变这部分和这部分所使用的参数。

- isabasan

---

如果您能分享您已经完成的查询，我们可以帮助您。 - Ven

@BHouse 我的实际查询有点复杂，不太方便在这里分享。因为涉及到更多的关系和要求。我会尝试简化它并在这里分享。 - isabasan

你正在使用哪个版本的SQL Server？ - CPearson

是的，我在处理这个查询的时候意识到我失败了。然后我决定寻求帮助。 - isabasan

也许你会发现这个链接有用：http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm （另外，如果@specValues是表变量，应该使用SELECT value FROM @specValues） - Vanity Slug

Answer 1

首先，我要感谢@alex。我使用了表值参数来解决我的问题。

类型：

CREATE TYPE [dbo].[specificationsFilter] AS TABLE(
    [specId] [int] NULL,
    [specValue] [varchar](50) NULL
)

存储过程：

ALTER PROCEDURE [dbo].[ProductsGetAll]
@categoryId int,
@brandIds varchar(max),
@specifications specificationsFilter readonly,
@pageNo int,
@pageSize int,
@status smallint,
@search varchar(255),
@sortOrder smallint
as
declare @filterCount int
set @filterCount = (select count(distinct specId) from @specifications)
/*
ORDER BY
    TODO: Modify query to use sortOrder
*/
select * into #products
from
(
    select ROW_NUMBER() OVER (order by p.sortOrder) as rowId,p.*
    from Products p
    where
    (@status = -1
        or (@status = -2 and (p.status = 0 or p.status = 1))
        or (p.status = @status)
    )
    and (@categoryId = -1 or p.categoryId = @categoryId)
    and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
    and (
        @search = ''
        or p.title like '%' + @search + '%'
        or p.description like '%' + @search + '%'
        or p.detail like '%' + @search + '%'
    )
    and (@filterCount = 0
        or (
            p.id in (
                select productId
                from ProductSpecifications ps, @specifications s
                where
                ps.specificationId = s.specId
                and ps.value = s.specValue
                group by productId
                having sum(1) >= @filterCount
            )
        )
    )
) x
where
(rowId > @pageSize * (@pageNo - 1) and rowId <= @pageSize * @pageNo)

select * from #products
select * from Categories where id in (select categoryId from #products)
select * from Brands where id in (select brandId from #products)

select count(p.id)
from Products p
where 
(@status = -1
    or (@status = -2 and (p.status = 0 or p.status = 1))
    or (p.status = @status)
)
and (@categoryId = -1 or p.categoryId = @categoryId)
and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
and (
    @search = ''
    or p.title like '%' + @search + '%'
    or p.description like '%' + @search + '%'
    or p.detail like '%' + @search + '%'
)
and (@filterCount = 0
    or (
        p.id in (
            select productId
            from ProductSpecifications ps, @specifications s
            where
            ps.specificationId = s.specId
            and ps.value = s.specValue
            group by productId
            having sum(1) >= @filterCount
        )
    )
)

drop table #products

.Net代码创建数据表参数：

    private DataTable GetSpecificationFilter(string specificationFilter)
    {
        DataTable table = new DataTable();
        table.Columns.Add("specId", typeof(Int32));
        table.Columns.Add("specValue", typeof(string));

        string[] specifications = specificationFilter.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
        foreach(string specification in specifications)
        {
            string[] specificationParams = specification.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
            int specificationId = Convert.ToInt32(specificationParams[0]);
            string[] specificationValues = specificationParams[1].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            foreach(string value in specificationValues)
            {
                table.Rows.Add(specificationId, value);
            }
        }
        return table;
    }

我的查询字符串结构如下：

?specs=1:Red,Blue;3:LED,OLED

这是一个完整的解决方案，用于在垂直表格结构中过滤产品规格。我在电子商务项目中使用了它。我希望这个解决方案能够帮助您处理类似情况。

- isabasan

Answer 2

你需要一种方式来传递规格及其值。一种方法是在整个查询中使用 group by 和 having ：

select ps.product_id
from product_specifications ps join
     specifications s
     on ps.specification_id = s.specification_id
where (s.name = @title1 and ps.value = @value1) or
      (s.name = @title2 and ps.value = @value2)
having count(*) = 2;  -- "2" is the number of specifications you are checking

这个版本需要将规范和值作为单独的变量添加进去。有类似的方法，你可以使用临时变量或者values子句传递值。在特定情况下，哪种传值方法更有效并不清楚。

- Gordon Linoff

---

我已经更新了我的问题并添加了一些额外的信息。@Gordon，你能否请审核一下？我猜我们在同一个点上。 :) - isabasan

Answer 3

更新

在这种情况下应该使用表值参数。(参见相关)

---

旧回答
_{这似乎是op原始存储过程中发生的变化的一种变体。}

这不是最好的方法，但可以完成工作。

CREATE PROCEDURE GetData
    @Color CHAR(2) -- "10" is only red, "01" is only green, "11" is both red and green
,   @Display CHAR(2) -- "10" is LED, "01" is OLED, "11" is both LED and OLED
AS
BEGIN
    DECLARE @Values TABLE (Value NVARCHAR(10))

    IF SUBSTRING(@Color, 1, 1) = '1'   BEGIN INSERT INTO @Values (Value) VALUES ('Red') END
    IF SUBSTRING(@Color, 2, 1) = '1'   BEGIN INSERT INTO @Values (Value) VALUES ('Green') END   
    IF SUBSTRING(@Display, 1, 1) = '1' BEGIN INSERT INTO @Values (Value) VALUES ('LED') END
    IF SUBSTRING(@Display, 2, 1) = '1' BEGIN INSERT INTO @Values (Value) VALUES ('OLED') END

    SELECT      *
    FROM        productspecifications ps
    INNER JOIN  products p
    ON          p.id = ps.productid
    INNER JOIN  specifications s
    ON          ps.specificationid = s.id
    WHERE       ps.Value IN (SELECT * FROM @Values)
END

这个例子非常针对你在问题中提供的表格。

如何工作的解释：

您传递两个字符串，这些字符串仅由零和一组成（例如：“0010110”）。您的存储过程将知道在字符串@Color的索引0处解释为红色，索引1处的1解释为蓝色。对于LED与OLED也是同样。您的存储过程将有许多IF语句来检查每个字符串中的每个索引，并将相应的值存储在某个临时表中（或者如果没有太多值，则使用临时表变量）。然后，当查询表格时，只需放置一个单独的WHERE子句，该子句检查ProductSpecifications表中的值是否存在于您刚刚创建的临时表中。

如何运作：

如果您想要“（红色或蓝色）和LED”，则 @Color =“10”并且 @Display =“10”。 如果您想要“蓝色和OLED”，则 @Color =“01”并且 @Display =“01”。 如果您想要全部，则 @Color =“11”并且 @Display =“11”。

优点

• 您可以实现(红色或蓝色)和LED逻辑效果

缺点

• 您必须知道传递的字符串中哪个索引对应哪个值
• 逻辑从存储过程渗漏到代码中（缺乏封装性）

结论

这不是一个好的解决方案。我个人不喜欢它，但它能完成工作。如果有人知道如何改进这个方法，那将是非常棒的。我也很想学习更好的解决方案。
此外，我发现您需要将“数组”数据作为参数传递给存储过程，因此我认为您可能需要查看不同的方法来实现这一点。我提供的示例是实现“数组传递”的一种方式，但还有许多其他更好的方法。

- Vanity Slug

---

是的@alex，我知道这会起作用，但我也不太喜欢它。无论如何，还是谢谢你。 - isabasan

Answer 4

我认为如果只有值参数，这将起作用，或者您可以添加更多的搜索参数

CREATE PROCEDURE usp_ProductSpecifications (@value)
    AS
    BEGIN
        SELECT p.id
            ,p.NAME
            ,s.etc
            ,ps.value
            ,p.etc
        FROM productspecifications ps
        INNER JOIN products p
            ON p.id = ps.productid
        INNER JOIN specifications s
            ON ps.specificationid = s.id
        WHERE ps.value = @value
    END

- Ven

Answer 5

请尝试以下建议的解决方案，希望能有所帮助！

Create Procedure SearchByCriteria
       @Color  VARCHAR(100) = NULL,
       @Display VARCHAR(100) = NULL
       AS  
       BEGIN
        IF @Color IS NOT NULL
        SET @Color = '%' + REPLACE (@Color,',','% OR ') + '%'
       SELECT 
       fROM PRoduct p
       INNER JOIN ProductSpecification ps ON ps.ProductId = p.productID
       LEFT OUTER JOIN specification scolor ON scolor.ID = ps.SpecificationID
                         and scolor.Id = 1
       LEFT OUTER JOIN specification sDisplay ON sdisplay.ID = ps.SpecificationID
                         and sdisplay.Id = 2
       WHERE (@Color IS NULL OR  scolor.etc like @Color)
       AND (@Display IS NULL OR  Sdisplay like @Display)

       END 
       GO

- S V

Answer 6

我认为你首先需要一个外键来实现你想要的功能。

你可以在"Products"表中添加一个字段，命名为"specification"，这将成为你的外键。

之后，尝试使用"GROUP BY"表达式来实现你想要的功能。

- Frank