A 表的字段有id,smkzh
B 表id,smkzh,gcdd
其中有两种情况分别如下:
1. B.gcdd=1 or B.gcdd=12
2. B.gcdd=2 or B.gcdd=9
根据两种情况分别统计count(smkzh),
结果是count(smkzh)第一种情况,count(smkzh)第二种情况,count(smkzh)总和
其中A,B表的关系如下
A.smkzh=b.smkzh
说明以下:A,B数据数分别30万条.
求视图Sql,尽量是高效率!
感谢!
B 表id,smkzh,gcdd
其中有两种情况分别如下:
1. B.gcdd=1 or B.gcdd=12
2. B.gcdd=2 or B.gcdd=9
根据两种情况分别统计count(smkzh),
结果是count(smkzh)第一种情况,count(smkzh)第二种情况,count(smkzh)总和
其中A,B表的关系如下
A.smkzh=b.smkzh
说明以下:A,B数据数分别30万条.
求视图Sql,尽量是高效率!
感谢!
整个看不懂
显示格式要求为:smkzh ,count(smkzh)总,count(smkzh)第一种情况,count(smkzh)第二种情况还有一种构想,分别建三张视图
smkzh ,count(smkzh)总-------第一张smkzh ,count(smkzh)第一种情况-------第二张
smkzh ,count(smkzh)第二种情况-------第三张
小弟水平有限,望大师多多指教!
结果是count(smkzh)第一种情况,count(smkzh)第二种情况,count(smkzh)总和
整个看不懂
也就是视图的列
1. B.gcdd=1 or B.gcdd=12
2. B.gcdd=2 or B.gcdd=9
根据两种情况分别统计count(smkzh),
结果是count(smkzh)第一种情况,count(smkzh)第二种情况,count(smkzh)总和
count(smkzh)总和是count(smkzh)
count(smkzh)第一种情况是count(smkzh) where B.gcdd=1 or B.gcdd=12
count(smkzh)第二种情况是count(smkzh) where B.gcdd=2 or B.gcdd=9
如果a,b表都存在smkzh的多条记录时,有几种取法
create table a(id int ,smkzh varchar(5))
insert a select 1,'a' union all select 2,'a'
union all select 3,'b' union all select 3,'a'create table b(id int ,smkzh varchar(5),gcdd int)
insert b
select 1,'a' ,1 union all select 2,'b',2 union all
select 3,'b',2 union all select 4,'b',2
--drop table a,b
select * from a join b on a.smkzh =b.smkzh
如下情况,楼主是取a表的记录还是b表的记录
id smkzh id smkzh gcdd
----------- ----- ----------- ----- -----------
1 a 1 a 1
2 a 1 a 1
3 a 1 a 1
3 b 2 b 2
3 b 3 b 2
3 b 4 b 2(6 行受影响)
首先表示感谢!
取b表的count(b.smkzh)
视图怎么写?各位大侠,就此问题给出你们的代码!
go
create table B(id int, smkzh int, gcdd int)
goselect
smkzh,
总和=count(*),
第一种情况=sum(case when gcdd in(1,12) then 1 else 0 end),
第二种情况=sum(case when gcdd in(2, 9) then 1 else 0 end)
from B
group by smkzh
[smkzh_1]=(select sum(case when gcdd in (1,12) then 1 else 0 end) from b as c where smkzh =b.smkzh ),
[smkzh_2]=(select sum(case when gcdd in (2,9) then 1 else 0 end) from b as c where smkzh =b.smkzh ),
[总和]=(select count(*) from b as c where smkzh =b.smkzh )
from a join b on a.smkzh =b.smkzh
insert a select 1,'a' union all select 2,'a'
union all select 3,'b' union all select 3,'a'create table b(id int ,smkzh varchar(5),gcdd int)
insert b
select 1,'a' ,1 union all select 2,'b',2 union all
select 3,'b',2 union all select 4,'b',2
--drop table a,bselect b.smkzh,
[smkzh_1]=(select sum(case when gcdd in (1,12) then 1 else 0 end) from b as c where smkzh =b.smkzh ),
[smkzh_2]=(select sum(case when gcdd in (2,9) then 1 else 0 end) from b as c where smkzh =b.smkzh ),
[总和]=(select count(*) from b as c where smkzh =b.smkzh )
from a join b on a.smkzh =b.smkzh
group by b.smkzh--加一个组
smkzh smkzh_1 smkzh_2 总和
----- ----------- ----------- -----------
a 1 0 1
b 0 3 3(2 行受影响)