有一表查询
字段
ID MID code mr
1 112 23756 0
2 112 23758 0
2 113 23005 1
3 113 23006 0
MID中112是同类，113也是同类。现在查询MID或code字段，如果mr为0则查询出任意一条记录，
如mr=1则查询出mr=1的那条记录。
比方查112或23756（23758）查到记录
1 112 23756 0
或者
2 112 23758 0
都可
如果查113或23005（23006）则查出
2 113 23005 1
------解决思路----------------------
select top 1 ID,MID,code,mr from tb where mid=113 order by mr desc
------解决思路----------------------
------解决思路----------------------
create table 表1
(ID int,MID int,code int,mr int)
insert into 表1
select 1,112,23756,0 union all
select 2,112,23758,0 union all
select 2,113,23005,1 union all
select 3,113,23006,0
-- 查112或23756（23758）
declare @mid int,@code int
select @mid=112,@code=23756
select top 1 *
from 表1
where MID=@mid or code=@code
order by case when mr=1 then 0 else cast(rand()*100 as int) end
/*
ID MID code mr
----------- ----------- ----------- -----------
2 112 23758 0
(1 row(s) affected)
*/
-- 查113或23005（23006）
declare @mid int,@code int
select @mid=113,@code=23005
select top 1 *
from 表1
where MID=@mid or code=@code
order by case when mr=1 then 0 else cast(rand()*100 as int) end
/*
ID MID code mr
----------- ----------- ----------- -----------
2 113 23005 1
(1 row(s) affected)
*/