字段 比较 结果 写下 同行其他字段

字段 比较 结果 写入 同行其他字段

SQL code

tab1a1   a2   a3 1    23    2    633    434    535    666    67 

看a2是否含有3 有3输出结果1 无3输出结果0 在看a2是否含有6，有6输出结果1 无6输出结果0 2个结果进行相加 写入 同行 a3  

update tab1 ,(SELECT sum(if(a2 rlike 3,1,0))+
sum(if(a2 rlike 6,1,0)) as s
FROM tab1 GROUP BY a2
) c
set tab1.a3=c.s
这句 怎么 不行啊 什么问题出错了么

[解决办法]

SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE TABLE tba(    a1 INT,    a2 INT,    a3 INT) GOINSERT INTO tba SELECT 1,23,0 UNION  SELECT 2,63,0 UNIONSELECT 3,43,0 UNIONSELECT 4,53,0 UNIONSELECT 5,66,0 UNIONSELECT 6,67,0GOUPDATE tba SET a3 = CASE WHEN CHARINDEX('3',LTRIM(a2)) > 0 THEN 1                         ELSE 0 END + CASE WHEN CHARINDEX('6',LTRIM(a2)) > 0 THEN 1                                           ELSE 0 END SELECT * FROM tbaa1    a2    a31    23    12    63    23    43    14    53    15    66    16    67    1
[解决办法]
SQL code
USE tempdb;GOIF OBJECT_ID('testtb') IS NOT NULL DROP TABLE testtb;GOCREATE TABLE testtb(a1 INT IDENTITY(1,1), a2 INT NULL , a3 INT NULL  );GOINSERT INTO testtb VALUES(23,NULL);INSERT INTO testtb VALUES(63,NULL);INSERT INTO testtb VALUES(43,NULL);INSERT INTO testtb VALUES(53,NULL);INSERT INTO testtb VALUES(66,NULL);INSERT INTO testtb VALUES(67,NULL);GOUPDATE dbo.testtb SET a3=(CASE WHEN PATINDEX('%3%',CAST(a2 AS CHAR(4)))<>0 THEN 1                               ELSE 0 END)+                          (CASE WHEN PATINDEX('%6%',CAST (a2 AS CHAR(4)))<>0 THEN 1                           ELSE 0 END );                         SELECT * FROM dbo.testtb;/*--结果a1    a2    a31    23    12    63    23    43    14    53    15    66    16    67    1*/ 

 

查看更多 下一篇