表 tb 结构 a1 a2 a3 a4 a5 a6 a7 dx 数据 1 2 3 4 5 6 7统计大于4的数 update tb set dx=(select count (a1) as a from tb where a1>4)+
(select count (a2) as a from tb where a2>4)+
(select count (a3) as a from tb where a3>4)+
(select count (a4) as a from tb where a4>4)+
(select count (a5) as a from tb where a5>4)+
(select count (a6) as a from tb where a6>4)+
(select count (a7) as a from tb where a7>4)这样写有什么问题?
(select count (a2) as a from tb where a2>4)+
(select count (a3) as a from tb where a3>4)+
(select count (a4) as a from tb where a4>4)+
(select count (a5) as a from tb where a5>4)+
(select count (a6) as a from tb where a6>4)+
(select count (a7) as a from tb where a7>4)这样写有什么问题?
case when a2>4 then 1 else 0 end+
case when a3>4 then 1 else 0 end+
case when a4>4 then 1 else 0 end+
case when a5>4 then 1 else 0 end+
case when a6>4 then 1 else 0 end+
case when a7>4 then 1 else 0 end
create table tb ( t1 int,
t2 int,
t3 int,
t4 int,
t5 int,
t6 int,
t7 int,
dx int)insert into tb(t1, t2, t3, t4, t5, t6, t7) values(1, 2, 3, 4, 5, 6, 7)
update tb set dx =
case when t1 > 4 then t1 else 0 end
+case when t2 > 4 then t2 else 0 end
+case when t3 > 4 then t3 else 0 end
+case when t4 > 4 then t4 else 0 end
+case when t5 > 4 then t5 else 0 end
+case when t6 > 4 then t6 else 0 end
+case when t7 > 4 then t7 else 0 end
select * from tb
t1 int,
t2 int,
t3 int,
t4 int,
t5 int,
t6 int,
t7 int,
dx int
)insert into @tb(t1, t2, t3, t4, t5, t6, t7) values(1, 2, 3, 4, 5, 6, 7)
select
SIGN(t1/4)+SIGN(t2/4)+SIGN(t3/4)+SIGN(t4/4)+SIGN(t5/4)+SIGN(t6/4)+SIGN(t7/4)
from
@tb(1 個資料列受到影響)-----------
4(1 個資料列受到影響)
case when a2>4 then 1 else 0 end+
case when a3>4 then 1 else 0 end+
case when a4>4 then 1 else 0 end+
case when a5>4 then 1 else 0 end+
case when a6>4 then 1 else 0 end+
case when a7>4 then 1 else 0 end)as char(10))
+":"+
Cast((7-(case when a1>4 then 1 else 0 end+
case when a2>4 then 1 else 0 end+
case when a3>4 then 1 else 0 end+
case when a4>4 then 1 else 0 end+
case when a5>4 then 1 else 0 end+
case when a6>4 then 1 else 0 end+
case when a7>4 then 1 else 0 end )as char(10))
这样可以实现大于4与小于4的3:5吗?
----------------------------
1.000000顶4楼,不会出错
假设小于4的有a个,大于等于4的有b个,可以直接ltrim(a)+':'+ltrim(b)
不是整型時轉為整型
select sign(cast(3.0 as int)/4)
update tb set dx=case when a1>4 end+
case when a2>4 end+
case when a3>4 end+
case when a4>4 end+
case when a5>4 end+
case when a6>4 end+
case when a7>4 end
then 1 else 0 代表什么意思呢
integer 转换成 char 用什么?ltrim?
--那個測試表變量
update tb set dx=SIGN(t1/4)+SIGN(t2/4)+SIGN(t3/4)+SIGN(t4/4)+SIGN(t5/4)+SIGN(t6/4)+SIGN(t7/4)
SIGN
返回给定表达式的正 (+1)、零 (0) 或负 (-1) 号。sign是返回表达式的正\零\负号.update tb set dx=SIGN(t1/4)+SIGN(t2/4)+SIGN(t3/4)+SIGN(t4/4)+SIGN(t5/4)+SIGN(t6/4)+SIGN(t7/4)没看明白!