select * from
(select t1.no,t1.qty* t2.qty as qty
from tab t1 join tab t2 on t1.no=t2.no
group by t1.no,t1.qty* t2.qty
having count(*)>1
union all
select no,min(qty)as qty from tab group by no having count(*)=1)t
order by no
(select t1.no,t1.qty* t2.qty as qty
from tab t1 join tab t2 on t1.no=t2.no
group by t1.no,t1.qty* t2.qty
having count(*)>1
union all
select no,min(qty)as qty from tab group by no having count(*)=1)t
order by no
RETURNS int
AS
BEGIN
DECLARE @RESULT INT
SELECT @RESULT=1
SELECT @RESULT=@RESULT*QTY FROM TAB WHERE NO=@NO
RETURN(@RESULT)
END
GO
SELECT DISTINCT NO,DBO.F1(NO) FROM TAB
马上给分!!!