sql-server - 垂直表结构的 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)
        )
    )

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

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

这是在垂直表结构中过滤产品规范的完整解决方案。我将其用于电子商务项目。我希望这个解决方案可以帮助您解决类似的情况。