TABLE aid fid cc
-----------
1 f91 TT
2 f91 TT
3 f92 TT
4 f93 TT
5 f93 TT
6 f93 TT查询表a 当fid=f91时 如果 count(fid)>1加条数据
id fid cc
-----------
z xxx TT以下是查询语句显示结果:
fid=91时id fid cc
-----------
1 f91 TT
2 f91 TT
z f91 TT ///count(fid)>1 增加一条fid=92时id fid cc
-----------
3 f92 TT ///count(fid)>1 这里没有fid=93时id fid cc
-----------
4 f93 TT
5 f93 TT
6 f93 TT
z f93 TT ///count(fid)>1 增加一条
我这样写错误,不能用union all
select * from a where id in
(
select id from a union all
select 'z'
where a.fid in(
select fid from A where fid='f91' group by fid having count(fid)>0
)
)
各位帮忙该怎么写
-----------
1 f91 TT
2 f91 TT
3 f92 TT
4 f93 TT
5 f93 TT
6 f93 TT查询表a 当fid=f91时 如果 count(fid)>1加条数据
id fid cc
-----------
z xxx TT以下是查询语句显示结果:
fid=91时id fid cc
-----------
1 f91 TT
2 f91 TT
z f91 TT ///count(fid)>1 增加一条fid=92时id fid cc
-----------
3 f92 TT ///count(fid)>1 这里没有fid=93时id fid cc
-----------
4 f93 TT
5 f93 TT
6 f93 TT
z f93 TT ///count(fid)>1 增加一条
我这样写错误,不能用union all
select * from a where id in
(
select id from a union all
select 'z'
where a.fid in(
select fid from A where fid='f91' group by fid having count(fid)>0
)
)
各位帮忙该怎么写
select * from a where id in
(
select id from a where fid in(
select fid from a where fid='f91' group by fid having count(fid)>0 )
union all
select 'z' as id
)
)
select * from a where id in
(
select id from a where
fid in( select fid from a where fid='f91' group by fid having count(fid)>0 )
union all
select 'z' as id
)
这样写的话
fid=92时 id fid cc
-----------
3 f92 TT
z f92 TT ///count(fid)=1 增加一条
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[fid] varchar(3),[cc] varchar(2))
insert [tb]
select '1','f91','TT' union all
select '2','f91','TT' union all
select '3','f92','TT' union all
select '4','f93','TT' union all
select '5','f93','TT' union all
select '6','f93','TT'
---查询---
declare @fid varchar(10)
set @fid='f92'
select
distinct *
from
(
select * from tb
union all
select 'z',fid,cc from tb t where fid=@fid and EXISTS(select fid from tb where fid=@fid and fid=t.fid group by fid having count(fid)>1)
) t
where
fid=@fid---结果---
id fid cc
---- ---- ----
3 f92 TT(所影响的行数为 1 行)
IF OBJECT_ID('[tab]') IS NOT NULL
DROP TABLE [tab]
go
CREATE TABLE [tab] (id VARCHAR(5),fid VARCHAR(5),cc varchar(12))
INSERT INTO [tab] select
'1', 'f91' , 'TT'union all select
'2', 'f91' , 'TT'union all select
'3', 'f92' , 'TT'union all select
'4', 'f93' , 'TT'union all select
'5', 'f93' , 'TT'union all select
'6', 'f93' , 'TT'
declare @str varchar(12)
set @str='f93'if (select count(*)as co from tab where fid=@str)>1
select* from tab where fid=@str
union
select 'z',@str,'tt' from tab
else
select* from tab where fid=@strid fid cc
----- ------------ ------------
4 f93 TT
5 f93 TT
6 f93 TT
z f93 tt(4 行受影响)
IF OBJECT_ID('[tab]') IS NOT NULL
DROP TABLE [tab]
go
CREATE TABLE [tab] (id VARCHAR(5),fid VARCHAR(5),cc varchar(12))
INSERT INTO [tab] select
'1', 'f91' , 'TT'union all select
'2', 'f91' , 'TT'union all select
'3', 'f92' , 'TT'union all select
'4', 'f93' , 'TT'union all select
'5', 'f93' , 'TT'union all select
'6', 'f93' , 'TT'
declare @str varchar(12)
set @str='f92'--方法2
select* from tab where fid=@str
union
select 'z',@str,'tt' from tab
where (select count(*)as co from tab where fid=@str)>1
/*
id fid cc
----- ------------ ------------
3 f92 TT(1 行受影响)
*/