表结构如下tba
id a b c d
1840291 -1 e -1 6 tbb
id sa bb cw ff uid
2 4 zz -1 6 1840291tbc
id ae fc fv e uid
2 -1 6 -1 -1 1840291tbd
id ae db xc ze uid
4 4 6 -1 a 1840291想要SQL求出某一ID在若干表中值为-1的字段数量,弄来弄去都觉得不科学,不知道有没有比较简单高效的方法,我是想计算这个ID下-1(表示无数据)字段占总字段的百分比,知道这里牛人多,特求教。
id a b c d
1840291 -1 e -1 6 tbb
id sa bb cw ff uid
2 4 zz -1 6 1840291tbc
id ae fc fv e uid
2 -1 6 -1 -1 1840291tbd
id ae db xc ze uid
4 4 6 -1 a 1840291想要SQL求出某一ID在若干表中值为-1的字段数量,弄来弄去都觉得不科学,不知道有没有比较简单高效的方法,我是想计算这个ID下-1(表示无数据)字段占总字段的百分比,知道这里牛人多,特求教。
用tba来说,是不是a字段=-1,c字段=-1,总共2个字段?
楼主应该把问题描述清楚些
go
create table [tbc] (id int,ae int,fc int,fv int,e int,uid int)
insert into [tbc]
select 2,-1,6,-1,-1,1840291select * from [tbc]
;with CTE
as(
select [value],[name]
from (
select ae,fc,fv,e,[uid]
from tbc)P
unpivot ([value] for [name] in(ae,fc,fv,e,[uid])) t)
select sum(case when value= -1 then 1 else 0 end) as [-1字段],count(1) as [全字段数] from CTE
/*
-1字段 全字段数
3 5*/参考转成列,然后算一下。
if object_id('tba','U') is not null
drop table tba
go
create table tba
(
id int,
a int,
b varchar(10),
c int,
d int
)
go
insert into tba values (1840291,-1,'e',-1,6)
go
if object_id('tbb','U') is not null
drop table tbb
go
create table tbb
(
id int,
sa int,
bb varchar(10),
cw int,
ff int,
uid int
)
go
insert into tbb values (2,4,'zz',-1,6,1840291)
go
if object_id('tbc','U') is not null
drop table tbc
go
create table tbc
(
id int,
ae int,
fc int,
fv int,
e int,
uid int
)
go
insert into tbc values (2,-1,6,-1,-1,1840291)
go
if object_id('tbd','U') is not null
drop table tbd
go
create table tbd
(
id int,
ae int,
db int,
xc int,
ze varchar(10),
uid int
)
go
insert into tbd values (4,4,6,-1,'a',1840291)
gowith cte as
(
select id,c1=cast(a as varchar),c2=cast(b as varchar),c3=cast(c as varchar),c4=cast(d as varchar),c5='' from tba
union all
select id,cast(sa as varchar),cast(bb as varchar),cast(cw as varchar),cast(ff as varchar),cast(uid as varchar) from tbb
union all
select id,cast(ae as varchar),cast(fc as varchar),cast(fv as varchar),cast(e as varchar),cast(uid as varchar) from tbc
union all
select id,cast(ae as varchar),cast(db as varchar),cast(xc as varchar),cast(ze as varchar),cast(uid as varchar) from tbd
)
select count(*) from
(
select id,c1 from cte union all
select id,c2 from cte union all
select id,c3 from cte union all
select id,c4 from cte union all
select id,c5 from cte
) tb where id=1840291 and c1='-1'
/*
-----------
2(1 行受影响)
*/是这样吗
就是这意思,比如tba有3个-1,TB2有2个-1则希望求出5
;with CTE --这里说语法错,不知道这个是什么意思
as(
select [value],[name]
from (
select ae,fc,fv,e,[uid]
from tbc)P
unpivot ([value] for [name] in(ae,fc,fv,e,[uid])) t)select sum(case when value= -1 then 1 else 0 end) as [-1字段],count(1) as [全字段数] from CTE