彩票中奖

彩票网站的一个数据表Lottery表有7个字段（F1-F7），用于存储彩民购买的彩票上的7个号码（顺序按数字从小到大分别放在F1-F7中）。

Fname	F1	F2	F3	F4	F5	F6	F7
张三	1	3	5	12	21	22	30
李四	4	6	12	14	24	28	29
...	...	...	...	...	...	...	...

 

 

 

 

摇奖摇出了一组号码也是7个数字：1  3  14  17  19  22  30。只要有4个号码符合的用户就会获奖。

--创建测试表
create table Lottery(
Fname nvarchar(20),
F1 int,
F2 int,
F3 int,
F4 int,
F5 int,
F6 int,
F7 int
)
--插入测试数据
insert Lottery
select '張三','1','3','5','12','21','22','30'
union all
select '李四','10','32','25','2','23','22','30'
union all
select '李白','12','33','25','16','1','42','13'

--方法一
select Fname from (
select Fname,F1,null as F2,null as F3,null as F4,null as F5,null as F6,null as F7 from Lottery
union
select Fname,null,F2,null,null,null,null,null from Lottery
union
select Fname,null,null,F3,null,null,null,null from Lottery
union
select Fname,null,null,null,F4,null,null,null from Lottery
union
select Fname,null,null,null,null,F5,null,null from Lottery
union
select Fname,null,null,null,null,null,F6,null from Lottery
union
select Fname,null,null,null,null,null,null,F7 from Lottery
) as a 
where F1=1 or F2=3 or F3=14 or F4=17 or F5=19 or F6=22
or F7=30
group by Fname 
having COUNT(*)>=4

--方法二
select Fname from (
select fname,Name,Guess from Lottery unpivot(Guess
for Name in(F1,F2,F3,F4,F5,F6,F7)) t
)as a where (Name='F1' and Guess=1)
or (Name='F2' and Guess=3)
or (Name='F3' and Guess=14)
or (Name='F4' and Guess=17)
or (Name='F5' and Guess=19)
or (Name='F6' and Guess=22)
or (Name='F7' and Guess=30)
group by Fname 
having COUNT(*)>=4