sql.txt

对已往的3D彩票号码进行统计

select * from List where (d1-d2=-1 and d2-d3=-1) or (d1-d2=1 and d2-d3=1)
选择所有顺子
select * from List where d1=d2 or d2=d3 or d1=d3
select * from list where s1=0
选择所有组3
select uid,d1+d2+d3 as Dsum,d1,d2,d3 from list
列出和值
select (d1+d2+d3) as 和值,count(*) as 数量 from list group by (d1+d2+d3) 
和值统计
select sum(d1) as D1,sum(d2) as D2,sum(d3) as D3 from  List
各位数值统计
select uid,d1,d2,d3,iif(d1=d2 ,d1,iif(d1=d3,d1,d2)) as 组数,iif(d1=d2 ,d3,iif(d1=d3,d2,d1))  as 合数 from list where s1=0
列出组三的组数,合数

select iif(d1=d2 ,d1,iif(d1=d3,d1,d2)) as 组数,count(*) as 数量  from list where s1=0 group by iif(d1=d2 ,d1,iif(d1=d3,d1,d2))
统计各组数的数量
select uid,d1,d2,d3,iif(d1=d2 ,d1,iif(d1=d3,d1,d2)) as 组数 from list where s2>77 and s2<101 and s1=0
天，23 期里有16期组3



select  d1,d2,d3 from list where d1 <>0 order by d1,d2,d3

select d3,count(*) from list group by d3
计算各数在D3位出现的次数
select d2,count(*) from list group by d2
计算各数在D2位出现的次数
select d1,count(*) from list group by d1
计算各数在D1位出现的次数

select * from list where d1=3 or d2=3 or d3=3
查询每个数出现的情况

select iif(s1=1,1,iif(d1=d2 ,iif( d1=3,2,1),iif(d2=d3 ,iif( d2=3,2,1), iif( d1=3,2,1)) )) from list where d1=3 or d2=3 or d3=3

select sum(sumd) from (select iif(s1=1,1,iif(d1=d2 ,iif( d1=3,2,1),iif(d2=d3 ,iif( d2=3,2,1), iif( d1=3,2,1)) ))  as sumd from list where d1=3 or d2=3 or d3=3)
取某数出现次数

select count(* ) as d3 from List where d1=3
union all select count(* ) as d3 from List where d2=3
union all select count(* ) as d3 from List where d3=3

select a.*,b.M2 from  (select d1,count(*) as M1 from list  group by d1) a
left join( select d2,count(*) as M2 from list   group by d2 ) b on b.d2=a.d1

select a.*,d.M2,d.M3 ,M1+M2+M3 as AllTime from  (select d1,count(*) as M1 from list  group by d1) a
left join( 
     select b.*,c.M3 from  (select d2,count(*) as M2 from list  group by d2) b
     left join( select d3,count(*) as M3 from list   group by d3 ) c on b.d2=c.d3) d on d.d2=a.d1
综合查询各个数字出现的次数

update list set s1=0 where d1=d2 or d1=d3 or d2=d3
更新数据
update list set Num=d1*100+d2*10+d3
update list set cost = 20 where s2= 303

select d1,d2,d3 from mynum 
minus
(
select a.d1,a.d2,a.d3 from mynum a,list b 
where a.d1=b.d1 and a.d2= b.d2 and a.d3=b.d3
)
select a.qishu,b.uid,a.d1,a.d2,a.d3 from mynum a,list b 
where a.d1=b.d1 and a.d2= b.d2 and a.d3=b.d3

update list set road=(d1 mod 3)*100+(d2 mod 3)*10 +(d3 mod 3)


select count(*) from list  where road mod 3=0 and road <>111 and road <>0 and road <>222 (52)

更新数据
update list set borl=iif(d1>6 ,'大',iif(d1>2 ,'中','小'))+iif(d2>6 ,'大',iif(d2>2 ,'中','小'))+iif(d3>6 ,'大',iif(d3>2 ,'中','小'))
select uid,d1,d2,d3,borl,format(road,'000') from List order by uid desc

和值统计
select Totalnum,sum(NumCount) as numcount from (
 select (d1+d2+d3) as Totalnum,count(*) as NumCount from list group by (d1+d2+d3) 
union all
select (d1+d2+d3) as Totalnum,count(*) as NumCount from history  group by (d1+d2+d3) 
)group by totalnum


select (d1+d2+d3) as Totalnum,count(*) as NumCount from history where uid like "2002%"  group by (d1+d2+d3) 

各期组数出现次数
select uid,a.onum,tnum from history a,(
select onum,count(*) as tnum from history group by onum)b where a.onum=b.onum 
order by uid desc

各期组数出现次数(历年)
select onum,sum(tnum) from (
select onum,count(*) as tnum from history group by onum 
union 
select onum,count(*) as tnum from list group by onum ) group by onum

各期组数出现次数(历年详细)
select uid,a.onum,tnum from list a,(
select onum,sum(tnum) as tnum from (
select onum,count(*)  as tnum from history group by onum 
union 
select onum,count(*) as tnum from list group by onum ) group by onum)b where a.onum=b.onum 
order by uid desc