你能帮我解决这个问题 [英] can you help me in this query

问题描述

大家好



我有这样的产品表：



productID，producteName，category



现在，我需要将每个类别的最新4个产品插入到产品表中

请帮助我



关于

Hi all

I have products table like this:

productID,producteName,category

now, I need to get last 4 products inserted into products table for every category
help me please

regards

推荐答案

试试这个：

Try this:

DECLARE @ct INT

SELECT @ct = COUNT(DISTINCT category)
FROM @products

SET @ct = @ct * 4

SELECT T.*
FROM (
	SELECT productID, producteName, category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY productID DESC) AS Number
	FROM products
	) AS T
WHERE T.Number < 5

现在，它必须工作！



我在下面的例子中进行了测试：

Now, it must work!

I tested it on below example:

DECLARE @products TABLE (productID INT IDENTITY(1,1), producteName VARCHAR(30), category VARCHAR(30))

INSERT INTO @products (producteName, category)
VALUES('Audi','cars')
INSERT INTO @products (producteName, category)
VALUES('Volkswagen','cars')
INSERT INTO @products (producteName, category)
VALUES('Citroen','cars')
INSERT INTO @products (producteName, category)
VALUES('Peugeot','cars')
INSERT INTO @products (producteName, category)
VALUES('Honda','cars')
INSERT INTO @products (producteName, category)
VALUES('Toyota','cars')
INSERT INTO @products (producteName, category)
VALUES('Yamaha','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Romet','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Zipp','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Kymco','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Router','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Zumico','motor scooter')
INSERT INTO @products(producteName, category)
VALUES('Fiat','caravan')
INSERT INTO @products (producteName, category)
VALUES('Skoda','caravan')

SELECT T.*
FROM (
	SELECT productID, producteName, category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY productID DESC) AS Number
	FROM @products
	) AS T
WHERE T.Number < 5

结果：

< TR> < TD>雪铁龙

的productID	producteName	类别	数
14	斯柯达	大篷车	1
13	菲亚特	大篷车	2
6	丰田	汽车	1
5	本田	汽车	2
4	标致	汽车	3
3	汽车	4
12	Zumico	马达滑板车	1
11	路由器	小型摩托车	2
10	Kymco	小型摩托车	3
9	Zipp	小型摩托车	4

[/ EDIT]





阅读我的评论并点击链接。

这种艺术 [ ^ ]是关于如何在MySQL查询中模拟MS SQL ROW_NUMBER（）函数，但是有一些限制。

Result:

productID	producteName	category	Number
14	Skoda	caravan	1
13	Fiat	caravan	2
6	Toyota	cars	1
5	Honda	cars	2
4	Peugeot	cars	3
3	Citroen	cars	4
12	Zumico	motor scooter	1
11	Router	motor scooter	2
10	Kymco	motor scooter	3
9	Zipp	motor scooter	4

[/EDIT]


Read my comments and follow the links.
This artcile[^] is about how to emulate MS SQL ROW_NUMBER() function in MySQL queries, but there are some restrictions.

引用：

我们可以有吗这个结果在MySQL？这有两种方法可以实现。首先，使用用户变量：

Can we have this result in MySQL? Here are two ways to get it. First, with user variables:

SET @iprev=0, @jprev=0;
SELECT i, j, row_number
FROM (
  SELECT j, @jprev := if(@iprev = i, @jprev+1, 1) AS row_number, @iprev := i AS i
  FROM test
  ORDER BY i,j
) AS tmp;

第二种方法使用连接和聚合，但只有在没有重复值时才正确。

The second method uses a join and aggregation, but is correct only if there are no duplicate values of j

SELECT a.i, a.j, count(*) as row_number
FROM test a
JOIN test b ON a.i=b.i AND a.j >= b.j
GROUP BY a.i, a.j ;  

更有趣的示例查询，你会在这里找到：http://www.artfulsoftware.com/infotree/queries.php [ ^ ]

[/ EDIT]

More interesting example queries, you''ll find here: http://www.artfulsoftware.com/infotree/queries.php[^]
[/EDIT]

基于Maciej Los ] 解决方案2 [ ^ 我会先生成所有类别的列表，然后从那里开始工作：

Based on Maciej Los] excellent example in Solution 2[^] I''d do it by generating a list of all categories first and then work it from there:

SELECT t.category, DISTINCT p1.category
FROM @products AS p1 RIGHT JOIN (
    SELECT TOP(4) p2.productID, p2.category
    FROM @products AS p1
    ORDER BY p2.productID DESC
) AS t ON p1.category = t.category
ORDER BY p1.category ASC

很抱歉，我不能尝试这个，因为我没有SQL Server 2008 Handy的实例（因为在Maciej的例子中使用的表类型。



问候，

- Manfred

您好，



检查解决方案....解决方案1示例数据

Hi,

Check the Solution.... with Solution 1 Sample Data

DECLARE @ct INT
SET @ct = 4
 
SELECT p.productID, p.producteName, p.category
FROM @products AS p 
INNER JOIN (SELECT productID, 
               ROW_NUMBER() OVER(PARTITION BY category ORDER BY productID DESC) 'RowNumber'
 	    FROM @products) T ON T.productID=p.productID AND T.RowNumber<= @ct

这篇关于你能帮我解决这个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！