sql查询前20条数据

[啾啾我]

select * from 
(
	select [Id],[Mid],[Name],[Picture],[Status],[Hot],[Tops],[CreateTime],ids=ROW_NUMBER()
	over(partition by Mid order by CreateTime desc) from Product where Mid in (5,7,6,13)
)a where ids<15 and [Status]>0 and [Tops]>0 order by CreateTime desc

上面这条sql查询是查询 Mid等于 5,6,7,13数据前14条数据
问题：my sql，我想查询Mid=5 or 7的各前14条数据和Mid=6 or 13的各前6条数据，一条sql查询出来怎么做

[中国风]

MYSQL，可尝试 e.g.

select * from 
(
    select [Id],[Mid],[Name],[Picture],[Status],[Hot],[Tops],[CreateTime],@rowcount:=if(Mid=@Mid,@rowcount+1,1) as ids,@Mid=Mid from Product where Mid in (5,7,6,13) ORDER BY Mid,CreateTime des
)a where ids<15 and [Status]>0 and [Tops]>0 order by CreateTime DESC

[啾啾我]

引用 1 楼 roy_88 的回复:

MYSQL，可尝试 e.g.

select * from 
(
    select [Id],[Mid],[Name],[Picture],[Status],[Hot],[Tops],[CreateTime],@rowcount:=if(Mid=@Mid,@rowcount+1,1) as ids,@Mid=Mid from Product where Mid in (5,7,6,13) ORDER BY Mid,CreateTime des
)a where ids<15 and [Status]>0 and [Tops]>0 order by CreateTime DESC

我想查询Mid=5 or 7的各前14条数据 Mid=6 or 13的各前6条数据 你这个执行有错误！

[中国风]

ids<15把这条件自己动手改就行了 ------ 有什么错误？

[中国风]

给你测个例子，参照以下例子改 e.g.

create temporary table Tab10(ID int unique auto_increment,Score int);
#drop table Tab10

insert into Tab10(Score) values(5),(10),(15);
select 
*
from (
select 
a.*,@rowcount:=if(a.Score=@Score,@rowcount+1,1) as RN,@Score:=a.Score
from (select * from Tab10 order by score,ID) as a) as t
where RN<=2 and Score=5 or RN<=3 and Score=10 or RN<=5 and Score=15;

显示结果

# ID, Score, RN, @Score:=a.Score
'1', '5', '1', '5'
'4', '5', '2', '5'
'2', '10', '1', '10'
'5', '10', '2', '10'
'8', '10', '3', '10'
'3', '15', '1', '15'
'6', '15', '2', '15'
'9', '15', '3', '15'
'12', '15', '4', '15'
'15', '15', '5', '15'

[啾啾我]

引用 4 楼 roy_88 的回复:

给你测个例子，参照以下例子改 e.g.

create temporary table Tab10(ID int unique auto_increment,Score int);
#drop table Tab10

insert into Tab10(Score) values(5),(10),(15);
select 
*
from (
select 
a.*,@rowcount:=if(a.Score=@Score,@rowcount+1,1) as RN,@Score:=a.Score
from (select * from Tab10 order by score,ID) as a) as t
where RN<=2 and Score=5 or RN<=3 and Score=10 or RN<=5 and Score=15;

显示结果

# ID, Score, RN, @Score:=a.Score
'1', '5', '1', '5'
'4', '5', '2', '5'
'2', '10', '1', '10'
'5', '10', '2', '10'
'8', '10', '3', '10'
'3', '15', '1', '15'
'6', '15', '2', '15'
'9', '15', '3', '15'
'12', '15', '4', '15'
'15', '15', '5', '15'

刚接触数据库，mysql已经ok了，sql server 中怎么实现呢，大神能帮忙用sql server实现一下吗？

[中国风]

SQL SERVER用你的语句就行了，把ids<15改为同mysql一样的条件 注意：你的结果集是其它条件前还是其它条件后 前在内嵌表里加条件同伪列一起生成结果集，条件后用你的语句在WHERE 改就行了

[啾啾我]

引用 6 楼 roy_88 的回复:

SQL SERVER用你的语句就行了，把ids<15改为同mysql一样的条件 注意：你的结果集是其它条件前还是其它条件后 前在内嵌表里加条件同伪列一起生成结果集，条件后用你的语句在WHERE 改就行了

自己太菜了，sql server还是没有搞好。

[中国风]

测测看，是不是这样效果

SELECT  *
FROM    ( SELECT    [Id] ,
                    [Mid] ,
                    [Name] ,
                    [Picture] ,
                    [Status] ,
                    [Hot] ,
                    [Tops] ,
                    [CreateTime] ,
                    ids = ROW_NUMBER() OVER ( PARTITION BY Mid ORDER BY CreateTime DESC )
          FROM      Product
          WHERE     Mid IN ( 5, 7, 6, 13 )
                    AND [Status] > 0
                    AND [Tops] > 0
        ) a
WHERE   ids <= 14
        AND Mid IN ( 5, 7 )
        OR ids <= 6
        AND Mid IN ( 6, 13 )
ORDER BY CreateTime DESC;