表A和表B
A:opID, x,y
B: op, ....
其中op对应opID,是一样的要求找出op的值,符合以下条件
1:A中 opID 必须符合 xx=1,超过一个时取y最小者
2: B中 按op分组,取个数最小者
3:将op赋值给@op
A:opID, x,y
B: op, ....
其中op对应opID,是一样的要求找出op的值,符合以下条件
1:A中 opID 必须符合 xx=1,超过一个时取y最小者
2: B中 按op分组,取个数最小者
3:将op赋值给@op
将op赋值给@op --变量@op哪来的?
A:opID, x,y
B: op, ....
其中op对应opID,即A.opID=B.op(int)要求找出op的值,符合以下条件
1: A中 opID 必须符合 A.x=1
2: B中 按op分组统计个数,取个数最小者
3: 超过一个时取A.y最小者然后将op赋值给@op(事先已声明)
A opID x y
1 1 1
2 2 1
3 1 2B op u
1 0
2 1
1 1
1 3
2 2表A中符合条件1的有1和3
表B中个数最少的为2,但2不符合条件1,最后求出的应是1
@bFax bit
AS
DECLARE @op SmallInt
DECLARE @Tb Table(op int, C int)INSERT @Tb
SELECT op,Count(*) AS C
FROM B INNER JOIN A
ON B.op=A.opID WHERE A.X=1
GROUP BY op
ORDER BY CSELECT TOP 1 @op=op FROM @TbRETURN (@op)
GO
insert into A
select 1,1,1
union all
select 2,2,1
union all
select 3,1,2create table B (op int,u int)
insert into B
select 1,0
union all
select 2,1
union all
select 1,1
union all
select 1,3
union all
select 2,2select * from
(select opid
from A
group by opid
union
select count(op) as op from B
group by op) aa
where opid=1
AS
DECLARE @op SmallIntSELECT @op=T.op FROM(
SELECT op,Count(*) AS C,A.y
FROM B INNER JOIN A
ON B.op=A.opID WHERE A.X=1
GROUP BY op,A,y
ORDER BY C,A.y)RETURN (@op)
GO
--@bFax bit @bFax起什么作用
AS
DECLARE @op SmallIntselect @op=op from
(select top 1 op,Count(*) AS C
from B INNER JOIN A ON B.op=A.opID
WHERE A.X=1 GROUP BY op ORDER BY C)TBRETURN (@op)
GO
--@bFax bit @bFax起什么作用
AS
DECLARE @op SmallIntselect @op=op from
(select top 1 op,Count(*) AS C
from B INNER JOIN A ON B.op=A.opID
WHERE A.X=1 GROUP BY op ORDER BY C)TBRETURN (@op)
GO