100分求一个存储过程！在线=

[qr0413356]

sql3="select* form SMT_yp"
sql1="select SMT_yp_id ,count(*) as num from SMT_sca where smt_yp_id="& rs("SMT_id")&"group by SMT_yp_id"
set rs=server.createobject("adodb.recordset")
rs.open sql3,conn,1,1
do while not rs.eof
set rs1=server.createobject("adodb.recordset")
rs1.open sql1,conn,1,1
sql2="update SMT_yp set SMT_mun ="&rs1("num")&" where SMT_id ="&rs("SMT_id")&""
set rs2=server.createobject("adodb.recordset")
rs2.open sql2,conn,1,1
loop
rs.close
set rs=nothing
用存储过程实现

[qr0413356]

试一下先

[dobear_0922]

看看先

[fcuandy]

一条语句就解决了。

UPDATE a SET a.SMT_mun = cnt
	FROM SMT_yp a
INNER JOIN
	(SELECT Smt_yp_id,COUNT(*) cnt FROM smt_sca GROUP BY Smt_yp_id) b
	ON a.smt_id = b.smt_yp_id

[fcuandy]

如果要存储过程，自己套上语法就可以了

CREATE PROC p
AS
UPDATE a SET a.SMT_mun = cnt
	FROM SMT_yp a
INNER JOIN
	(SELECT Smt_yp_id,COUNT(*) cnt FROM smt_sca GROUP BY Smt_yp_id) b
	ON a.smt_id = b.smt_yp_id

[dobear_0922]

[Quote=引用 3 楼 fcuandy 的回复:]
如果要存储过程，自己套上语法就可以了



SQL codeCREATE PROC p
AS
UPDATE a SET a.SMT_mun = cnt
FROM SMT_yp a
INNER JOIN
(SELECT Smt_yp_id,COUNT(*) cnt FROM smt_sca GROUP BY Smt_yp_id) b
ON a.smt_id = b.smt_yp_id
[/Quote]

FC速度真快！

[-狙击手-]

[Quote=引用 3 楼 fcuandy 的回复:]
如果要存储过程，自己套上语法就可以了



SQL codeCREATE PROC p
AS
UPDATE a SET a.SMT_mun = cnt
FROM SMT_yp a
INNER JOIN
(SELECT Smt_yp_id,COUNT(*) cnt FROM smt_sca GROUP BY Smt_yp_id) b
ON a.smt_id = b.smt_yp_id
[/Quote]

的确