如何找出每年销售量都递增的商品

[yyouyou]

有一数据库，表字段
year,goods,sales
year表示年份，goods表示商品，sales表示销售量
请教，如何用sql语句把哪些每年销售量都在递增的商品找出来呢？
year goods sales
2000 酒 100
2001 酒 110
2002 酒 120
2003 酒 130
2004 酒 140
2005 酒 150
2000 饼干 50
2001 饼干 60
2002 饼干 70
2003 饼干 80
2004 饼干 90
2005 饼干 85
2000 电视 100
2001 电视 200
2002 电视 300
2003 电视 400
2004 电视 500
2005 电视 600
2000 冰箱 50
2001 冰箱 60
2002 冰箱 70
2003 冰箱 65
2004 冰箱 80
2005 冰箱 90
我希望出来的结果就是
goods
酒
电视

[yyouyou]

year goods sales 2000 酒 100 2001 酒 110 2002 酒 120 2003 酒 130 2004 酒 140 2005 酒 150 2000 饼干 50 2001 饼干 60 2002 饼干 70 2003 饼干 80 2004 饼干 90 2005 饼干 85 2000 电视 100 2001 电视 200 2002 电视 300 2003 电视 400 2004 电视 500 2005 电视 600 2000 冰箱 50 2001 冰箱 60 2002 冰箱 70 2003 冰箱 65 2004 冰箱 80 2005 冰箱 90 我希望出来的结果就是 goods 酒 电视

[RINK_1]

SELECT DISTINCT GOODS FROM #T C WHERE NOT EXISTS (SELECT 1 FROM #T A JOIN #T B ON A.GOODS=B.GOODS AND A.YEAR=B.YEAR-1 WHERE B.SALES-A.SALES<=0 AND C.GOODS=A.GOODS)

[许晨旭]

create table sales
(
	years int,
	goods varchar(10),
	sales  int
)
insert into sales
select 2000,'酒',100 union all
select 2001,'酒',110 union all
select 2002,'酒',120 union all
select 2003,'酒',130 union all
select 2004,'酒',140 union all
select 2005,'酒',150 union all
select 2000,'饼干',50 union all
select 2001,'饼干',60 union all
select 2002,'饼干',70 union all
select 2003,'饼干',80 union all
select 2004,'饼干',90 union all
select 2005,'饼干',85 union all
select 2000,'电视',100 union all
select 2001,'电视',200 union all
select 2002,'电视',300 union all
select 2003,'电视',400 union all
select 2004,'电视',500 union all
select 2005,'电视',600 union all
select 2000,'冰箱',50 union all
select 2001,'冰箱',60 union all
select 2002,'冰箱',70 union all
select 2003,'冰箱',65 union all
select 2004,'冰箱',80 union all
select 2005,'冰箱',90


;with cte as(
select 
	ROW_NUMBER() over(partition by goods order by years) as yearsNum,
	ROW_NUMBER() over(partition by goods order by sales) as salesNum,
	*
from sales
)

select distinct goods 
from sales
where goods not in (
	select goods
	from cte
	where yearsNum<>salesNum
)

goods
----------
电视
酒

(2 行受影响)

[gw6328]

--create table sales
--(
--    years int,
--    goods varchar(10),
--    sales  int
--)
--insert into sales
--select 2000,'酒',100 union all
--select 2001,'酒',110 union all
--select 2002,'酒',120 union all
--select 2003,'酒',130 union all
--select 2004,'酒',140 union all
--select 2005,'酒',150 union all
--select 2000,'饼干',50 union all
--select 2001,'饼干',60 union all
--select 2002,'饼干',70 union all
--select 2003,'饼干',80 union all
--select 2004,'饼干',90 union all
--select 2005,'饼干',85 union all
--select 2000,'电视',100 union all
--select 2001,'电视',200 union all
--select 2002,'电视',300 union all
--select 2003,'电视',400 union all
--select 2004,'电视',500 union all
--select 2005,'电视',600 union all
--select 2000,'冰箱',50 union all
--select 2001,'冰箱',60 union all
--select 2002,'冰箱',70 union all
--select 2003,'冰箱',65 union all
--select 2004,'冰箱',80 union all
--select 2005,'冰箱',90

DECLARE @v NVARCHAR(max);
DECLARE @v1 NVARCHAR(max);
DECLARE @v2 NVARCHAR(max);

