CREATE TABLE #t1(id INT,NAME VARCHAR(10)) INSERT #t1 SELECT 1,'a' UNION ALL SELECT 2,'b' CREATE TABLE #t2(id INT,NAME VARCHAR(10)) INSERT #t2 SELECT 3,'c' UNION ALL SELECT 4,'d' CREATE TABLE #t3(id INT,NAME VARCHAR(10)) INSERT #t3 SELECT 1,'a' UNION ALL SELECT 3,'c'SELECT a.*,tag=case when c.tag IS NULL THEN 0 ELSE 1 END FROM (SELECT * FROM #t1 UNION ALL SELECT * FROM #t2)a LEFT JOIN ( SELECT b.id,tag=1 FROM #t3 b )c ON a.id=c.id/* id NAME tag ----------- ---------- ----------- 1 a 1 2 b 0 3 c 1 4 d 0(4 行受影响) */
/***** 判断你的推荐表里的id在结果集里是否存在,在了设置为1,否则为0 *****/update tb set tag = 1 where id in (select id from (/*你的结果集*/))
CREATE TABLE A(id INT) INSERT INTO A SELECT 1CREATE TABLE B(id INT) INSERT INTO B SELECT 2CREATE TABLE C(id INT, id2 INT, flag INT) INSERT INTO C (id, id2) SELECT 1, 2 UNION ALL SELECT 2, 3--子查询 UPDATE C SET flag = CASE WHEN EXISTS(SELECT TOP 1 1 FROM (SELECT A.id, id2 = B.id FROM A,B) a WHERE a.id = C.id AND a.id2 = C.id2) THEN 1 ELSE 0 END--CTE ;WITH AB AS ( SELECT A.id, id2 = B.id FROM A,B ) UPDATE C SET flag = CASE WHEN EXISTS(SELECT TOP 1 1 FROM AB a WHERE a.id = C.id AND a.id2 = C.id2) THEN 1 ELSE 0 END
select c.* , case when exists(select 1 from (select a.* , b.* from a , b where ...) d where ... ) then '存在' else '不存在' end from c
CREATE TABLE #t1(id INT,NAME VARCHAR(10))
INSERT #t1
SELECT 1,'a' UNION ALL
SELECT 2,'b'
CREATE TABLE #t2(id INT,NAME VARCHAR(10))
INSERT #t2
SELECT 3,'c' UNION ALL
SELECT 4,'d'
CREATE TABLE #t3(id INT,NAME VARCHAR(10))
INSERT #t3
SELECT 1,'a' UNION ALL
SELECT 3,'c'SELECT a.*,tag=case when c.tag IS NULL THEN 0 ELSE 1 END FROM
(SELECT * FROM #t1
UNION ALL
SELECT * FROM #t2)a
LEFT JOIN (
SELECT b.id,tag=1 FROM #t3 b
)c
ON a.id=c.id/*
id NAME tag
----------- ---------- -----------
1 a 1
2 b 0
3 c 1
4 d 0(4 行受影响)
*/
/*****
判断你的推荐表里的id在结果集里是否存在,在了设置为1,否则为0
*****/update tb
set tag = 1
where id in (select id from (/*你的结果集*/))
(case 折扣卷ID is null 0 else 1 end)
INSERT INTO A SELECT 1CREATE TABLE B(id INT)
INSERT INTO B SELECT 2CREATE TABLE C(id INT, id2 INT, flag INT)
INSERT INTO C (id, id2)
SELECT 1, 2 UNION ALL
SELECT 2, 3--子查询
UPDATE C
SET flag = CASE WHEN EXISTS(SELECT TOP 1 1 FROM (SELECT A.id, id2 = B.id FROM A,B) a WHERE a.id = C.id AND a.id2 = C.id2) THEN 1 ELSE 0 END--CTE
;WITH AB AS
(
SELECT A.id, id2 = B.id FROM A,B
)
UPDATE C
SET flag = CASE WHEN EXISTS(SELECT TOP 1 1 FROM AB a WHERE a.id = C.id AND a.id2 = C.id2) THEN 1 ELSE 0 END