sql server 2005查询-唯一随机父行的随机子行

发布于 2024-08-02 04:33:57 字数 677 浏览 6 评论 0原文

我有一个父表“ProductCategory”和一个子表“Product”。我有一个返回 3 个随机产品的查询：

SELECT    TOP (3) ProductId
FROM      Product
ORDER BY NEWID();

我想增强该查询以实现所有产品都来自不同的产品类别。因此，获取唯一类别的查询将是：

SELECT    TOP (3) ProductCategoryId
FROM      ProductCategory
ORDER BY NEWID();

我无法弄清楚如何组合这两个查询来实现我的目标。明显的查询

SELECT    TOP (3) p.ProductId
FROM      Product p
where p.productcategory_ProductCategoryId in
    (
    SELECT    TOP (3) ProductCategoryId pc
    FROM      ProductCategory pc
    ORDER BY NEWID()
    )
ORDER BY NEWID();

不起作用。似乎内部 select 语句被忽略了。我还尝试使用 EXISTS 语句或连接表。全部结果相同。

有人有主意吗？预先非常感谢！

原文

I got a parent table 'ProductCategory' and a child table 'Product'. I have this query that returns 3 random products:

SELECT    TOP (3) ProductId
FROM      Product
ORDER BY NEWID();

I want to enhance that query to achieve that all the products are from different product categories. So the query to get unique categories would be:

SELECT    TOP (3) ProductCategoryId
FROM      ProductCategory
ORDER BY NEWID();

I am not able to figure out how to combine those 2 queries to achieve my goal. The obvious query

SELECT    TOP (3) p.ProductId
FROM      Product p
where p.productcategory_ProductCategoryId in
    (
    SELECT    TOP (3) ProductCategoryId pc
    FROM      ProductCategory pc
    ORDER BY NEWID()
    )
ORDER BY NEWID();

does not work. It seems like the inner select statement is ignored. I also tried with the EXISTS statement or joining the tables. All with the same result.

Does someone have an idea? Thanks a lot in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

很酷不放纵 2024-08-09 04:33:57

我现在明白了！

在 Burbidge87 中，我添加了一个 where 条件：

FROM Product p
where @CategoryID = p.ProductCategory_ProductCategoryId

就可以了。再次感谢！

杰杰

I got it now!

In Burbidge87's I added a where condition:

FROM Product p
where @CategoryID = p.ProductCategory_ProductCategoryId

that does it. Thanks again!

回复收藏 0 原文

始终不够 2024-08-09 04:33:57

您必须将 2 个查询解耦，这是

Per ProductCategoryId 的一种解决方案，即相关子查询来获取随机产品。 ProductCategoryId 的唯一性由外部查询处理。

SELECT TOP 3
    (SELECT TOP 1
        ProductId
    FROM
        Product P
    WHERE
        P.ProductCategoryId = PC.ProductCategoryId
    ORDER BY 
        NEWID()
    ) AS ProductId
FROM
    ProductCategory PC
WHERE
    EXISTS (SELECT *
        FROM
            Product Pex
        WHERE
            Pex.ProductCategoryId = PC.ProductCategoryId)
ORDER BY
    NEWID();

You have to decouple the 2 queries and this is one solution

Per ProductCategoryId, correlated subquery to get a random product. Uniqueness of ProductCategoryId is handled by the outer query.

SELECT TOP 3
    (SELECT TOP 1
        ProductId
    FROM
        Product P
    WHERE
        P.ProductCategoryId = PC.ProductCategoryId
    ORDER BY 
        NEWID()
    ) AS ProductId
FROM
    ProductCategory PC
WHERE
    EXISTS (SELECT *
        FROM
            Product Pex
        WHERE
            Pex.ProductCategoryId = PC.ProductCategoryId)
ORDER BY
    NEWID();

回复收藏 0 原文

毅然前行 2024-08-09 04:33:57

像这样的事情怎么样？我能够使用临时表和游标完成此任务。虽然步骤较长，但确实有效。

create table #temp(
 productID int
,CategoryID int
)
declare @CategoryID int
declare ID_Cursor cursor
for select ProductCategoryID from ProductCategory order by NEWID() 
open ID_Cursor
FETCH NEXT FROM ID_Cursor INTO @CategoryID

WHILE @@FETCH_STATUS = 0 and (select COUNT(*) from #temp)<3
BEGIN

if (@CategoryID not in (select CategoryID from #temp))
Begin
insert into #temp
SELECT top(1) ProductID, @CategoryID
  FROM [Product] 
  order by NEWID() 
 END 

FETCH NEXT FROM ID_Cursor INTO @CategoryID
END 
CLOSE ID_Cursor
DEALLOCATE ID_Cursor

select * from #temp
drop table #temp

How about something like this? I was able to accomplish this task using a temp table and a cursor. Longer of a step, but it works.

create table #temp(
 productID int
,CategoryID int
)
declare @CategoryID int
declare ID_Cursor cursor
for select ProductCategoryID from ProductCategory order by NEWID() 
open ID_Cursor
FETCH NEXT FROM ID_Cursor INTO @CategoryID

WHILE @@FETCH_STATUS = 0 and (select COUNT(*) from #temp)<3
BEGIN

if (@CategoryID not in (select CategoryID from #temp))
Begin
insert into #temp
SELECT top(1) ProductID, @CategoryID
  FROM [Product] 
  order by NEWID() 
 END 

FETCH NEXT FROM ID_Cursor INTO @CategoryID
END 
CLOSE ID_Cursor
DEALLOCATE ID_Cursor

select * from #temp
drop table #temp

回复收藏 0 原文

~没有更多了~