I have this query:
我有这个问题:
SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName
FROM Items WHERE Items.ProductCode IN (
SELECT TOP 10 Recommended.ProductCode
FROM
Recommended
INNER JOIN Stock ON Recomended.ProductCode = Stock.ProductCode
AND Stock.StatusCode = 1
WHERE (Recommended.Type = 'TOPICAL') ORDER BY CHECKSUM(NEWID()));
It is fine for my data, except that the Recommended table has a SKU field I need also however I cannot put it next to Recommended.ProductCode and have the query still work.
I have used JOINS for this query and these work - but this query runs faster I just need the ProductCode and SKU from the Recommended table - how can this be done without needing yet another sub query?
Database: MS SQL Server 2000
它对我的数据很好,除了推荐表有一个我需要的SKU字段但是我不能把它放在Recommended.ProductCode旁边并让查询仍然有效。我已经使用JOINS进行此查询并且这些工作 - 但是此查询运行得更快我只需要推荐表中的ProductCode和SKU - 如何在不需要其他子查询的情况下完成此操作?数据库:MS SQL Server 2000
4 个解决方案
#1
The subquery seems to be picking 10 random recommendations. I think you can do that without a subquery:
子查询似乎正在挑选10个随机推荐。我认为你可以在没有子查询的情况下做到这一点:
SELECT TOP 10
Items.*,
Recommended.*,
Stock.*
FROM Items
INNER JOIN Recommended
ON Items.ProductCode = Recommended.ProductCode
AND Recommended.Type = 'TOPICAL'
INNER JOIN Stock
ON Recomended.ProductCode = Stock.ProductCode
AND Stock.StatusCode = 1
ORDER BY CHECKSUM(NEWID())
This gives you access to all columns, without having to pass them up from the subquery.
这使您可以访问所有列,而无需从子查询中传递它们。
#2
You can only return one value with the subselect, so you have to obtain the fields from the Recommended table by a join - which I presume is what you have already:
您只能通过子选择返回一个值,因此您必须通过连接从推荐表中获取字段 - 我认为这是您已经拥有的:
SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName, Recommended.SKU FROM Items INNER JOIN Recommended ON Recommended.ProductCode = Items.ProductCode WHERE Items.ProductCode IN ( SELECT TOP 10 Recommended.ProductCode FROM Recommended INNER JOIN Stock ON Recomended.ProductCode = Stock.ProductCode AND Stock.StatusCode = 1 WHERE (Recommended.Type = 'TOPICAL') ORDER BY CHECKSUM(NEWID()));
Most likely the Join in reality is an outer too I guess. This really shouldn't have any performance issues so long as you have both the Items and and Recommended tables indexed on ProductCode.
我猜,很可能现实中的加入也是外在的。只要您在ProductCode上同时包含Items和Recommended表,就不会有任何性能问题。
#3
I think you need to move the subquery out of the where clause:
我认为您需要将子查询移出where子句:
SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName, R.SKU
FROM Items
INNER JOIN
(SELECT TOP 10 Recommended.ProductCode, Recommended.SKU FROM Recommended
INNER JOIN Stock ON Recommended.ProductCode = Stock.ProductCode AND
Stock.StatusCode = 1 WHERE (Recommended.Type = 'TOPICAL')
ORDER BY CHECKSUM(NEWID()))
AS Rec ON Items.ProductCode = Rec.ProductCode;
The above is valid syntax in MySQL, your mileage may vary...
以上是MySQL中的有效语法,您的里程可能会有所不同......
#4
Under those circumstances I would normally use an inner join to get the row filtering from the where clause I needed and the extra columns. Something like below; if this is what you did that gave you a performance hit then you might need to flip the query; go from recommended and join to items; as that will probably lead to more data filtering before the join.
在这种情况下,我通常会使用内部联接从我需要的where子句和额外的列中获取行过滤。像下面的东西;如果这是你所做的那个给你带来性能打击的话,你可能需要翻转查询;从推荐和加入项目;因为这可能会导致在加入之前进行更多的数据过滤。
SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName
FROM Items
Inner Join
(
SELECT TOP 10 Recommended.ProductCode, SKUID
FROM
Recommended
INNER JOIN Stock ON Recomended.ProductCode = Stock.ProductCode
AND Stock.StatusCode = 1
WHERE (Recommended.Type = 'TOPICAL')
) reccomended
on items.productcode - reccomended.ProductCode
ORDER BY CHECKSUM(NEWID()
#1
The subquery seems to be picking 10 random recommendations. I think you can do that without a subquery:
子查询似乎正在挑选10个随机推荐。我认为你可以在没有子查询的情况下做到这一点:
SELECT TOP 10
Items.*,
Recommended.*,
Stock.*
FROM Items
INNER JOIN Recommended
ON Items.ProductCode = Recommended.ProductCode
AND Recommended.Type = 'TOPICAL'
INNER JOIN Stock
ON Recomended.ProductCode = Stock.ProductCode
AND Stock.StatusCode = 1
ORDER BY CHECKSUM(NEWID())
This gives you access to all columns, without having to pass them up from the subquery.
这使您可以访问所有列,而无需从子查询中传递它们。
#2
You can only return one value with the subselect, so you have to obtain the fields from the Recommended table by a join - which I presume is what you have already:
您只能通过子选择返回一个值,因此您必须通过连接从推荐表中获取字段 - 我认为这是您已经拥有的:
SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName, Recommended.SKU FROM Items INNER JOIN Recommended ON Recommended.ProductCode = Items.ProductCode WHERE Items.ProductCode IN ( SELECT TOP 10 Recommended.ProductCode FROM Recommended INNER JOIN Stock ON Recomended.ProductCode = Stock.ProductCode AND Stock.StatusCode = 1 WHERE (Recommended.Type = 'TOPICAL') ORDER BY CHECKSUM(NEWID()));
Most likely the Join in reality is an outer too I guess. This really shouldn't have any performance issues so long as you have both the Items and and Recommended tables indexed on ProductCode.
我猜,很可能现实中的加入也是外在的。只要您在ProductCode上同时包含Items和Recommended表,就不会有任何性能问题。
#3
I think you need to move the subquery out of the where clause:
我认为您需要将子查询移出where子句:
SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName, R.SKU
FROM Items
INNER JOIN
(SELECT TOP 10 Recommended.ProductCode, Recommended.SKU FROM Recommended
INNER JOIN Stock ON Recommended.ProductCode = Stock.ProductCode AND
Stock.StatusCode = 1 WHERE (Recommended.Type = 'TOPICAL')
ORDER BY CHECKSUM(NEWID()))
AS Rec ON Items.ProductCode = Rec.ProductCode;
The above is valid syntax in MySQL, your mileage may vary...
以上是MySQL中的有效语法,您的里程可能会有所不同......
#4
Under those circumstances I would normally use an inner join to get the row filtering from the where clause I needed and the extra columns. Something like below; if this is what you did that gave you a performance hit then you might need to flip the query; go from recommended and join to items; as that will probably lead to more data filtering before the join.
在这种情况下,我通常会使用内部联接从我需要的where子句和额外的列中获取行过滤。像下面的东西;如果这是你所做的那个给你带来性能打击的话,你可能需要翻转查询;从推荐和加入项目;因为这可能会导致在加入之前进行更多的数据过滤。
SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName
FROM Items
Inner Join
(
SELECT TOP 10 Recommended.ProductCode, SKUID
FROM
Recommended
INNER JOIN Stock ON Recomended.ProductCode = Stock.ProductCode
AND Stock.StatusCode = 1
WHERE (Recommended.Type = 'TOPICAL')
) reccomended
on items.productcode - reccomended.ProductCode
ORDER BY CHECKSUM(NEWID()