SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' 为什么上面的sql就能通过呢
因为这个 MAX(rjsj) < '2014-01-11 23:59:59' 是一个比较,不是一个结果,所以需要用case when 返回1个结果
MAX(rjsj) < '2014-01-11 23:59:59' 只能写在where子句中。
好象不能 那么写哈 就是select 1<2 from tableA也是报错的是的想过滤数据。 关于 between a and b 若字段b的值 与 表里最大值比较 当b>最大值的时候我希望 返回0
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
select * from tableA where max(rjsj)>'2014-01-05 00:00:00'服务器: 消息 147,级别 15,状态 1,行 3 聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。
SELECT CASE WHEN MAX(rjsj) < '2014-01-02 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-02 23:59:59'同样返回0...
改成这样试试: select * from tableA where (select max(rjsj) from tableA) >'2014-01-05 00:00:00'
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' 不管怎样都返回0的。修改哪
select max(rjsj) from tableA having max(rjsj)<'2014-01-04 00:00:00'
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' 不管怎样都返回0的。修改哪用这个试试:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' 不管怎样都返回0的。修改哪用这个试试:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
我试了,无论都显示0 用between A and B 查询,B不在表里的时候显示0是我希望的结果 可是当B在表里的时候 也显示0 我就想应该有个地方需要改动 问题不大哈
你要是觉得麻烦,最简单的方式就是用IF/ELSE来判断
我不懂啊 sql也能写if else是吧
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' 不管怎样都返回0的。修改哪用这个试试:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*) END FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
我试了,无论都显示0 用between A and B 查询,B不在表里的时候显示0是我希望的结果 可是当B在表里的时候 也显示0 我就想应该有个地方需要改动 问题不大哈你的逻辑是不是 在rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' 时,当MAX(rjsj) 小于 '2014-01-11 23:59:59',就是0,而只有当 MAX(rjsj) = '2014-01-11 23:59:59'时,才会返回 count(*) ,是这样的逻辑?
当然可以,类似这样: if (select max(rjsj) from tb)<'2014-01-11 23:59:59' begin select 0 end else begin SELECT COUNT(*) FROM tableA WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' end
ok 您说的对那就是这样呗: SELECT case when max(rjsj) < '2014-01-11 23:59:59' then 0 else COUNT(case when rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' then 1 else null end) end FROM tableA
ok 您说的对那就是这样呗: SELECT case when max(rjsj) < '2014-01-11 23:59:59' then 0 else COUNT(case when rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' then 1 else null end) end FROM tableA 思路比我开阔 对的
因为这个 MAX(rjsj) < '2014-01-11 23:59:59' 是一个比较,不是一个结果,所以需要用case when 返回1个结果
关于 between a and b
若字段b的值 与 表里最大值比较 当b>最大值的时候我希望 返回0
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
select * from tableA
where max(rjsj)>'2014-01-05 00:00:00'服务器: 消息 147,级别 15,状态 1,行 3
聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。
SELECT CASE WHEN MAX(rjsj) < '2014-01-02 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-02 23:59:59'同样返回0...
改成这样试试:
select * from tableA
where (select max(rjsj) from tableA) >'2014-01-05 00:00:00'
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
不管怎样都返回0的。修改哪
having max(rjsj)<'2014-01-04 00:00:00'
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
不管怎样都返回0的。修改哪用这个试试:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
不管怎样都返回0的。修改哪用这个试试:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
我试了,无论都显示0
用between A and B 查询,B不在表里的时候显示0是我希望的结果
可是当B在表里的时候 也显示0 我就想应该有个地方需要改动
问题不大哈
sql也能写if else是吧
你这个写法,应该就可以的:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
不管怎样都返回0的。修改哪用这个试试:SELECT CASE WHEN MAX(rjsj) < '2014-01-11 23:59:59' THEN 0 ELSE COUNT(*)
END
FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
我试了,无论都显示0
用between A and B 查询,B不在表里的时候显示0是我希望的结果
可是当B在表里的时候 也显示0 我就想应该有个地方需要改动
问题不大哈你的逻辑是不是 在rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59' 时,当MAX(rjsj) 小于 '2014-01-11 23:59:59',就是0,而只有当 MAX(rjsj) = '2014-01-11 23:59:59'时,才会返回 count(*) ,是这样的逻辑?
if (select max(rjsj) from tb)<'2014-01-11 23:59:59'
begin
select 0
end
else
begin
SELECT COUNT(*) FROM tableA
WHERE rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
end
SELECT case when max(rjsj) < '2014-01-11 23:59:59' then 0
else COUNT(case when rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
then 1
else null end)
end
FROM tableA
SELECT case when max(rjsj) < '2014-01-11 23:59:59' then 0
else COUNT(case when rjsj BETWEEN '2014-01-01 00:00:00' AND '2014-01-11 23:59:59'
then 1
else null end)
end
FROM tableA
思路比我开阔 对的
select 是返回值来的买一些基础的书看看,这个问题是相对比较简单的。
条件,不在case when then里,就在where里