62,046
社区成员
发帖
与我相关
我的任务
分享
int winRedCount = userNums.Where(u => winNums.Contains(u)).Count();
switch (winRedCount)
{
case 4:
score = isWinBlue ? 100 : 20;
break;
case 5:
score = isWinBlue ? 300 : 100;
break;
case 6:
score = isWinBlue ? 600 : 500;
break;
default:
score = isWinBlue ? 20 : 0;
break;
}
use test
if object_id('tb') is not null drop table tb
GO
create table tb([id] int identity(1,1),Hong1 int,Hong2 int,Hong3 int ,Hong4 int, Hong5 int,Hong6 int ,Lan int, Qishu varchar(8),userName nvarchar(20))
Insert into tb
select 3,5,8,15,16,18,10,'2011036','zhangsan' union all
select 5,9,13,18,22,24,6,'2011036','lisi' union all
select 2,7,11,20,24,28,9,'2011036','lisi' union all
select 2,6,11,19,23,25,12,'2011036','王五' union all
select 6,11,16,19,22,26,4,'2011036','王五' union all
select 8,11,20,22,24,30,5,'2011036','王五'
--2,11,20,22,24,31 5
--得分记录表
--如果是:0+1,1+1,2+1,得5分,
--如果是:4+0,3+1 得20分
--如果是:4+1,5+0 得100分
--如果是:5+1 得300分
--如果是:6+0 得500分
--如果是:6+1 得1000分
if object_id('tb1') is not null drop table tb1
GO
create table tb1(sumHong int,sumLan int,score int)
insert into tb1
select 0,1,5 union all
select 1,1,5 union all
select 2,1,5 union all
select 3,1,20 union all
select 4,0,20 union all
select 4,1,100 union all
select 5,0,100 union all
select 6,0,500 union all
select 6,1,1000
;with cte as
(
select [id] ,userName, [球类型] , [号码] from tb unpivot ([号码] for [球类型] in(Hong1,Hong2,Hong3,Hong4,Hong5,Hong6,Lan)) t
)
,cte2 as
(
select distinct [id],userName,
'红球中奖数量'=(select count(*) from cte where [号码]in(2,11,20,22,24,31) and c.[id]=[id]),
'篮球中奖数量'=(select count(*) from cte where [号码]in(5) and c.[id]=[id])
from cte c
)
select a.*,[分数]=isnull(b.score,0) from cte2 a left join tb1 b on a.[红球中奖数量]=b.sumHong and a.[篮球中奖数量]=b.sumLan
--查询结果
--(6 行受影响)
--
--(9 行受影响)
--id userName 红球中奖数量 篮球中奖数量 分数
------------- -------------------- ----------- ----------- -----------
--1 zhangsan 0 1 5
--4 王五 2 0 0
--5 王五 2 0 0
--2 lisi 2 1 5
--3 lisi 4 0 20
--6 王五 4 1 100
--
--(6 行受影响)
ID Hong1 Hong2 Hong3 Hong4 Hong5 Hong6 Lanqiu qishu UserName res
----------- ----- ----- ----- ----- ----- ----- ------ -------- -------------------------------------------------- --------------------------------------------------
1 03 05 08 15 16 18 08 2011036 Zhangsan 0 +0积分:0
2 05 09 03 18 22 24 06 2011036 Lisi 1 +0积分:0
3 02 07 11 20 24 28 09 2011036 Lisi 4 +0积分:0
4 06 06 11 19 23 25 12 2011036 王五 1 +0积分:0
5 06 11 16 19 22 26 04 2011036 王五 2 +0积分:0
6 08 11 20 22 24 30 05 2011036 王五 4 +1积分:0
Create function getResult(@ID int,@honghao varchar(50),@lanhao varchar(5))
Returns nvarchar(50)
As
Begin
declare @res varchar(20)
declare @H int
declare @L int
declare @jifen int
declare @UserName varchar(20)
select @UserName=UserName,
@H=
case when charindex(Hong1,@honghao)>0 then 1 else 0 end +
case when charindex(Hong2,@honghao)>0 then 1 else 0 end +
case when charindex(Hong3,@honghao)>0 then 1 else 0 end +
case when charindex(Hong4,@honghao)>0 then 1 else 0 end +
case when charindex(Hong5,@honghao)>0 then 1 else 0 end,
@L=case when Lanqiu=@lanhao then 1 else 0 end from TableName
where ID=@ID
set @res=Convert(nvarchar(2),@H)+'+'+Convert(nvarchar(2),@L)
if(@res='0+1' or @res='1+1' or @res='2+1')
set @jifen=5;
if(@res='4+0' or @res='3+1')
set @jifen=20
if(@res='4+1' or @res='5+0')
set @jifen=100
if(@res='5+1')
set @jifen=300
if(@res='6+0')
set @jifen=500
if(@res='6+1')
set @jifen=1000
Return @res+'积分:'+isNull(convert(varchar(5),@jifen),0)
End
select *,dbo.getResult(ID,'02,11,20,22,24,30','05') as res from tableName
//using System.Linq;
static void Main(string[] args)
{
List<int> winNums = new List<int> { 3, 5, 8, 15, 16, 18 };
Console.Write("本期中奖号码:");
winNums.ForEach(w => Console.Write(w + " "));
List<int> userNums = new List<int> { 3, 5, 8, 15, 35 };
Console.Write("\r\n用户投注号码:");
userNums.ForEach(u => Console.Write(u + " "));
int winBlue = 8, userBlue = 8, score = 0;
bool isWinBlue = winBlue == userBlue;
int winRedCount = userNums.Where(u => winNums.Contains(u)).Count();
if (winBlue == userBlue)
{
switch (winRedCount)
{
case 4:
score = isWinBlue ? 100 : 20;
break;
case 5:
score = isWinBlue ? 300 : 100;
break;
case 6:
score = isWinBlue ? 600 : 500;
break;
default:
score = isWinBlue ? 20 : 0;
break;
}
}
Console.WriteLine("\r\n用户得分:{0}", score);
Console.ReadLine();
}
//购买的红球列表
List<string> a = new List<string>();
//开奖好吗
List<string> b = new List<string>();
//购买的篮球
string c;
//开奖篮球
string d;
int cnt = a.FindAll(new Predicate<string>(delegate(string v) { return b.Contains(v); })).Count;
string flag = cnt.ToString() + "+" + d.Equals(c) ? "1" : "0";
int f = 0;//分数
switch (flag)
{
case "0+1":
case "1+1":
case "2+1":
f = 5;
break;
case "4+0":
case "3+1":
f = 20;
break;
case "4+1":
case "5+0":
f = 100;
break;
case "5+1":
f = 300;
break;
case "6+0":
f = 500;
break;
case "6+1":
f = 1000;
break;
default:
f = 0;
break;
}