DECLARE @sql NVARCHAR(max);

SET @v=stuff((SELECT DISTINCT ',['+goods+'] as ['+goods+']' FROM sales FOR XML PATH('')),1,1,'')
SET @v1=stuff((SELECT DISTINCT ',['+goods+']' FROM sales FOR XML PATH('')),1,1,'')
SET @v2=stuff((SELECT DISTINCT ',replicate(''='',['+goods+']/10.0) as ['+goods+']' FROM sales FOR XML PATH('')),1,1,'')
SET @sql='
;with cte as (
	SELECT years,'+@v+' FROM sales pivot (max(sales) for goods in ('+@v1+') ) pt 
)
select years,'+@v2+' from cte
'

EXEC(@sql)

绘个条形图出来一看就看出来了。

[ChinaITOldMan]

引用 3 楼 Landa_Ran 的回复:

create table sales
(
	years int,
	goods varchar(10),
	sales  int
)
insert into sales
select 2000,'酒',100 union all
select 2001,'酒',110 union all
select 2002,'酒',120 union all
select 2003,'酒',130 union all
select 2004,'酒',140 union all
select 2005,'酒',150 union all
select 2000,'饼干',50 union all
select 2001,'饼干',60 union all
select 2002,'饼干',70 union all
select 2003,'饼干',80 union all
select 2004,'饼干',90 union all
select 2005,'饼干',85 union all
select 2000,'电视',100 union all
select 2001,'电视',200 union all
select 2002,'电视',300 union all
select 2003,'电视',400 union all
select 2004,'电视',500 union all
select 2005,'电视',600 union all
select 2000,'冰箱',50 union all
select 2001,'冰箱',60 union all
select 2002,'冰箱',70 union all
select 2003,'冰箱',65 union all
select 2004,'冰箱',80 union all
select 2005,'冰箱',90


;with cte as(
select 
	ROW_NUMBER() over(partition by goods order by years) as yearsNum,
	ROW_NUMBER() over(partition by goods order by sales) as salesNum,
	*
from sales
)

select distinct goods 
from sales
where goods not in (
	select goods
	from cte
	where yearsNum<>salesNum
)

goods
----------
电视
酒

(2 行受影响)

正确！

[Haytor]

SQL2012有另外一种方法，可以参考一下：

;WITH Goods([Year],Goods,Sales) AS (
	SELECT 2000,'酒',100 UNION ALL
	SELECT 2001,'酒',110 UNION ALL
	SELECT 2002,'酒',120 UNION ALL
	SELECT 2003,'酒',130 UNION ALL
	SELECT 2004,'酒',140 UNION ALL
	SELECT 2005,'酒',150 UNION ALL
	SELECT 2000,'饼干',50 UNION ALL
	SELECT 2001,'饼干',60 UNION ALL
	SELECT 2002,'饼干',70 UNION ALL
	SELECT 2003,'饼干',80 UNION ALL
	SELECT 2004,'饼干',90 UNION ALL
	SELECT 2005,'饼干',85 UNION ALL
	SELECT 2000,'电视',100 UNION ALL
	SELECT 2001,'电视',200 UNION ALL
	SELECT 2002,'电视',300 UNION ALL
	SELECT 2003,'电视',400 UNION ALL
	SELECT 2004,'电视',500 UNION ALL
	SELECT 2005,'电视',600 UNION ALL
	SELECT 2000,'冰箱',50 UNION ALL
	SELECT 2001,'冰箱',60 UNION ALL
	SELECT 2002,'冰箱',70 UNION ALL
	SELECT 2003,'冰箱',65 UNION ALL
	SELECT 2004,'冰箱',80 UNION ALL
	SELECT 2005,'冰箱',90
),
Tab2 AS (
	SELECT
		a.Year,
		a.Goods,
		SUM(a.Sales) AS  Sales,
		LEAD(SUM(a.Sales)) OVER(PARTITION BY a.Goods ORDER BY a.year) AS NextYear
	FROM 
		Goods a
	GROUP BY
		a.Year,a.Goods
)
SELECT a.Goods 
FROM Goods a
WHERE NOT EXISTS( 
				SELECT 
					* 
				FROM 
					Tab2 b 
				WHERE 
					b.Sales > ISNULL(b.NextYear,b.Sales)
				AND b.Goods = a.Goods
				)
GROUP BY a.Goods

Goods
电视
酒