用存储过程,在里面用个判断即可.decalre @cnt select @cnt = count(1) from tb if @cnt = 0 ...
直接看这个视图里有没有数据不就可以了吗? case when then else end 用一个变量记录视图里的数据条数,再进行判断就行了.
判断IF COUNT=0 ...........
create table t1(code varchar(10),usrname varchar(20))insert into t1 select 'a','aa' union select 'b','bb'create view v_t as select * from (select * from t1 union select case when count(*)>0 then null else 'N' end ,'N' from t1) t where code is not nullselect * from v_tcode usrname ---------- -------------------- a aa b bb(所影响的行数为 2 行) delete from t1select * from v_tcode usrname ---------- -------------------- N N(所影响的行数为 1 行)
其实可以写事物来判断 if exists(select count(1) from tb)=0 ....
谢谢老大们的提示,尤其是三楼的acupofnescafe提示了我。1、不为空记录时返回记录的值。 2、我写了一个视图,但还是报错,老大给看看。表名t1,只有一列t_idSELECT CASE COUNT(t_id) WHEN 0 THEN '00' ELSE t_id END AS t_id FROM dbo.t1错误原因好像是没有GROUP BY,请老大看看,指正。谢谢。
SELECT CASE COUNT(t_id) WHEN 0 THEN '00' ELSE t_id END AS t_id FROM dbo.t1GROUP BY t_id
create view v_t as select * from (select * from t1 union select case when count(*)>0 then null else 'N' end from t1) t where t_id is not null
不知道这样是否符合你的需求:( SELECT 'N' FROM TEST_TABLE_1 HAVING COUNT(*) = 0 ) UNION ALL ( SELECT [value] FROM TEST_TABLE_1 )
select @cnt = count(1) from tb
if @cnt = 0
...
case when then else end
用一个变量记录视图里的数据条数,再进行判断就行了.
...........
create table t1(code varchar(10),usrname varchar(20))insert into t1
select 'a','aa'
union select 'b','bb'create view v_t as select * from (select * from t1 union select case when count(*)>0 then null else 'N' end ,'N' from t1) t where code is not nullselect * from v_tcode usrname
---------- --------------------
a aa
b bb(所影响的行数为 2 行)
delete from t1select * from v_tcode usrname
---------- --------------------
N N(所影响的行数为 1 行)
if exists(select count(1) from tb)=0
....
2、我写了一个视图,但还是报错,老大给看看。表名t1,只有一列t_idSELECT CASE COUNT(t_id) WHEN 0 THEN '00' ELSE t_id END AS t_id
FROM dbo.t1错误原因好像是没有GROUP BY,请老大看看,指正。谢谢。
FROM dbo.t1GROUP BY t_id
create view v_t as
select * from
(select * from t1
union
select case when count(*)>0 then null else 'N' end
from t1) t
where t_id is not null
SELECT 'N' FROM TEST_TABLE_1
HAVING COUNT(*) = 0
)
UNION ALL
(
SELECT [value] FROM TEST_TABLE_1
)