SQL Server 2008 - 如何获取具有特定 ID 范围的记录
在 SQL Server 2008 中，我想创建一个存储过程来选择特定产品，并将其 ID 作为参数传递。
但我打算带来一系列产品。
该范围必须具有通知的productIds参数值（例如：1、8和29 - 这意味着该范围必须具有productId 1、productId 8和productId 29）。
不需要的范围（例如productId 1 和productId 8、或productId 1 和productId 29、或productId 8 和productId 29）不会显示为结果。
如何从查询结果中过滤掉那些不需要的范围？
CREATE PROCEDURE [dbo].[ListProduct]
(
@categoryId
@productIds VARCHAR(4000) --Suppose we want to bring ONLY productIds 1 AND 8 AND 29
)
AS
BEGIN
SELECT
category_id,
product_id,
product_name,
FROM Product
WHERE category_id = @category_id
AND productId = --... is it better to use a function? a cursor?
END
如果我们有
CategoryId 1
ProductId 2
ProductId 8
ProductId 20
ProductId 29
CategoryId 2
ProductId 1
ProductId 3
ProductId 20
ProductId 29
CategoryId 3
ProductId 1
ProductId 8
ProductId 20
ProductId 29
结果将是
CategoryId 1 | ProductId 1
CategoryId 1 | ProductId 8
CategoryId 1 | ProductId 29
CategoryId 3 | ProductId 1
CategoryId 3 | ProductId 8
CategoryId 3 | ProductId 29
这是我到目前为止所得到的：
CREATE TABLE #mylist(product_id int NOT NULL PRIMARY KEY)
INSERT #mylist
SELECT Value FROM dbo.fn_split('1,8,29',',')
SELECT category_id,product_id
FROM Product
WHERE product_id IN (SELECT product_id FROM #mylist)
但是，当缺少产品 ID（如 8）时，此查询将显示：
CategoryId 1 | ProductId 1
CategoryId 1 | ProductId 8
CategoryId 1 | ProductId 29
CategoryId 2 | ProductId 1 --category2 should not be displayed,
CategoryId 2 | ProductId 29 -- because product id 8 is missing....
CategoryId 3 | ProductId 1
CategoryId 3 | ProductId 8
CategoryId 3 | ProductId 29
In SQL Server 2008 I would like to create a stored procedure to select specific products, passing their IDs as argument.
But I intend to bring as result a range of products.
This range must have the informed productIds argument value (for example: 1 and 8 and 29 - that means, the range MUST have productId 1 and productId 8 and productId 29).
The unwanted ranges (those like productId 1 and productId 8, or productId 1 and productId 29, or productId 8 and productId 29) won't be shown as result.
How can I filter those unwanted ranges from my query result?
CREATE PROCEDURE [dbo].[ListProduct]
(
@categoryId
@productIds VARCHAR(4000) --Suppose we want to bring ONLY productIds 1 AND 8 AND 29
)
AS
BEGIN
SELECT
category_id,
product_id,
product_name,
FROM Product
WHERE category_id = @category_id
AND productId = --... is it better to use a function? a cursor?
END
If we have
CategoryId 1
ProductId 2
ProductId 8
ProductId 20
ProductId 29
CategoryId 2
ProductId 1
ProductId 3
ProductId 20
ProductId 29
CategoryId 3
ProductId 1
ProductId 8
ProductId 20
ProductId 29
The result would be
CategoryId 1 | ProductId 1
CategoryId 1 | ProductId 8
CategoryId 1 | ProductId 29
CategoryId 3 | ProductId 1
CategoryId 3 | ProductId 8
CategoryId 3 | ProductId 29
Here is what I've got so far:
CREATE TABLE #mylist(product_id int NOT NULL PRIMARY KEY)
INSERT #mylist
SELECT Value FROM dbo.fn_split('1,8,29',',')
SELECT category_id,product_id
FROM Product
WHERE product_id IN (SELECT product_id FROM #mylist)
But, when there's a product id missing like 8, this query is showing:
CategoryId 1 | ProductId 1
CategoryId 1 | ProductId 8
CategoryId 1 | ProductId 29
CategoryId 2 | ProductId 1 --category2 should not be displayed,
CategoryId 2 | ProductId 29 -- because product id 8 is missing....
CategoryId 3 | ProductId 1
CategoryId 3 | ProductId 8
CategoryId 3 | ProductId 29
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
基本上，您传递一个值列表以使用行级过滤器
权威、普遍且经常引用的文章："SQL Server 中的数组和列表"
Basically, you're passing in a list of values to use a row level filter
The authoritative, ubiquitous, and oft-quoted article: "Arrays and List in SQL Server"
我之前见过通过调用函数将productid 字符串转换为表来完成的操作。然后，您可以将该表连接到查询中以限制结果。
这里似乎有一个这样做的例子：
https://web .archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=375
I've seen it done before by calling a function to convert the string of productids to a table. You can then join the table into your query to limit the results.
There seems to be an example of doing this here:
https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=375
传入一个表变量作为参数
Pass in a table variable as the parameter