工作没有搞定,彩票次次落空!因为学习的oracle数据库,就想建一个表以及一些列,将每次的中奖的6个红色号码球号以及1个蓝色号码球号输入进去。
1,写sql求出红色号码出现次数最多的前6个号码。
2,写sql求出蓝色号码出现次数最多的那个号码。
因为失业中,每次我就买上面算出来的这组号码,每次就花2块钱,这样省事情省力气,大家觉得怎么样啊?下面是sql实现:create table redlottery(redid varchar2(20));
create table bluelottery(blueid varchar2(20));
insert into redlottery values('12');
insert into redlottery values('12');
insert into redlottery values('12');
insert into redlottery values('12');
insert into redlottery values('22');
insert into redlottery values('30');
insert into redlottery values('30');
insert into redlottery values('22');
insert into redlottery values('02');
insert into redlottery values('02');
insert into redlottery values('02');
insert into redlottery values('12');
insert into redlottery values('15');
insert into redlottery values('17');
insert into redlottery values('17');
insert into redlottery values('24');
insert into redlottery values('24');
insert into redlottery values('24');
insert into redlottery values('24');
insert into redlottery values('24');
insert into redlottery values('24');
但是在取号码的时候,我的select语句:select redid from redlottery group by redid 却取不出 正确的前六个号码;大家有什么好的思路,怎么实现没有啊?写下,谢谢了!
1,写sql求出红色号码出现次数最多的前6个号码。
2,写sql求出蓝色号码出现次数最多的那个号码。
因为失业中,每次我就买上面算出来的这组号码,每次就花2块钱,这样省事情省力气,大家觉得怎么样啊?下面是sql实现:create table redlottery(redid varchar2(20));
create table bluelottery(blueid varchar2(20));
insert into redlottery values('12');
insert into redlottery values('12');
insert into redlottery values('12');
insert into redlottery values('12');
insert into redlottery values('22');
insert into redlottery values('30');
insert into redlottery values('30');
insert into redlottery values('22');
insert into redlottery values('02');
insert into redlottery values('02');
insert into redlottery values('02');
insert into redlottery values('12');
insert into redlottery values('15');
insert into redlottery values('17');
insert into redlottery values('17');
insert into redlottery values('24');
insert into redlottery values('24');
insert into redlottery values('24');
insert into redlottery values('24');
insert into redlottery values('24');
insert into redlottery values('24');
但是在取号码的时候,我的select语句:select redid from redlottery group by redid 却取不出 正确的前六个号码;大家有什么好的思路,怎么实现没有啊?写下,谢谢了!
color_type 颜色种类
num 号码
比如说
lot_id color_type num
1 red 2
1 red 3
1 red 4
1 red 7
1 red 9
1 red 20
1 red 21
1 blue 23
2 red 2
2 red 5
2 red 4
2 red 8
2 red 19
2 red 20
2 red 21
2 blue 29select num ,count(*)
from a
where corlor_type='red'
group by num
order by 2求蓝球一样
取红色:
SELECT REDID, COUNT
FROM (SELECT REDID, COUNT(*) COUNT
FROM REDLOTTERY
GROUP BY REDID
ORDER BY 2 DESC)
WHERE ROWNUM <= 6;取绿色:
SELECT blueid, COUNT
FROM (SELECT blueid, COUNT(*) COUNT
FROM bluelottery
GROUP BY blueid
ORDER BY 2 DESC)
WHERE ROWNUM <= 1;
from lottery
where colortype='red'
group by num
order by 2 desc) where
rownum <=6;
select sum(decode(颜色,号码,1,0)) as n from 表 order by n desc 可以适一下,我没试过!因为我的oracle 出现问题,不能登陆了!