--更新OLDdetail各项统计信息为NULL的数据
----update OLDdetail set XXSJ=0 where XXSJ is null
----update OLDdetail set XXSJ=XXSJ*60 where XXSJ<>0 --将学习时间按秒计算
----update OLDdetail set DYTWS=0 where DYTWS is null
----update OLDdetail set DYHDS=0 where DYHDS is null
----update OLDdetail set TZS=0 where TZS is null
----update OLDdetail set ZYS=0 where ZYS is null
----update OLDdetail set ZYPYS=0 where ZYPYS is null
----update OLDdetail set XDS=0 where XDS is null
----update OLDdetail set ziyuans=0 where ziyuans is null
----update OLDdetail set LSDYS=0 where LSDYS is null
----update OLDdetail set LSXDS=0 where LSXDS is null
----update OLDdetail set LSFQSLS=0 where LSFQSLS is null
----update OLDdetail set LSBZZYS=0 where LSBZZYS is null
----update OLDdetail set LSPYZYS=0 where LSPYZYS is null
----update OLDdetail set LSFBGGS=0 where LSFBGGS is null
----update OLDdetail set LSFBJBS=0 where LSFBJBS is null
update OLDdetail set BJ='宜章小学教科3班' where BJ='宜章小学科教3班' and Class_id=''
--根据用户身份证号码补齐用户的USERID
--查询数据
--select a.*,b.ID_CARD_NUM,B.USER_ID,B.USER_NAME,NAME from OLDdetail as a inner join pub.ce_pub.dbo.t_pub_user as b
--on a.IDCardNumber=b.id_card_num
--where UserId=0 and IDCardNumber is not null --1039
--更新数据(pub.ce_pub.dbo.t_pub_user。。。就是。。pub.被侉的那个数据库名.dbo.被侉数据库里面要使用到的表名)
--update OLDdetail set UserId=B.USER_ID from OLDdetail as a,pub.ce_pub.dbo.t_pub_user as b
--where a.IDCardNumber=b.id_card_num and UserId=0
--查询根据班级名称查询班级ID
--select a.UserId,a.IDCardNumber,a.BJ,b.CLASS_ID,B.CLASS_NAME from OLDdetail as a
--inner join pub.ce_pub.dbo.t_pub_class as b
--on a.BJ=b.CLASS_NAME
--where UserId<>0 and a.Class_id='' --904
--更新OLDdetail表中班级ID
--UPDATE OLDdetail SET Class_id=b.CLASS_ID from OLDdetail as a,pub.ce_pub.dbo.t_pub_class as b
--where a.BJ=b.CLASS_NAME and UserId<>0 and a.Class_id=''
----(模糊查询)查询根据班级名称查询班级ID
--select a.UserId,a.IDCardNumber,a.BJ,b.CLASS_ID,B.CLASS_NAME from OLDdetail as a
--inner join pub.ce_pub.dbo.t_pub_class as b
--on a.BJ like '%'+b.CLASS_NAME+'%'
--where UserId<>0 and a.Class_id=''
----(模糊更新)更新OLDdetail表中班级ID
--UPDATE OLDdetail SET Class_id=b.CLASS_ID from OLDdetail as a,pub.ce_pub.dbo.t_pub_class as b
--where a.BJ like '%'+b.CLASS_NAME+'%' and UserId<>0 and a.Class_id=''
--更新某人的学习信息
--update OLDdetail set DYTWS=2,DYHDS=1,TZS=2,ZYS=5,ZYPYS=4,XDS=3,ziyuans=2
--where IDCardNumber='430111197003130414'
--插入一条新数据
insert into OLDdetail values(123935,'430426197811225121','新课标全员','祁东新课标小数1班','4300000000517',1792*60,3,33,2,3,3,1,1,0,0,0,0,0,0,0)
--根据用户身份证号码查找用户ID
select USER_ID from pub.ce_pub.dbo.t_pub_user where id_card_num='432801197706252028'
--根据班级名称查找班级ID
select CLASS_ID from pub.ce_pub.dbo.t_pub_CLASS WHERE CLASS_NAME='宜章师德小学3班'
--执行存储过程更新数据
exec HZX_ClassUser_LearnStatistic --统计平台中所有用户的学情信息
exec HZX_Detail_STATISTIC --将统计信息
跨库查询
SELECT * from t_learning_project as a
inner join pub.ce_pub.dbo.t_pub_org as b
on a.ORG_ID=b.OrgID
select * from t_learning_project as a,pub.ce_pub.dbo.t_pub_org as b
where a.ORG_ID=b.OrgID