select top 4 * from tab order by case when cityid = 2001 then 0 else 1 end,id
select top 4 * from tb order by case when cityID=2001 then 0 else 1 end
select top 4 * from tb order by case when cityID= 2001 then 0 else 1 end
select top 4 * from tab order by case when cityid = 2001 then 0 else 1 end,newid()
select top 4 * from 表A order by (case when cityID=2001 then 0 else 1 end)
select top 4 * from tb order by (case when cityID>=2001 then city else 2001*2-city end)
可能我自己也有些混乱吧,问题没有描述的很清楚。 现在我有两个解决方案一: SELECT NAME FROM A WHERE CityId = 2001 --SELECT recrowcount=@@ROWCOUNT; IF @@ROWCOUNT >4 SELECT TOP 4 Name FROM A cpgb WITH (NOLOCK) WHERE CityId = 2001 ELSE SELECT TOP 4 Name FROM (SELECT Name FROM A WHERE CityId = 2001 UNION SELECT top 4 Name FROM A WHERE CityId <> 221 ) CG这样再加上 业务的条件的话,会很复杂 二:逻辑在后台代码中判断 先查询出 SELECT top 4 Name FROM A WHERE CityId = 2001 判断 结果的行数 rowCount ,如果小于4 则 查询 SELECT top (4-rowCount) Name FROM A WHERE CityId <> 221 然后拼凑dt
from tab
order by case when cityid = 2001 then 0 else 1 end,id
from tab
order by case when cityid = 2001 then 0 else 1 end,newid()
select top 4 * from 表A
order by (case when cityID=2001 then 0 else 1 end)
order by (case when cityID>=2001 then city else 2001*2-city end)
现在我有两个解决方案一:
SELECT NAME
FROM A
WHERE CityId = 2001
--SELECT recrowcount=@@ROWCOUNT;
IF @@ROWCOUNT >4
SELECT TOP 4 Name
FROM A cpgb
WITH (NOLOCK)
WHERE CityId = 2001
ELSE
SELECT TOP 4 Name
FROM
(SELECT Name
FROM A
WHERE CityId = 2001
UNION
SELECT top 4 Name
FROM
A
WHERE CityId <> 221
) CG这样再加上 业务的条件的话,会很复杂
二:逻辑在后台代码中判断
先查询出 SELECT top 4 Name
FROM A
WHERE CityId = 2001
判断 结果的行数 rowCount ,如果小于4
则 查询 SELECT top (4-rowCount) Name
FROM
A
WHERE CityId <> 221
然后拼凑dt