我写的一条SQL语句select top 5 Id,Name,LanguageEh,ImageAdd,IsSell,NowSale,IsFrame,IsSpecials,IsOrder from V_F_PaintList where LanguageEh='EN' and IsSell=1 and IsFrame=0 and IsOrder=0 and IsHomeShow=1 Order by TPost desc得到下面这个;
我现在只要让它ID相同的只显示第一个;要怎么改那句SQL呢;
我现在只要让它ID相同的只显示第一个;要怎么改那句SQL呢;
(select top 5 row_number() over(partition by id order by TPost desc) as num,Id,Name,LanguageEh,ImageAdd,IsSell,
NowSale,IsFrame,IsSpecials,IsOrder
from V_F_PaintList
where LanguageEh='EN' and IsSell=1 and IsFrame=0 and IsOrder=0 and IsHomeShow=1 ) b
where num=1
select Id,Name,LanguageEh,ImageAdd,IsSell,NowSale,IsFrame,IsSpecials,IsOrder,ROW_NUMBER()over(partition by id order by id) as rn
from V_F_PaintList
where LanguageEh='EN' and IsSell=1 and IsFrame=0 and IsOrder=0 and IsHomeShow=1 Order by TPost desc) a
where rn=1
select Id,Name,LanguageEh,ImageAdd,IsSell,max(NowSale),IsFrame,IsSpecials,IsOrder
from V_F_PaintList
where LanguageEh='EN' and IsSell=1 and IsFrame=0 and IsOrder=0 and IsHomeShow=1
group by Id,Name,LanguageEh,ImageAdd,IsSell,IsFrame,IsSpecials,IsOrder
Order by TPost desc
)t
SELECT TOP 5
Id ,
Name ,
LanguageEh ,
ImageAdd ,
IsSell ,
IsFrame ,
IsSpecials ,
IsOrder
FROM V_F_PaintList
WHERE LanguageEh = 'EN'
AND IsSell = 1
AND IsFrame = 0
AND IsOrder = 0
AND IsHomeShow = 1)
GROUP BY Id,NAME,LanguageEh,ImageAdd,IsSell,IsFrame,IsSpecials,IsOrder
select top 5 distinct Id,Name,LanguageEh,ImageAdd,IsSell,NowSale,IsFrame,IsSpecials,IsOrder from V_F_PaintList where LanguageEh='EN' and IsSell=1 and IsFrame=0 and IsOrder=0 and IsHomeShow=1 Order by TPost desc
WITH cte AS
(
SELECT Id ,
Name ,
LanguageEh ,
ImageAdd ,
IsSell ,
IsFrame ,
IsSpecials ,
IsOrder,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY GETDATE())iii
FROM V_F_PaintList
WHERE LanguageEh = 'EN'
AND IsSell = 1
AND IsFrame = 0
AND IsOrder = 0
AND IsHomeShow = 1)
GROUP BY Id,NAME,LanguageEh,ImageAdd,IsSell,IsFrame,IsSpecials,IsOrder
)
SELECT TOP 5
Id ,
Name ,
LanguageEh ,
ImageAdd ,
IsSell ,
IsFrame ,
IsSpecials ,
IsOrder
FROM cte
WHERE iii = 1
insert into @t
select 1 , 'A' , 30 union
select 2 , 'a' , 10 union
select 3 , 'a' , -5 union
select 4 , 'B' , 20 union
select 5 , 'b' , 100 union
select 6 , 'C' , 1 union
select 7 , 'c' , 98 select 序号,case when 序号=(select min(序号) from @t where 货物=a.货物) then 货物 else '' end as 货物, 数量
from @t a
order by 序号/*
序号 货物 数量
----------- -------------------- -----------
1 A 30
2 10
3 -5
4 B 20
5 100
6 C 1
7 98(所影响的行数为 7 行)
*/