某彩票网站的一个数据表T_DATA有7个数字字段(F1,F2~F7),用于存储彩民购买的彩票上的7个号码(顺序按数字从小到大分别放在F1~F7中):
T_DATA | |||||||
FNAME | F1 | F2 | F3 | F4 | F5 | F6 | F7 |
张三 | 1 | 3 | 5 | 12 | 21 | 22 | 30 |
李四 | 4 | 6 | 12 | 14 | 24 | 28 | 29 |
… | … | … | … | … | … | … | … |
摇奖摇出了一组号码也是7个数字:
3,12,14,17,19,22,30
只要有4个号码符合的用户就会获奖,请使用一条符合SQL92规范的语句把中奖的用户查询出来。
declare mycur cursor scroll for select id,fname,f1,f2,f3,f4,f5,f6,f7 from T_DATA
open mycur
declare @fid int;
declare @fname varchar(50);
declare @f1 int;
declare @f2 int;
declare @f3 int;
declare @f4 int;
declare @f5 int;
declare @f6 int;
declare @f7 int;
declare @count int;
fetch first from mycur into @fid,@fname,@f1,@f2,@f3,@f4,@f5,@f6,@f7;
while (@@fetch_status = 0)
begin
set @count=0;
if(@f1 in(3,12,14,17,19,22,30)) SET @count = @count + 1;
if( @f2 in(3,12,14,17,19,22,30)) SET @count = @count + 1;
if( @f3 in(3,12,14,17,19,22,30)) SET @count = @count + 1;
if( @f4 in(3,12,14,17,19,22,30)) SET @count = @count + 1;
if(@f5 in(3,12,14,17,19,22,30)) SET @count = @count + 1;
if(@f6 in(3,12,14,17,19,22,30)) SET @count = @count + 1;
if( @f7 in(3,12,14,17,19,22,30)) SET @count = @count + 1;
if(@count>=4)
print(@fname);
fetch next from mycur into @fid,@fname,@f1,@f2,@f3,@f4,@f5,@f6,@f7;
end
close mycur;