商品编号(id),所属商铺(spid), 是否推荐(tj,int类型,1为是,0为否), 推荐时间(tjsj,时间类型)id spid tj tjsj
1 1 1 2009-05-01
2 1 1 2009-05-02
3 1 1 2009-05-03
1 2 1 2009-05-01
2 2 1 2009-05-02
3 2 1 2009-05-03
1 2 1 2009-05-01
2 2 1 2009-05-01
3 2 1 2009-05-01
结果
重复SPID的数据,根据时间或id取2条。
id spid tj tjsj
2 1 1 2009-05-02
3 1 1 2009-05-03
2 2 1 2009-05-02
3 2 1 2009-05-03
2 2 1 2009-05-01
3 2 1 2009-05-01获取所有 tj=1 和 每个店铺 最新推荐的2个商品 如果时间一样,就获取同1spid 最大的2个
1 1 1 2009-05-01
2 1 1 2009-05-02
3 1 1 2009-05-03
1 2 1 2009-05-01
2 2 1 2009-05-02
3 2 1 2009-05-03
1 2 1 2009-05-01
2 2 1 2009-05-01
3 2 1 2009-05-01
结果
重复SPID的数据,根据时间或id取2条。
id spid tj tjsj
2 1 1 2009-05-02
3 1 1 2009-05-03
2 2 1 2009-05-02
3 2 1 2009-05-03
2 2 1 2009-05-01
3 2 1 2009-05-01获取所有 tj=1 和 每个店铺 最新推荐的2个商品 如果时间一样,就获取同1spid 最大的2个
DECLARE @t TABLE (id int,spid int,tj int,tjsj datetime)
INSERT INTO @t
SELECT
1 , 1 , 1 , '2009-05-01' UNION ALL SELECT
2 , 1 , 1 , '2009-05-02' UNION ALL SELECT
3 , 1 , 1 , '2009-05-03' UNION ALL SELECT
1 , 2 , 1 , '2009-05-01' UNION ALL SELECT
2 , 2 , 1 , '2009-05-02' UNION ALL SELECT
3 , 2 , 1 , '2009-05-03' UNION ALL SELECT
1 , 2 , 1 , '2009-05-01' UNION ALL SELECT
2 , 2 , 1 , '2009-05-01' UNION ALL SELECT
3 , 2 , 1 , '2009-05-01' SELECT * FROM @t aWHERE id IN (
SELECT TOP 2 id
FROM @t
WHERE tj=1
AND spid=a.spid
ORDER BY tjsj DESC ,id DESC
)
/*
2 1 1 2009-05-02 00:00:00.000
3 1 1 2009-05-03 00:00:00.000
2 2 1 2009-05-02 00:00:00.000
3 2 1 2009-05-03 00:00:00.000
2 2 1 2009-05-01 00:00:00.000
3 2 1 2009-05-01 00:00:00.000
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[spid] int,[tj] int,[tjsj] datetime)
insert [tb]
select 1,1,1,'2009-05-01' union all
select 2,1,1,'2009-05-02' union all
select 3,1,1,'2009-05-03' union all
select 1,2,1,'2009-05-01' union all
select 2,2,1,'2009-05-02' union all
select 3,2,1,'2009-05-03' union all
select 1,2,1,'2009-05-01' union all
select 2,2,1,'2009-05-01' union all
select 3,2,1,'2009-05-01'
---查询---
select
*
from
tb t
where
tj=1
and
(select count(1) from tb where SPID=t.SPID and ((tjsj>=t.tjsj) or (tjsj=t.tjsj and id>=t.id)))<=2
---结果---id spid tj tjsj
----------- ----------- ----------- ------------------------------------------------------
2 1 1 2009-05-02 00:00:00.000
3 1 1 2009-05-03 00:00:00.000
2 2 1 2009-05-02 00:00:00.000
3 2 1 2009-05-03 00:00:00.000(所影响的行数为 4 行)
不是每个店铺两个吗?
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[spid] int,[tj] int,[tjsj] datetime)
insert [tb]
select 1,1,1,'2009-05-01' union all
select 2,1,1,'2009-05-02' union all
select 3,1,1,'2009-05-03' union all
select 1,2,1,'2009-05-01' union all
select 2,2,1,'2009-05-02' union all
select 3,2,1,'2009-05-03' union all
select 1,2,1,'2009-05-01' union all
select 2,2,1,'2009-05-01' union all
select 3,2,1,'2009-05-01'
---查询---
select *
from tb
where id
in
(
select id
from tb t
where tj=1
and (select count(1) from tb where SPID=t.SPID and ((tjsj>=t.tjsj) or (tjsj=t.tjsj and id>=t.id)))<=2
)---结果---
id spid tj tjsj
----------- ----------- ----------- ------------------------------------------------------
2 1 1 2009-05-02 00:00:00.000
3 1 1 2009-05-03 00:00:00.000
2 2 1 2009-05-02 00:00:00.000
3 2 1 2009-05-03 00:00:00.000
2 2 1 2009-05-01 00:00:00.000
3 2 1 2009-05-01 00:00:00.000(所影响的行数为 6 行)
功能是实现了,不过没有考虑效率的问题,估计效率不会高
貌似结果有点问题
declare @table table (id int,spid int,tj int,tjsj datetime)
insert into @table
select 1,1,1,'2009-05-04 20:00:00' union all
select 2,1,1,'2009-05-04 23:00:00' union all
select 3,1,0,'2009-05-04 23:58:00' union all
select 4,3,1,'2009-05-03 14:00:00' union all
select 5,3,1,'2009-05-03 21:00:00' union all
select 6,3,1,'2009-05-02 20:00:00' union all
select 7,2,1,'2009-05-02 07:00:00' union all
select 8,4,1,'2009-05-01 04:00:00' union all
select 9,4,1,'2009-05-01 03:00:00' union all
select 10,2,1,'2009-05-01 01:00:00' union all
select 11,4,1,'2009-04-30 06:00:00' union all
select 12,1,1,'2009-04-30 09:00:00' union all
select 13,6,1,'2009-04-28 20:00:00' union all
select 14,6,1,'2009-04-27 13:00:00' union all
select 15,1,0,'2009-04-27 10:00:00' union all
select 16,5,1,'2009-04-23 02:00:00' select
*
from
@table t
where
tj=1
and
(select count(1) from @table where SPID=t.SPID and ((tjsj>=t.tjsj) or (tjsj=t.tjsj and id>=t.id)))<=2
/*
id spid tj tjsj
----------- ----------- ----------- -----------------------
2 1 1 2009-05-04 23:00:00.000
4 3 1 2009-05-03 14:00:00.000
5 3 1 2009-05-03 21:00:00.000
7 2 1 2009-05-02 07:00:00.000
8 4 1 2009-05-01 04:00:00.000
9 4 1 2009-05-01 03:00:00.000
10 2 1 2009-05-01 01:00:00.000
13 6 1 2009-04-28 20:00:00.000
14 6 1 2009-04-27 13:00:00.000
16 5 1 2009-04-23 02:00:00.000
*/
go
create table [tb]([id] int,[spid] int,[tj] int,[tjsj] datetime)
insert [tb]
select 1,1,1,'2009-05-01' union all
select 2,1,1,'2009-05-02' union all
select 3,1,1,'2009-05-03' union all
select 1,2,1,'2009-05-01' union all
select 2,2,1,'2009-05-02' union all
select 3,2,1,'2009-05-03' union all
select 1,2,1,'2009-05-01' union all
select 2,2,1,'2009-05-01' union all
select 3,2,1,'2009-05-01'
go
--select * from [tb]select id,spid,tj,tjsj
from
(
select *,rn=row_number() over(partition by spid,tjsj order by id desc)
from tb
where tj=1
) t
where rn<=2
/*
id spid tj tjsj
----------- ----------- ----------- -----------------------
1 1 1 2009-05-01 00:00:00.000
2 1 1 2009-05-02 00:00:00.000
3 1 1 2009-05-03 00:00:00.000
3 2 1 2009-05-01 00:00:00.000
2 2 1 2009-05-01 00:00:00.000
2 2 1 2009-05-02 00:00:00.000
3 2 1 2009-05-03 00:00:00.000(7 行受影响)
*/
每个商铺只选2个商品,所以只取2条了.
如果同spid的tjsj一样,则依取ID最大的2条.
如果同spid的tjsj不一样,则取tjsj最新的2条.希望我没理解错.
我被经验误导了,需求有点怪异……
如果同spid的tjsj一样,则依取ID最大的2条.
如果同spid的tjsj不一样,则取tjsj最新的2条. 是这样的
子查询里应该还要加一个条件 tj=1[code=SQL
select
*
from
@table t
where
tj=1
and
(select count(1) from @table where tj=1 and SPID=t.SPID and ((tjsj>=t.tjsj) or (tjsj=t.tjsj and id>=t.id)))<=2/**
id spid tj tjsj
----------- ----------- ----------- ------------------------------------------------------
1 1 1 2009-05-04 20:00:00.000
2 1 1 2009-05-04 23:00:00.000
4 3 1 2009-05-03 14:00:00.000
5 3 1 2009-05-03 21:00:00.000
7 2 1 2009-05-02 07:00:00.000
8 4 1 2009-05-01 04:00:00.000
9 4 1 2009-05-01 03:00:00.000
10 2 1 2009-05-01 01:00:00.000
13 6 1 2009-04-28 20:00:00.000
14 6 1 2009-04-27 13:00:00.000
16 5 1 2009-04-23 02:00:00.000(所影响的行数为 11 行)
**/[/code]
楼主的这个问题是.net版的