1 官网下载mysql免安装版,修改my.ini为
[mysql]
default-character-set = UTF8MB4
[mysqld]
basedir ="C:\Users\huang\Downloads\mysql-8.0.20-winx64"
datadir ="C:\Users\huang\Downloads\mysql-8.0.20-winx64\data"
port=3306
max_connections = 20
character-set-server = UTF8MB4
default-storage-engine = INNODB
default_authentication_plugin = mysql_native_password
[client]
default-character-set = UTF8MB4
进入mysql后将密码修改为root,basedir/datadir应修改为自己的实际路径。再确认C:\Windows\System32\drivers\etc\host文件中有127.0.0.1 localhost这一行。
2 测试用双色球数据
2020050 04 09 17 20 32 33 15 2020-06-14 1101983169 379409292 9664099 5 323895 90 3000 1305 200 61232 10 1226803 5 8175729
2020051 03 06 08 11 19 28 08 2020-06-16 1014976572 342968302 5442583 23 47478 268 3000 3689 200 146306 10 2075975 5 11213884
2020052 02 08 13 29 32 33 15 2020-06-18 1085336824 352815504 10000000 2 568303 53 3000 707 200 49881 10 1105323 5 5849766
2020053 02 14 15 16 32 33 01 2020-06-21 1057074616 381666534 6277278 19 84264 360 3000 1404 200 58928 10 1018510 5 7898492
2020054 03 10 19 25 26 31 02 2020-06-23 1050374135 338817016 6596678 11 104544 210 3000 1818 200 80778 10 1444247 5 8430192
2020055 01 05 07 23 28 30 12 2020-06-25 1009674637 327140694 5761052 14 67264 198 3000 2860 200 124578 10 1889224 5 10927492
2020056 02 05 08 12 26 31 14 2020-06-28 984522456 380233782 6280167 17 146255 186 3000 1223 200 72751 10 1416500 5 9023227
2020057 09 14 21 23 26 32 03 2020-06-30 1019998705 349147892 10000000 3 193145 113 3000 1334 200 68019 10 1289604 5 10655792
2020058 01 03 11 12 19 26 07 2020-07-02 958212978 356052050 5569985 18 94299 136 3000 2821 200 107734 10 1708942 5 15213521
2020059 02 04 10 17 22 25 14 2020-07-05 1002178704 386807160 10000000 4 147308 190 3000 1485 200 88573 10 1671444 5 7739433
2020060 05 09 14 20 24 30 08 2020-07-07 837071557 356937202 5238209 38 20424 554 3000 4357 200 164149 10 2321290 5 12105159
2020061 08 17 24 26 27 31 04 2020-07-09 903858043 366447904 10000000 1 281269 91 3000 1118 200 66022 10 1327960 5 9467898
2020062 10 14 17 22 26 27 05 2020-07-12 932509919 391827962 8901954 5 259438 94 3000 1220 200 63319 10 1218439 5 13187729
2020063 12 15 16 22 29 32 14 2020-07-14 991892696 357076054 10000000 3 289264 103 3000 769 200 50919 10 1099317 5 6461252
2020064 01 03 07 21 27 32 01 2020-07-16 1063056063 356057386 10000000 1 351356 77 3000 919 200 56606 10 1140698 5 8153023
2020065 09 15 18 21 23 26 08 2020-07-19 1090832907 381062334 8838315 5 269544 89 3000 1454 200 70971 10 1281044 5 11879201
2020066 02 09 13 17 26 28 07 2020-07-21 1057341835 352149702 6006277 15 137720 137 3000 2116 200 85857 10 1486743 5 11739149
2020067 04 07 09 23 27 30 08 2020-07-23 1061790227 353153516 6979941 10 176780 140 3000 1518 200 69948 10 1299104 5 8502701
2020068 12 16 21 26 27 32 10 2020-07-26 996910195 376630286 5430373 17 71448 128 3000 3605 200 117265 10 1780262 5 19179292
2020069 03 09 10 13 18 26 04 2020-07-28 1016346427 343151528 7827856 7 249936 99 3000 1278 200 67529 10 1276366 5 7813164
2020070 01 02 04 06 19 21 15 2020-07-30 953784945 343442594 5829067 23 238356 100 3000 951 200 55811 10 1144050 5 9497682
2020071 09 11 12 13 22 23 08 2020-08-02 931954524 363190440 6156653 12 130449 133 3000 2094 200 93662 10 1588088 5 13533760
2020072 06 08 10 15 17 26 04 2020-08-04 945569865 335806408 7525472 7 212479 104 3000 1837 200 80731 10 1505907 5 7887468
2020073 05 07 11 13 27 29 03 2020-08-06 949682723 344984126 7005129 8 155436 129 3000 1913 200 95278 10 1702038 5 9404409
2020074 04 08 09 13 19 33 12 2020-08-09 974585503 375478220 8629293 5 211991 107 3000 1690 200 83978 10 1572292 5 11132696
2020075 03 11 13 20 24 30 16 2020-08-11 1031374526 342593198 10000000 1 155685 143 3000 1703 200 72291 10 1374269 5 9101749
2020076 10 15 16 18 20 27 06 2020-08-13 1033084399 346015916 6895903 8 231207 82 3000 1378 200 83353 10 1385076 5 11811261
2020077 03 10 16 21 25 27 12 2020-08-16 960537693 376430570 5841121 27 87079 326 3000 1359 200 64947 10 1148379 5 8469869
2020078 03 11 14 16 21 32 04 2020-08-18 1029746619 339766446 10000000 1 347407 76 3000 885 200 45994 10 1001391 5 7801189
2020079 05 12 20 21 22 29 14 2020-08-20 1058784536 347031342 8578055 6 146641 183 3000 1179 200 57916 10 1177078 5 7162542
2020080 14 15 18 22 31 33 01 2020-08-23 1122276717 375199530 10000000 2 397581 70 3000 1011 200 54230 10 1096781 5 9535610
3 vs2019新建.net core console项目,通过NuGet添加
Microsoft.EntityFrameworkCore //ef core
Microsoft.EntityFrameworkCore.Design //用来在nuget console
Microsoft.EntityFrameworkCore.Tools //中管理数据迁移
Pomelo.EntityFrameworkCore.MySql //mysql
Microsoft.Extensions.Logging.Debug //查看efcore生成的sql
4 准备table class
[Table(name:"Balls")]
public class LottoBalls
{
/// <summary>
/// 期号
/// </summary>
[Key]
public int Draw { get; set; }
/// <summary>
/// 红球1
/// </summary>
[Column(TypeName = "TINYINT(2) ZEROFILL")]
public int Red1 { get; set; }
/// <summary>
/// 红球2
/// </summary>
[Column(TypeName = "TINYINT(2) ZEROFILL")]
public int Red2 { get; set; }
/// <summary>
/// 红球3
/// </summary>
[Column(TypeName = "TINYINT(2) ZEROFILL")]
public int Red3 { get; set; }
/// <summary>
/// 红球4
/// </summary>
[Column(TypeName = "TINYINT(2) ZEROFILL")]
public int Red4 { get; set; }
/// <summary>
/// 红球5
/// </summary>
[Column(TypeName = "TINYINT(2) ZEROFILL")]
public int Red5 { get; set; }
/// <summary>
/// 红球6
/// </summary>
[Column(TypeName = "TINYINT(2) ZEROFILL")]
public int Red6 { get; set; }
/// <summary>
/// 蓝球
/// </summary>
[Column(TypeName = "TINYINT(2) ZEROFILL")]
public int Blue { get; set; }
/// <summary>
/// 开奖日期
/// </summary>
[Column(TypeName = "DATE")]
public DateTime DrawDate { get; set; }
public LottoPrize Prize { get; set; }
}
[Table(name: "Prize")]
public class LottoPrize
{
/// <summary>
/// 期号
/// </summary>
[Key]
public int Draw { get; set; }
/// <summary>
/// 奖池金额
/// </summary>
[Column(TypeName = "INT UNSIGNED")]
public int PoolAmount { get; set; }
/// <summary>
/// 销售额
/// </summary>
[Column(TypeName = "INT UNSIGNED")]
public int SaleAmount { get; set; }
/// <summary>
/// 一等奖金额
/// </summary>
[Column(TypeName = "INT UNSIGNED")]
public int Prize1 { get; set; }
/// <summary>
/// 一等奖注数
/// </summary>
[Column(TypeName = "SMALLINT UNSIGNED")]
public int Prize1Bet { get; set; }
/// <summary>
/// 二等奖金额
/// </summary>
[Column(TypeName = "MEDIUMINT UNSIGNED")]
public int Prize2 { get; set; }
/// <summary>
/// 二等奖注数
/// </summary>
[Column(TypeName = "SMALLINT UNSIGNED")]
public int Prize2Bet { get; set; }
/// <summary>
/// 三等奖金额
/// </summary>
[Column(TypeName = "SMALLINT UNSIGNED")]
public int Prize3 { get; set; }
/// <summary>
/// 三等奖注数
/// </summary>
[Column(TypeName = "MEDIUMINT UNSIGNED")]
public int Prize3Bet { get; set; }
/// <summary>
/// 四等奖金额
/// </summary>
[Column(TypeName = "TINYINT UNSIGNED")]
public int Prize4 { get; set; }
/// <summary>
/// 四等奖注数
/// </summary>
[Column(TypeName = "MEDIUMINT UNSIGNED")]
public int Prize4Bet { get; set; }
/// <summary>
/// 五等奖金额
/// </summary>
[Column(TypeName = "TINYINT UNSIGNED")]
public int Prize5 { get; set; }
/// <summary>
/// 五等奖注数
/// </summary>
[Column(TypeName = "MEDIUMINT UNSIGNED")]
public int Prize5Bet { get; set; }
/// <summary>
/// 六等奖金额
/// </summary>
[Column(TypeName = "TINYINT UNSIGNED")]
public int Prize6 { get; set; }
/// <summary>
/// 六等奖注数
/// </summary>
[Column(TypeName = "INT UNSIGNED")]
public int Prize6Bet { get; set; }
/// <summary>
/// 开奖日期
/// </summary>
[Column(TypeName = "DATE")]
public DateTime DrawDate { get; set; }
public LottoBalls Balls { get; set; }
}
将开奖号码和中奖信息分为两个表,两个表共享主键实现one-to-one。
5 创建自己的DbContext
public class LottoContext : DbContext
{
public DbSet<LottoBalls> Balls { get; set; }
public DbSet<LottoPrize> Prize { get; set; }
//output to debug
public static readonly LoggerFactory LoggerFactory =
new LoggerFactory(new[] { new DebugLoggerProvider() });
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseLoggerFactory(LoggerFactory);
optionsBuilder.UseMySql("server=localhost;userid=root;pwd=root;port=3306;database=unionlotto");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//one-to-one
modelBuilder.Entity<LottoBalls>()
.HasOne(b => b.Prize)
.WithOne(p => p.Balls)
.HasForeignKey<LottoPrize>(p => p.Draw);
modelBuilder.Entity<LottoBalls>()
.Property(b => b.Red1)
.IsRequired();
modelBuilder.Entity<LottoBalls>()
.Property(b => b.Red2)
.IsRequired();
modelBuilder.Entity<LottoBalls>()
.Property(b => b.Red3)
.IsRequired();
modelBuilder.Entity<LottoBalls>()
.Property(b => b.Red4)
.IsRequired();
modelBuilder.Entity<LottoBalls>()
.Property(b => b.Red5)
.IsRequired();
modelBuilder.Entity<LottoBalls>()
.Property(b => b.Red6)
.IsRequired();
modelBuilder.Entity<LottoBalls>()
.Property(b => b.Blue)
.IsRequired();
modelBuilder.Entity<LottoBalls>()
.Property(b => b.DrawDate)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.PoolAmount)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.SaleAmount)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize1)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize1Bet)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize2)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize2Bet)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize3)
.HasDefaultValue(3000);
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize3Bet)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize4)
.HasDefaultValue(200);
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize4Bet)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize5)
.HasDefaultValue(10);
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize5Bet)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize6)
.HasDefaultValue(5);
modelBuilder.Entity<LottoPrize>()
.Property(p => p.Prize6Bet)
.IsRequired();
modelBuilder.Entity<LottoPrize>()
.Property(p => p.DrawDate)
.IsRequired();
}
}
6 打开NuGet console,输入
Add-Migratiion Init
Update-Database
在cmd console中进入Mysql查看数据库(unionlotto)以及表(balls、prize)是否已创建。
7 添加数据
public static void InitialDatabase()
{
var str = @"2020050 04 09 17 20 32 33 15 2020-06-14 1101983169 379409292 9664099 5 323895 90 3000 1305 200 61232 10 1226803 5 8175729
2020051 03 06 08 11 19 28 08 2020-06-16 1014976572 342968302 5442583 23 47478 268 3000 3689 200 146306 10 2075975 5 11213884
2020052 02 08 13 29 32 33 15 2020-06-18 1085336824 352815504 10000000 2 568303 53 3000 707 200 49881 10 1105323 5 5849766
2020053 02 14 15 16 32 33 01 2020-06-21 1057074616 381666534 6277278 19 84264 360 3000 1404 200 58928 10 1018510 5 7898492
2020054 03 10 19 25 26 31 02 2020-06-23 1050374135 338817016 6596678 11 104544 210 3000 1818 200 80778 10 1444247 5 8430192
2020055 01 05 07 23 28 30 12 2020-06-25 1009674637 327140694 5761052 14 67264 198 3000 2860 200 124578 10 1889224 5 10927492
2020056 02 05 08 12 26 31 14 2020-06-28 984522456 380233782 6280167 17 146255 186 3000 1223 200 72751 10 1416500 5 9023227
2020057 09 14 21 23 26 32 03 2020-06-30 1019998705 349147892 10000000 3 193145 113 3000 1334 200 68019 10 1289604 5 10655792
2020058 01 03 11 12 19 26 07 2020-07-02 958212978 356052050 5569985 18 94299 136 3000 2821 200 107734 10 1708942 5 15213521
2020059 02 04 10 17 22 25 14 2020-07-05 1002178704 386807160 10000000 4 147308 190 3000 1485 200 88573 10 1671444 5 7739433
2020060 05 09 14 20 24 30 08 2020-07-07 837071557 356937202 5238209 38 20424 554 3000 4357 200 164149 10 2321290 5 12105159
2020061 08 17 24 26 27 31 04 2020-07-09 903858043 366447904 10000000 1 281269 91 3000 1118 200 66022 10 1327960 5 9467898
2020062 10 14 17 22 26 27 05 2020-07-12 932509919 391827962 8901954 5 259438 94 3000 1220 200 63319 10 1218439 5 13187729
2020063 12 15 16 22 29 32 14 2020-07-14 991892696 357076054 10000000 3 289264 103 3000 769 200 50919 10 1099317 5 6461252
2020064 01 03 07 21 27 32 01 2020-07-16 1063056063 356057386 10000000 1 351356 77 3000 919 200 56606 10 1140698 5 8153023
2020065 09 15 18 21 23 26 08 2020-07-19 1090832907 381062334 8838315 5 269544 89 3000 1454 200 70971 10 1281044 5 11879201
2020066 02 09 13 17 26 28 07 2020-07-21 1057341835 352149702 6006277 15 137720 137 3000 2116 200 85857 10 1486743 5 11739149
2020067 04 07 09 23 27 30 08 2020-07-23 1061790227 353153516 6979941 10 176780 140 3000 1518 200 69948 10 1299104 5 8502701
2020068 12 16 21 26 27 32 10 2020-07-26 996910195 376630286 5430373 17 71448 128 3000 3605 200 117265 10 1780262 5 19179292
2020069 03 09 10 13 18 26 04 2020-07-28 1016346427 343151528 7827856 7 249936 99 3000 1278 200 67529 10 1276366 5 7813164
2020070 01 02 04 06 19 21 15 2020-07-30 953784945 343442594 5829067 23 238356 100 3000 951 200 55811 10 1144050 5 9497682
2020071 09 11 12 13 22 23 08 2020-08-02 931954524 363190440 6156653 12 130449 133 3000 2094 200 93662 10 1588088 5 13533760
2020072 06 08 10 15 17 26 04 2020-08-04 945569865 335806408 7525472 7 212479 104 3000 1837 200 80731 10 1505907 5 7887468
2020073 05 07 11 13 27 29 03 2020-08-06 949682723 344984126 7005129 8 155436 129 3000 1913 200 95278 10 1702038 5 9404409
2020074 04 08 09 13 19 33 12 2020-08-09 974585503 375478220 8629293 5 211991 107 3000 1690 200 83978 10 1572292 5 11132696
2020075 03 11 13 20 24 30 16 2020-08-11 1031374526 342593198 10000000 1 155685 143 3000 1703 200 72291 10 1374269 5 9101749
2020076 10 15 16 18 20 27 06 2020-08-13 1033084399 346015916 6895903 8 231207 82 3000 1378 200 83353 10 1385076 5 11811261
2020077 03 10 16 21 25 27 12 2020-08-16 960537693 376430570 5841121 27 87079 326 3000 1359 200 64947 10 1148379 5 8469869
2020078 03 11 14 16 21 32 04 2020-08-18 1029746619 339766446 10000000 1 347407 76 3000 885 200 45994 10 1001391 5 7801189
2020079 05 12 20 21 22 29 14 2020-08-20 1058784536 347031342 8578055 6 146641 183 3000 1179 200 57916 10 1177078 5 7162542
2020080 14 15 18 22 31 33 01 2020-08-23 1122276717 375199530 10000000 2 397581 70 3000 1011 200 54230 10 1096781 5 9535610";
var lottos = str.Split(Environment.NewLine)
.Where(str => !string.IsNullOrWhiteSpace(str))
.Select(str => str.Split("\t"));
using(var context = new LottoContext())
{
AddDataRange(context, lottos);
}
}
private static void AddDataRange(LottoContext context, IEnumerable<string[]> lottos)
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
foreach (var lotto in lottos)
{
var balls = ExtractBalls(lotto);
var prize = ExtractPrize(lotto);
context.Balls.Add(balls);
context.Prize.Add(prize);
}
context.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
}
}
public static LottoBalls ExtractBalls(string[] lotto)
=> new LottoBalls
{
Draw = int.Parse(lotto[0]),
Red1 = int.Parse(lotto[1]),
Red2 = int.Parse(lotto[2]),
Red3 = int.Parse(lotto[3]),
Red4 = int.Parse(lotto[4]),
Red5 = int.Parse(lotto[5]),
Red6 = int.Parse(lotto[6]),
Blue = int.Parse(lotto[7]),
DrawDate = DateTime.Parse(lotto[8])
};
public static LottoPrize ExtractPrize(string[] lotto)
=> new LottoPrize
{
Draw = int.Parse(lotto[0]),
PoolAmount = int.Parse(lotto[9]),
SaleAmount = int.Parse(lotto[10]),
Prize1 = int.Parse(lotto[11]),
Prize1Bet = int.Parse(lotto[12]),
Prize2 = int.Parse(lotto[13]),
Prize2Bet = int.Parse(lotto[14]),
Prize3 = int.Parse(lotto[15]),
Prize3Bet = int.Parse(lotto[16]),
Prize4 = int.Parse(lotto[17]),
Prize4Bet = int.Parse(lotto[18]),
Prize5 = int.Parse(lotto[19]),
Prize5Bet = int.Parse(lotto[20]),
Prize6 = int.Parse(lotto[21]),
Prize6Bet = int.Parse(lotto[22]),
DrawDate = DateTime.Parse(lotto[8])
};
8 UnitTest
[TestMethod]
public async Task TestMethod1()
{
using (var context = new LottoContext())
{
var query = from lotto in context.Balls
select $"{lotto.Draw}\t{lotto.Red1}\t{lotto.Red2}\t" +
$"{lotto.Red3}\t{lotto.Red4}\t{lotto.Red5}\t" +
$"{lotto.Red6}\t{lotto.DrawDate}";
Debug.WriteLine(string.Join(Environment.NewLine, query));
}
}
运行测试就能够看到上面输入的数据了,在调试窗口还能看到efcore翻译的sql语句。
SELECT `b`.`Draw`, `b`.`Blue`, `b`.`DrawDate`, `b`.`Red1`, `b`.`Red2`, `b`.`Red3`, `b`.`Red4`, `b`.`Red5`, `b`.`Red6`
FROM `Balls` AS `b`
EFCore 使用sql语句查询
//无参sql语句
//最后一期
var query = context.Balls.FromSqlRaw("SELECT * FROM balls ORDER BY draw DESC LIMIT 1");
//带参数的sql语句
//期号大于2020070的
var _draw = 2020070;
query = context.Balls.FromSqlInterpolated($"SELECT * FROM balls WHERE draw > {_draw}");
EFCore 上述几种查询的限制是只能返回表的所有列数据。
如果要统计红球三分区数据时就得借助sql udf。EFCore 只能映射scalar udf,就是那种只返回一个值的db function。根据官网文档,udf 的EFCore映射方法声明在DbContext类中,方法名字与udf一致且默认不区分大小写,返回值类型和参数类型要和udf一致,参数名可以随意。sql udf如下,通过context.Database.ExecuteSqlRaw方法添加到数据库
//udf开关,设为on才能添加udf
context.Database.ExecuteSqlRaw(@"SET global log_bin_trust_function_creators = on");
//红球三分区
context.Database.ExecuteSqlRaw(@"DROP FUNCTION IF EXISTS redp3count;
CREATE FUNCTION redp3count(red1 INT, red2 INT, red3 INT, red4 INT, red5 INT, red6 INT, flag INT)
RETURNS INT
BEGIN
DECLARE Count INT DEFAULT 0;
IF (red1 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
IF (red2 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
IF (red3 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
IF (red4 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
IF (red5 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
IF (red6 - 1) DIV 11 = flag THEN SET Count = Count + 1; END IF;
RETURN Count;
END");
在LottoContext中添加映射方法,方法带DbFunction注释
public class LottoContext : DbContext
{
......
/// <summary>
/// 红球三分区个数
/// </summary>
/// <param name="index">0: 第一区,1: 第二区,2: 第三区</param>
[DbFunction]
public static int RedP3Count(int red1, int red2, int red3, int red4, int red5, int red6, int index)
=> throw new NotImplementedException();
......
}
如果想知道红球三区个数
var query = from lotto in context.Balls
select new
{
draw = lotto.Draw,
p1 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
lotto.Red5, lotto.Red6, 0),
p2 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
lotto.Red5, lotto.Red6, 1),
p3 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
lotto.Red5, lotto.Red6, 2)
} into t
select $"{t.draw}\t{t.p1}:{t.p2}:{t.p3}";
Debug.WriteLine(string.Join(Environment.NewLine, query));
结果如下:
2020050 2:2:2
2020051 4:1:1
2020052 2:1:3
2020053 1:3:2
2020054 2:1:3
2020055 3:0:3
2020056 3:1:2
2020057 1:2:3
2020058 3:2:1
2020059 3:2:1
2020060 2:2:2
2020061 1:1:4
2020062 1:3:2
2020063 0:4:2
2020064 3:1:2
2020065 1:3:2
2020066 2:2:2
2020067 3:0:3
2020068 0:3:3
2020069 3:2:1
2020070 4:2:0
2020071 2:3:1
2020072 3:2:1
2020073 3:1:2
2020074 3:2:1
2020075 2:2:2
2020076 1:4:1
2020077 2:2:2
2020078 2:3:1
2020079 1:4:1
2020080 0:4:2
如果想知道哪些三区个数现的次数多些
var query = from lotto in context.Balls
select new
{
p1 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
lotto.Red5, lotto.Red6, 0),
p2 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
lotto.Red5, lotto.Red6, 1),
p3 = RedP3Count(lotto.Red1, lotto.Red2, lotto.Red3, lotto.Red4,
lotto.Red5, lotto.Red6, 2)
} into t1
group t1 by new { t1.p1, t1.p2, t1.p3} into g
select new { Key = g.Key, Count = g.Count() } into t2
orderby t2.Count descending
select $"{t2.Key.p1}:{t2.Key.p2}:{t2.Key.p3}\t{t2.Count}";
Debug.WriteLine(string.Join(Environment.NewLine, query));
结果如下:
2:2:2 5
3:2:1 5
1:3:2 3
3:1:2 3
2:1:3 2
3:0:3 2
0:4:2 2
2:3:1 2
1:4:1 2
4:1:1 1
1:2:3 1
1:1:4 1
0:3:3 1
4:2:0 1
如果想统计红球频率
var query = from red in (from lotto in context.Balls
select lotto.Red1)
.Concat(from lotto in context.Balls
select lotto.Red2)
.Concat(from lotto in context.Balls
select lotto.Red3)
.Concat(from lotto in context.Balls
select lotto.Red4)
.Concat(from lotto in context.Balls
select lotto.Red5)
.Concat(from lotto in context.Balls
select lotto.Red6)
group red by red into g
select new { red = g.Key, count = g.Count() } into t
orderby t.red
select $"{t.red}\t{t.count}";
Debug.WriteLine(string.Join(Environment.NewLine, query));
结果如下:
1 4
2 6
3 8
4 5
5 5
6 3
7 4
8 6
9 9
10 7
11 6
12 6
13 7
14 6
15 6
16 6
17 6
18 4
19 5
20 5
21 8
22 6
23 5
24 3
25 3
26 11
27 8
28 3
29 4
30 4
31 4
32 8
33 5
如果想通过sql view来查询红球频率,首先通过sql语句添加view
//红球频率
context.Database.ExecuteSqlRaw(@"DROP VIEW IF EXISTS RedFreq;
CREATE VIEW RedFreq AS
SELECT t.red, COUNT(*) AS count
FROM (SELECT red1 AS red FROM balls UNION ALL
SELECT red2 AS red FROM balls UNION ALL
SELECT red3 AS red FROM balls UNION ALL
SELECT red4 AS red FROM balls UNION ALL
SELECT red5 AS red FROM balls UNION ALL
SELECT red6 AS red FROM balls
) AS t
GROUP BY t.red
ORDER BY t.red");
接着新建类RedFreq并在LottoContext里添加映射
public class RedFreq
{
public int Red { get; set; }
public int Count { get; set; }
}
public class LottoContext : DbContext
{
......
public DbSet<RedFreq> RedFreq { get; set; }
......
}
接着在LottoContext的OnModelCreating方法里添加
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
......
modelBuilder.Entity<RedFreq>(rf => {
rf.ToView("RedFreq");
rf.HasNoKey();
rf.Property(e => e.Red).HasColumnName("red");
rf.Property(e => e.Count).HasColumnName("count");
});
......
}
这样通过如下代码可以得到相同结果
var query = from freq in context.RedFreq
select $"{freq.Red}\t{freq.Count}";
Debug.WriteLine(string.Join(Environment.NewLine, query));