//作业：服务包新用户定购记录定时更新－－将中心下发的服务包定购记录上传到SOOTV数据库中
dimConn,Rs,Sql,TableName
dimInPath,OutPath
InPath="G:\程序开发\Soap\spforvnetSample\SubscribeServicePackage.xml"
OutPath=
TableName="UserService"
setConn=CreateObject("adodb.connection")
setrs=createobject("ADODB.recordset")
Conn.Open"Driver={SQLServer};SERVER=218.30.110.98;UID=sa;PWD=q1w2e3r4T%Y^U&;I*;DATABASE=nNDrm;"
Sql="Select*from"&;TableName&;"WhereUserIDin(SELECTUserIDFROM"
Sql=Sql&;TableName&;"whereServiceStatus=0GROUPBYUserIDHAVING(COUNT(1)>1))"
Sql=Sql&;"andServiceStatus=0OrderByUserID,ExpireDateDesc"
Rs.OpenSql,conn,3,3
UserID=""
查找某省份每天播放的节目总计
SELECTTOP10LEFT(MovieTitle,4)ASPrgramTitle,COUNT(*)AScnt
FROMSubmitData
WHERE(LEFT(UserId,2)='27')AND(CreateTime>='2005-2-21')and(CreateTime<='2005-2-22')
GROUPBYLEFT(MovieTitle,4)
ORDERBYcntDESC
//恢复已经取消的服务包用户
updateUserService
setcanceltime=null,servicestatus=0
whereuseridin(SELECTUserIDFROMUserServiceWHERE(PackageIDISNOTNULL)GROUPBYUserID,transactionidHAVING(COUNT(*)>1))
//选出重复的服务包用户记录
SELECTUserID,COUNT(*)ASExpr1
FROMUserService
WHERE(PackageIDISNOTNULL)
GROUPBYUserID,transactionid
HAVING(COUNT(*)>1)
//查找总共多少记录重复
SELECTCOUNT(*)ASExpr1
FROMUserService
WHERE(UserIDIN
(SELECTUserID
FROMUserService
WHEREflgISNULLANDpackageidISNOTNULL
GROUPBYUserID,TransactionID
HAVING(COUNT(*)>1)))AND(PackageIDISNOTNULL)
SELECT*fromTradeLogWHERE(TransactionIDIN(SELECTTransactionIDFROMTradeLogWHEREflgISNULLANDspid='ZSCM'GROUPBYUserID,TransactionID,TradeDetailHAVING(COUNT(*)>1)))
////////////////////注意：以下相当于没有任何筛选
SELECT*
FROMUserService
WHEREEXISTS
(SELECTUserID,TransactionID
FROMUserService
WHEREflgISNULLANDpackageidISNOTNULL
GROUPBYUserID,TransactionID
HAVING(COUNT(*)>1))
ORDERBYUserID
////////////////////
//删除重复记录的方法：
//1先选出那些重复的记录，将每个重复的记录中的其中一条flg置为1
//2删除那些flg为1的记录
DimConn,Rs,Sql
setConn=CreateObject("adodb.connection")
setrs=createobject("ADODB.recordset")
Conn.Open"Driver={SQLServer};SERVER=218.241.89.12;UID=sa;PWD=q1w2e3r4;DATABASE=nDrm;"
sql="SELECT*fromUserserviceWHERE(UserIDIN"
sql=sql&;"(SELECTUserIDFROMUserServiceWHEREflgISNULLANDpackageidISNOTNULL"
sql=sql&;"GROUPBYUserID,TransactionIDHAVING(COUNT(*)>1)))"
Rs.OpenSql,conn,3,3
UserID=""
Dowhilenotrs.eof
ifRs("UserID")<>UserIDthen
UserID=rs("UserID")
else
Rs("flg")=1
endif
Rs.Update
Rs.movenext
Loop
setrs=nothing
setconn=nothing
//删除重复记录的方法：
//1先选出那些重复的记录，将每个重复的记录中的其中一条flg置为1
//2删除那些flg为1的记录
DimConn,Rs,Sql
setConn=CreateObject("adodb.connection")
setrs=createobject("ADODB.recordset")
Conn.Open"Driver={SQLServer};SERVER=218.30.110.98;UID=sa;PWD=q1w2e3r4t%y^u&;i*;DATABASE=nnDrm;"
sql="SELECT*fromTradeLogWHERE(TransactionIDIN"
sql=sql&;"(SELECTTransactionIDFROMTradeLogWHEREflgISNULLANDspid='ZSCM'"
sql=sql&;"GROUPBYUserID,TransactionID,TradeDetailHAVING(COUNT(*)>1)))"
Rs.OpenSql,conn,3,3
UserID=""
Dowhilenotrs.eof
ifRs("UserID")<>UserIDthen
UserID=rs("UserID")
else
Rs("flg")=1
endif
Rs.Update
Rs.movenext
Loop
setrs=nothing
setconn=nothing
