不用while如何做？？

[-Tracy-McGrady-]

/*
对比两个表中的记录数量是否一致，不用while，如何做？匹对上的就update表里的is_ok字段为'y'
*/

IF OBJECT_ID('tempdb.dbo.#out') IS NOT NULL
	DROP TABLE #out
CREATE TABLE #out(code VARCHAR(10),price NUMERIC(18,2),amount INT,is_ok VARCHAR(1) DEFAULT 'n')
INSERT INTO #out(code, price, amount)
SELECT 'A',10,20 UNION ALL
SELECT 'A',10,20 UNION ALL
SELECT 'A',11,20 UNION ALL
SELECT 'A',11,30 UNION ALL
SELECT 'A',12,40 UNION ALL
SELECT 'A',12,20

IF OBJECT_ID('tempdb.dbo.#in') IS NOT NULL
	DROP TABLE #in
CREATE TABLE #in(code VARCHAR(10),price NUMERIC(18,2),amount INT,is_ok VARCHAR(1) DEFAULT 'n')
INSERT INTO #in(code, price, amount)
SELECT 'A',10,10 UNION ALL
SELECT 'A',10,10 UNION ALL
SELECT 'A',10,20 UNION ALL
SELECT 'A',11,10 UNION ALL
SELECT 'A',11,10 UNION ALL
SELECT 'A',11,15 UNION ALL
SELECT 'A',12,40 UNION ALL
SELECT 'A',12,20

[删库到跑路]

update  需要修改的表 set 字段=修改的数据 from 
需要修改的表 inner join 源数据 on。。。。。。。。
where 条件。。。。。。。

[吉普赛的歌]

--1. 更新 #out
UPDATE A
SET A.is_ok='y'
FROM #out AS A INNER JOIN #in AS B
	ON A.code=B.code 
WHERE A.price=B.price AND A.amount=B.amount
--2. 更新 #in
UPDATE B
SET B.is_ok='y'
FROM #out AS A INNER JOIN #in AS B
	ON A.code=B.code 
where A.price=B.price AND A.amount=B.amount

[morliz子轩]

update a set a.is_ok ='y' from #out a
inner join #in b on a.code = b.code
where a.price = b.price and a.amount = b.amount

这样子？

[-Tracy-McGrady-]

引用 1 楼 删库到跑路 的回复:

update  需要修改的表 set 字段=修改的数据 from 
需要修改的表 inner join 源数据 on。。。。。。。。
where 条件。。。。。。。

引用 2 楼 吉普赛的歌 的回复:

--1. 更新 #out
UPDATE A
SET A.is_ok='y'
FROM #out AS A INNER JOIN #in AS B
	ON A.code=B.code 
WHERE A.price=B.price AND A.amount=B.amount
--2. 更新 #in
UPDATE B
SET B.is_ok='y'
FROM #out AS A INNER JOIN #in AS B
	ON A.code=B.code 
where A.price=B.price AND A.amount=B.amount

引用 3 楼 morliz子轩 的回复:

update a set a.is_ok ='y' from #out a
inner join #in b on a.code = b.code
where a.price = b.price and a.amount = b.amount

这样子？

这么简单就好咯，amount是累计的，条件是code和price相同，然后看看左右两侧是sum(amount)是否在允许的误差范围内

[morliz子轩]

那问题没搞懂。 你只是贴了个数据结构模型，如果是看记录条数是否一致，count计数判断一下，就行。 如果要比对每一条的记录是否相符，不符的N，相符的Y。那你要把真实的数据内容，放几条上来看一下。

[文盲老顾]

IF OBJECT_ID('tempdb.dbo.#out') IS NOT NULL
    DROP TABLE #out
CREATE TABLE #out(code VARCHAR(10),price NUMERIC(18,2),amount INT,is_ok VARCHAR(1) DEFAULT 'n')
INSERT INTO #out(code, price, amount)
SELECT 'A',10,20 UNION ALL
SELECT 'A',10,20 UNION ALL
SELECT 'A',11,20 UNION ALL
SELECT 'A',11,30 UNION ALL
SELECT 'A',12,40 UNION ALL
SELECT 'A',12,20
 
IF OBJECT_ID('tempdb.dbo.#in') IS NOT NULL
    DROP TABLE #in
CREATE TABLE #in(code VARCHAR(10),price NUMERIC(18,2),amount INT,is_ok VARCHAR(1) DEFAULT 'n')
INSERT INTO #in(code, price, amount)
SELECT 'A',10,10 UNION ALL
SELECT 'A',10,10 UNION ALL
SELECT 'A',10,20 UNION ALL
SELECT 'A',11,10 UNION ALL
SELECT 'A',11,10 UNION ALL
SELECT 'A',11,15 UNION ALL
SELECT 'A',12,40 UNION ALL
SELECT 'A',12,20

;with t as (
	select a.code as a_code,a.price as a_price,a.amount as a_amount,b.code as b_code,b.price as b_price,b.amount as b_amout
		,(case when a.code=b.code and a.price=b.price and a.nid=b.nid and a.amount=b.amount then 1 else 0 end) as upd 
	from (
		select *,ROW_NUMBER() over(partition by code,price,amount order by amount) as nid from #in
	) a
	full join (
		select *,ROW_NUMBER() over(partition by code,price,amount order by amount) as nid from #out
	) b on a.code = b.code and a.price=b.price and a.nid=b.nid and a.amount=b.amount
)
update #in
set is_ok='y'
from t
where a_code=#in.code and a_price=#in.price and a_amount=#in.amount and upd=1

select * from #in
select * from #out