这个问题我上无发了一个帖子,可能是我没说清问题,问题没有完全解决,把早上的帖子结了 在这里重新发一下
会员表(commany) : 用户名(greenname) 用户等级 (type1)
供应表(busiess): id 发布时间(date) 用户名(greenname) 要求查询条件:查询出最近2天之内的用户等级>2(type1>2)发的最新信息(及每个用户只取一条)这些信息先按用户等级(type1)排序 再按发布时间(date)排序 把除去条件1 查询出来的剩下所有记录按发布时间排序接在条件1 查询结果后面
commany
greenname type1
aa 1
bb 3
cc 4
dd 4
busiess
id greenname date
1 aa 2008.12.12
2 bb 2007.11.11
3 cc 2008.12.12
4 bb 2008.12.12
5 cc 2008.12.11
6 bb 2008.12.11
7 dd 2008.12.11
我一步一步来说明这个查询要求
查询出最近2天之内的信息,有 (1,3,4,5,6,7) 然后是 用户等级>2 有(3,4,5,6,7)
然后每个用户只取一条最新信息有(3,4,7)这些信息按照会员等级和发布时间排序结果是(4,7,3)
在把剩下的 (1,2,5,6)按发布时间排序接在接在上面查出来的信息后面
最后的结果就是这样的 4,7,3, 1,6,5,2
会员表(commany) : 用户名(greenname) 用户等级 (type1)
供应表(busiess): id 发布时间(date) 用户名(greenname) 要求查询条件:查询出最近2天之内的用户等级>2(type1>2)发的最新信息(及每个用户只取一条)这些信息先按用户等级(type1)排序 再按发布时间(date)排序 把除去条件1 查询出来的剩下所有记录按发布时间排序接在条件1 查询结果后面
commany
greenname type1
aa 1
bb 3
cc 4
dd 4
busiess
id greenname date
1 aa 2008.12.12
2 bb 2007.11.11
3 cc 2008.12.12
4 bb 2008.12.12
5 cc 2008.12.11
6 bb 2008.12.11
7 dd 2008.12.11
我一步一步来说明这个查询要求
查询出最近2天之内的信息,有 (1,3,4,5,6,7) 然后是 用户等级>2 有(3,4,5,6,7)
然后每个用户只取一条最新信息有(3,4,7)这些信息按照会员等级和发布时间排序结果是(4,7,3)
在把剩下的 (1,2,5,6)按发布时间排序接在接在上面查出来的信息后面
最后的结果就是这样的 4,7,3, 1,6,5,2
我就加了限制要求最近2天高级会员发的,并且每个用户只取最新发布的一条(这里应该去max(date)而不是max(id)),然后把这些信息按照会员等级
和发布时间排序。接着把上面查询出来的信息之外的所有信息按照发布时间排在上面查询出来的信息后面。
CREATE TABLE commany
(
greenname VARCHAR(100),
type1 int
)
GO
INSERT commany
select 'aa',1
union all select 'bb',3
union all select 'cc',4
union all select 'dd',4CREATE TABLE busiess
(
id INT,
greenname VARCHAR(100),
date DATETIME
)
GO
INSERT busiess
select 1,'aa','2008.12.12'
union all select 2,'bb','2007.11.11'
union all select 3,'cc','2008.12.12'
union all select 4,'bb','2008.12.12'
union all select 5,'cc','2008.12.11'
union all select 6,'bb','2008.12.11'
union all select 7,'dd','2008.12.11'SELECT b3.id FROM [busiess] b3
LEFT JOIN
(
SELECT id,ROW_NUMBER() OVER(ORDER BY t2.type1 desc, t2.date desc) AS rn FROM [busiess] b2
JOIN
(
SELECT c.greenname, c.type1, MAX(date) date FROM commany c JOIN [busiess] b ON c.greenname = b.[greenname]
WHERE DATEDIFF(DAY, date, GETDATE()) <= 1 AND type1>2
GROUP BY c.greenname, c.type1
) t2 ON b2.[greenname] = t2.greenname AND b2.[date] = t2.date
)t3 ON b3.id = t3.id
ORDER BY t3.rn desc, b3.date desc
DROP TABLE commany
DROP TABLE busiess
真乱,你用的正序还是反序???
然后每个用户只取一条最新信息有(3,4,7)这些信息按照会员等级和发布时间排序结果是(4,7,3) 那这个应该是374吧,
3 cc 2008.12.12
4 bb 2008.12.12
7 dd 2008.12.11
go
create table commany(greenname varchar(10), type1 int)
insert commany select 'aa' , 1
insert commany select 'bb' , 3
insert commany select 'cc' , 4
insert commany select 'dd' , 4
if object_id('busiess')is not null drop table busiess
go
create table busiess ( id int, greenname varchar(10), date datetime)
insert busiess select 1 , 'aa' , '2008.12.12'
insert busiess select 2 , 'bb' , '2008.11.11'
insert busiess select 3 , 'cc' , '2008.12.12'
insert busiess select 4 , 'bb' , '2008.12.12'
insert busiess select 5 , 'cc' , '2008.12.11'
insert busiess select 6 , 'bb', '2008.12.11'
insert busiess select 7, 'dd', '2008.12.11'select b.*
from busiess b join commany c on b.greenname=c.greenname
order by case when (not exists(select 1 from busiess where greenname=b.greenname and date>b.date)) and c.type1>2
then 0 else 1 end
, type1 desc, date descdrop table busiess, commany
查询出最近2天之内的信息,有 (1,3,4,5,6,7) 然后是 用户等级>2 有(3,4,5,6,7)
然后每个用户只取一条最新信息有(3,4,7)这些信息按照会员等级和发布时间排序结果是(3,7,4)
在把剩下的 (1,2,5,6)按发布时间排序接在接在上面查出来的信息后面
最后的结果就是这样的 3,7,4, 1,6,5,2
go
create table commany(greenname varchar(10), type1 int)
insert commany select 'aa' , 1
insert commany select 'bb' , 3
insert commany select 'cc' , 4
insert commany select 'dd' , 4
if object_id('busiess')is not null drop table busiess
go
create table busiess ( id int, greenname varchar(10), date datetime)
insert busiess select 1 , 'aa' , '2008.12.12'
insert busiess select 2 , 'bb' , '2008.11.11'
insert busiess select 3 , 'cc' , '2008.12.12'
insert busiess select 4 , 'bb' , '2008.12.12'
insert busiess select 5 , 'cc' , '2008.12.11'
insert busiess select 6 , 'bb', '2008.12.11'
insert busiess select 7, 'dd', '2008.12.11'select b.*
from busiess b join commany c on b.greenname=c.greenname
order by (case when (not exists(select 1 from busiess where greenname=b.greenname and date>b.date)) and c.type1>2
then type1 else -1 end) desc
, date desc, type1 /*
id greenname date
----------- ---------- -----------------------
3 cc 2008-12-12 00:00:00.000
7 dd 2008-12-11 00:00:00.000
4 bb 2008-12-12 00:00:00.000
1 aa 2008-12-12 00:00:00.000
6 bb 2008-12-11 00:00:00.000
5 cc 2008-12-11 00:00:00.000
2 bb 2008-11-11 00:00:00.000(7 行受影响)
*/drop table busiess, commany
select b.*
from busiess b join commany c on b.greenname=c.greenname
order by (case when (not exists(select 1 from busiess where greenname=b.greenname and date>b.date))
and c.type1>2 and datediff(d, date, getdate())<=2
then type1 else -1 end) desc
, date desc, type1