我写的一条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呢;那些条件不变的;
and Id in (select max(Id) from V_F_PaintList group by [Name])
Order by TPost desc
and NowSell = (select max(NowSell) from V_F_PaintList where id=t1.id)
Order by TPost desc
and NowSell = (select min(NowSell) from V_F_PaintList where id=t1.id)
Order by TPost desc
--我已测试过是没有问题的
--你这里Where条件太多了,我没有写,你自己加上吧,
--可以加在最后,其它条件可以根据你的需求自己来搞定
--另外,字段我也没写全,太多。麻烦
SELECT TOP 5 Id,
(CASE WHEN COUNT(id)>0
THEN
(SELECT TOP 1 Name FROM V_F_PaintList ur2 WHERE ur2.id=ur.id)
ELSE
NULL
END) AS Name,
(CASE WHEN COUNT(id)>0
THEN
(SELECT TOP 1 LanguageEh FROM V_F_PaintList ur2 WHERE ur2.id=ur.id)
ELSE
NULL
END) AS LanguageEh,
(CASE WHEN COUNT(id)>0
THEN
(SELECT TOP 1 ImageAdd FROM V_F_PaintList ur2 WHERE ur2.id=ur.id)
ELSE
NULL
END) AS ImageAdd,
(CASE WHEN COUNT(id)>0
THEN
(SELECT TOP 1 ImageAdd FROM V_F_PaintList ur2 WHERE ur2.id=ur.id)
ELSE
NULL
END) AS ImageAdd
FROM V_F_PaintList ur
同问ID不是唯一的??
--我已测试过是没有问题的
--你这里Where条件太多了,我没有写,你自己加上吧,
--可以加在最后,其它条件可以根据你的需求自己来搞定
--另外,字段我也没写全,太多。麻烦
SELECT TOP 5 Id,
(CASE WHEN COUNT(id)>0
THEN
(SELECT TOP 1 Name FROM V_F_PaintList ur2 WHERE ur2.id=ur.id)
ELSE
NULL
END) AS Name,
(CASE WHEN COUNT(id)>0
THEN
(SELECT TOP 1 LanguageEh FROM V_F_PaintList ur2 WHERE ur2.id=ur.id)
ELSE
NULL
END) AS LanguageEh,
(CASE WHEN COUNT(id)>0
THEN
(SELECT TOP 1 ImageAdd FROM V_F_PaintList ur2 WHERE ur2.id=ur.id)
ELSE
NULL
END) AS ImageAdd,
(CASE WHEN COUNT(id)>0
THEN
(SELECT TOP 1 ImageAdd FROM V_F_PaintList ur2 WHERE ur2.id=ur.id)
ELSE
NULL
END) AS ImageAdd
FROM V_F_PaintList ur
GROUP BY ur.id ORDER BY ur.id