菜单

MySQL之学子排名难点,用计算列实现移动加权平均算法_数据库别的_脚本之家

2020年2月13日 - 首页

复制代码 代码如下: if OBJECT_ID is not
null drop table tb if OBJECT_ID is not null drop table TEMP if
OBJECT_ID is not null drop FUNCTION FUN_NOWPRICE if OBJECT_ID is not
null drop FUNCTION FUN_NOWQTY go create table tb( id INT ,Date1
datetime ,ctype varchar ,qnt float ,pri float State of Qatar –qnt 数量 –pri 单价
insert tb select 0,’二零零六-1-1′, ‘进货’, 10, 100 union all select
1,’二〇〇九-1-1′, ‘进货’, 50, 120 union all select 2,’二零零六-1-2′, ‘出货’, 30,
150 union all select 3,’二〇〇八-1-3′, ‘进货’, 40, 130 union all select
4,’二〇一〇-1-3′, ‘出货’, 25, 160 GO — 笔者要算开支价,按移动加权平均 /*
1进货未来的开支价c1=/ 2出货之后的开支价c2=/=C2
–也正是讲出货的时候价格不改变 3进货未来的花费价c3=*c2+40*130State of Qatar/
–也正是说进货的时候单价更新为/入库后总量量 就那样类推… */
–想了半天,以为不能不用循环、递归、游标完毕,因为出库时的价钱是基于以前的笔录算出来的。
–也可以有优秀的算法,哪个人知道的麻烦教教笔者照旧发个链接。
–这几个FUNCTION就是变相完毕递归的 CREATE FUNCTION FUN_NOWPRICE RETURNS
NUMERIC AS BEGIN RETURN (SELECT ISNULL FROM ‘NOWPRICE’ FROM TEMP T1
WHERE IDT1.ID AND ID

–对输入的多少进行节制
create table t(studentID char(10), [name] varchar(8),
startDate char(10) Check (isdate(startdate)=1 and cast(startdate as
datetime)=convert(datetime,startdate,120)))
insert into t
select ‘aa’,’bb’,’77799820′
union all select ‘bb’,’ggg’,’2007-10-11′
union all select ‘cc’,’ddd’,’2007-20-11′
select * from t
–创造测量检验表
create table tbl ([name] varchar(8), chinese float, math float,zong
float,px int)
insert tbl
select ‘a’, 80, 90,null,null
union all select ‘b’, 85, 88,null,null
union all select ‘c’, 77, 93,null,null
union all select ‘d’, 80, 90,null,null
union all select ‘e’, 99, 100,null,null
–另豆蔻年华种办法
select *,zong=chinese + math,
px=(select count(1)+1 from tbl b where b.chinese + b.math >a.chinese

 

–创立存款和储蓄进度
create proc mysql
as
update bb set zong=tt.zong,px=tt.px from tbl bb,
(SELECT name,zong , px=(SELECT COUNT(zong) FROM
(
select name,chinese,math,zong = chinese + math from tbl
) t
WHERE zong > a.zong or (zong = a.zong and chinese > a.chinese)) +
1
FROM
(
select name,chinese,math,zong = chinese + math from tbl
) a
) tt
where bb.name=tt.name
go
–调用存款和储蓄进程
exec mysql
–查看
select * from tbl

–删除
drop table tbl
drop proc mysql

–创建表
create table tb(ID int,TEAM varchar(10),SCORE int,SCORE2 int,SCORE3
int)
insert into tb values(1, ‘A ‘, 100 , 100 , 100)
insert into tb values(2, ‘A ‘, 90 , 100 , 100)
insert into tb values(3, ‘B ‘, 50 , 40 , 30 )
insert into tb values(4, ‘B ‘, 50 , 40 , 30)
insert into tb values(5, ‘A ‘, 90 , 100 , 80)
insert into tb values(6, ‘B ‘, 55 , 50 , 50)
insert into tb values(7, ‘A ‘, 90 , 100 , 50)

–查询
select *,px=(select count(1)+1 from tb
where team=a.team and (score >a.score)
or (score = a.score and score2 >a.score2)
or (score = a.score and score2=a.score2 and score3 >a.score3)
or (score = a.score and score2=a.score2 and score3=a.score3 and id
<a.id))
from tb a order by team

–删除
drop table tb

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图