就是说要根据客户的产品来显示
比如 有100个会员会员的产品数量都不一样有的可能20个产品,现在要不管一个会员有多少产品,只显示最新的3条在这里
如果某个客户不足3条的话就把那个客户的数据都显示比如
会员 产品 时间
id product date
1 苹果 1999-1-1
1 西瓜 1999-1-2
1 凤梨 1999-9-9
1 菠萝 2008-5-5
1 橘子 1998-8-4
2 电脑 2002-1-1
2 U盘 2003-5-7
2 MP3 2008-2-2
2 手机 2006-7-9
3 车子 2005-7-9
比如 有100个会员会员的产品数量都不一样有的可能20个产品,现在要不管一个会员有多少产品,只显示最新的3条在这里
如果某个客户不足3条的话就把那个客户的数据都显示比如
会员 产品 时间
id product date
1 苹果 1999-1-1
1 西瓜 1999-1-2
1 凤梨 1999-9-9
1 菠萝 2008-5-5
1 橘子 1998-8-4
2 电脑 2002-1-1
2 U盘 2003-5-7
2 MP3 2008-2-2
2 手机 2006-7-9
3 车子 2005-7-9
解决方案 »
- mssql update where
- 求一个简单的存储过程!
- Java [SQL Server]为过程或函数 pr_buildTest 指定的参数太多。
- 如何根据参数动态改变联接的列
- 求个sql
- select * into table1 from mt_keh 如何查询table1表的内容?
- 如何实现自动删除表中的所有记录
- 我的SQL server 2000怎么就是安装不起来,是不是跟分区有关阿
- 在sqlserver2000里能否用sql语句实现下列控制功能?(在线等待)
- 怎样才能进入oracle 8.15 oracle Enterprise console
- 北京外企高薪聘SQL Server DBA
- 请问如何修改有主键和外键的表?
select top 3 时间 from tb where id=a.id order by 时间 desc
) order by a.id,a.date
insert into tb select 1 , '西瓜' , '1999-1-2'
insert into tb select 1 , '凤梨' , '1999-9-9'
insert into tb select 1 , '菠萝' , '2008-5-5'
insert into tb select 1 , '橘子' , '1998-8-4'
insert into tb select 2 , '电脑' , '2002-1-1'
insert into tb select 2 , 'U盘' , '2003-5-7'
insert into tb select 2 , 'MP3' , '2008-2-2'
insert into tb select 2 , '手机' , '2006-7-9'
insert into tb select 3 , '车子' , '2005-7-9'select A.* from tb A where product in (
select top 3 product from tb where id = A.id )/*
1 苹果 1999-01-01 00:00:00.000
1 西瓜 1999-01-02 00:00:00.000
1 凤梨 1999-09-09 00:00:00.000
2 电脑 2002-01-01 00:00:00.000
2 U盘 2003-05-07 00:00:00.000
2 MP3 2008-02-02 00:00:00.000
3 车子 2005-07-09 00:00:00.000*/drop table tb
Insert @tb
select N'1',N'苹果',N'1999-1-1' union all
select N'1',N'西瓜',N'1999-1-2' union all
select N'1',N'凤梨',N'1999-9-9' union all
select N'1',N'菠萝',N'2008-5-5' union all
select N'1',N'橘子',N'1998-8-4' union all
select N'2',N'电脑',N'2002-1-1' union all
select N'2',N'U盘',N'2003-5-7' union all
select N'2',N'MP3',N'2008-2-2' union all
select N'2',N'手机',N'2006-7-9' union all
select N'3',N'车子',N'2005-7-9' union all
select N'3',N'楼主',N'2005-7-7'
select * from @tb as a
where checksum(会员,产品,时间)
in(select top 3 checksum(会员,产品,时间) from @tb where a.会员=会员 order by 时间 desc)
order by 会员,时间 desc,产品/*
--------------------
1 菠萝 2008-5-5
1 凤梨 1999-9-9
1 西瓜 1999-1-2
2 MP3 2008-2-2
2 手机 2006-7-9
2 U盘 2003-5-7
3 车子 2005-7-9
3 楼主 2005-7-7
*/
if object_id('tmepdb.dbo.#t') is not null
drop table #tcreate table #t([id] int,product varchar(10),[date] datetime)
insert into #t
select '1','苹果','1999-1-1' union all
select '1','西瓜','1999-1-2' union all
select '1','凤梨','1999-9-9' union all
select '1','菠萝','2008-5-5' union all
select '1','橘子','1998-8-4' union all
select '2','电脑','2002-1-1' union all
select '2','U盘','2003-5-7' union all
select '2','MP3','2008-2-2' union all
select '2','手机','2006-7-9' union all
select '3','车子','2005-7-9'select a.* from #t a where date in (
select top 3 date from #t where id=a.id order by date desc
) drop table #t/*
id product date
----------- ---------- ------------------------------------------------------
1 西瓜 1999-01-02 00:00:00.000
1 凤梨 1999-09-09 00:00:00.000
1 菠萝 2008-05-05 00:00:00.000
2 U盘 2003-05-07 00:00:00.000
2 MP3 2008-02-02 00:00:00.000
2 手机 2006-07-09 00:00:00.000
3 车子 2005-07-09 00:00:00.000
*/
insert into tb select 1 , '西瓜' , '1999-1-2'
insert into tb select 1 , '凤梨' , '1999-9-9'
insert into tb select 1 , '菠萝' , '2008-5-5'
insert into tb select 1 , '橘子' , '1998-8-4'
insert into tb select 2 , '电脑' , '2002-1-1'
insert into tb select 2 , 'U盘' , '2003-5-7'
insert into tb select 2 , 'MP3' , '2008-2-2'
insert into tb select 2 , '手机' , '2006-7-9'
insert into tb select 3 , '车子' , '2005-7-9'select A.* from tb A where product in (
select top 3 product from tb where id = A.id order by dt desc )/*
1 苹果 1999-01-01 00:00:00.000
1 西瓜 1999-01-02 00:00:00.000
1 凤梨 1999-09-09 00:00:00.000
2 电脑 2002-01-01 00:00:00.000
2 U盘 2003-05-07 00:00:00.000
2 MP3 2008-02-02 00:00:00.000
3 车子 2005-07-09 00:00:00.000*/drop table tb
go
create table tb(id int,product varchar(10),dt datetime)insert into tb select 1 , '苹果' , '1999-1-1'
insert into tb select 1 , '西瓜' , '1999-1-2'
insert into tb select 1 , '凤梨' , '1999-9-9'
insert into tb select 1 , '菠萝' , '2008-5-5'
insert into tb select 1 , '橘子' , '1998-8-4'
insert into tb select 2 , '电脑' , '2002-1-1'
insert into tb select 2 , 'U盘' , '2003-5-7'
insert into tb select 2 , 'MP3' , '2008-2-2'
insert into tb select 2 , '手机' , '2006-7-9'
insert into tb select 3 , '车子' , '2005-7-9'
goselect id,product
from tb t
where product in (select top 3 product from tb where id=t.id order by dt desc)