select a.col2 as 电压,substring(d.yd_code,1,2) as 所属用电分类,a.gs as 客户个数,c.num as 用电量
from (select col2,count(*) as gs from table1 group by col2) a,
(select hk_id,sum(num) as num from table3 group by hk_id) c,
table2 b,table1 d
where a.col2=d.col2 and c.hk_id=b.hk_id and b.yd_code=d.yd_code
from (select col2,count(*) as gs from table1 group by col2) a,
(select hk_id,sum(num) as num from table3 group by hk_id) c,
table2 b,table1 d
where a.col2=d.col2 and c.hk_id=b.hk_id and b.yd_code=d.yd_code
解决方案 »
- 服务器上的数据库如何防止串改
- 使用Rs.AddNew为何提示ADODB.Recordset (0x800A0CB3),在线等!!
- select 问题
- 我想学SQL SERVER技术,需不需要一台服务器?
- 这个简单的过程,为什么@sms_price 值求不出来呢?高手?
- 如何让sqlserver不产生日志?
- 一列中的多关联
- windows 2003 64bi t操作系统下的 SQL2005 内存使用问题
- 如何返回表中记录的同时带上该列的行记录
- sql server 2000 reporting service提示没有安装asp.net1.1
- 创建居部变量@var1,@var2,并赋值,然后输出变量的值
- ORACLE中,如何在一个表中找到某一列最大的两条记录?例如找出两条登记号最大的记录?注:登记号整型且唯一
同一电压可能有多个用户。
3 125 0202
4 126 0202--对应表3
3 125 50
4 126 60 --50+60=110--而你统计的结果:
20kv 02 3 221你不觉得问题描述有问题吗?
Insert T (KH_ID, YD_CODE) Select KH_ID, YD_CODE From 表2
Select Sum(num) As num, KH_ID as KH_ InTo #表3 From 表3 group by KH_ID
Update T Set 用电量 = num From #表3 Where KH_ID = KH_
Update T Set 电压 = col2 From 表1 a, T b Where a.YD_CODE = b.YD_CODE
Select Count(left(YD_CODE, 2)) as num, left(YD_CODE, 2) as YD_ into #num From T
Group by left(YD_CODE, 2))
Update T Set 客户个数= num, 所属用电分类 = left(YD_CODE, 2) From #num
where left(YD_CODE, 2) = YD_
Select Max(电压) as 电压, Sum(用电量) as 用电量, 所属用电分类, 客户个数 From T
Group by 所属用电分类, 客户个数Drop table T, #表3, #num
insert into @tb1 select
'01' , N'居民照明','' union all select
'0101' , N'居民1', '2kv' union all select
'0102' , N'居民2', '10kv' union all select
'02' , N'商业','' union all select
'0201' , N'商业1', '5kv' union all select
'0202' , N'商业2', '20kv'
declare @tb2 table (ID int, KH_ID varchar(10), YD_CODE varchar(10))
insert into @tb2 select
1 , '123' , '0101' union all select
2 , '124' , '0101' union all select
3 , '125' , '0202' union all select
4 , '126' , '0202' union all select
5 , '128' , '0201'
declare @tb3 table(ID int, KH_ID varchar(10), num int)
insert into @tb3 select
1 , '123' , 80 union all select
2 , '123' , 100 union all select
2 , '123' , 140 union all select
3 , '124' , 50 union all select
3 , '125' , 50 union all select
4 , '126' , 60 union all select
5 , '128' , 111select a.电压,a.所属用电分类,(select count(*) from @tb2 where YD_CODE=a.YD_CODE) as 客户个数,
(select isnull(sum(isnull(num,0)),0) from @tb3 where KH_ID in(select KH_ID from @tb2 where YD_CODE=a.YD_CODE )) as 用电量
from
(select distinct col2 as 电压,substring(yd_code,1,2) as 所属用电分类 ,YD_CODE from @tb1 where col2<>'') a
order by 所属用电分类--结果
电压 所属用电分类 客户个数 用电量
---------- ------ ----------- -----------
10kv 01 0 0
2kv 01 2 370
20kv 02 2 110
5kv 02 1 111(4 row(s) affected)
Insert T (KH_ID, YD_CODE) Select KH_ID, YD_CODE From 表2
Select Sum(num) As num, KH_ID as KH_ InTo #表3 From 表3 group by KH_ID
Update T Set 用电量 = num From #表3 Where KH_ID = KH_
Update T Set 电压 = col2 From 表1 a, T b Where a.YD_CODE = b.YD_CODE
Select Count(left(YD_CODE, 2)) as num, left(YD_CODE, 2) as YD_ into #num From T
Group by left(YD_CODE, 2))
Update T Set 客户个数= num, 所属用电分类 = left(YD_CODE, 2) From #num
where left(YD_CODE, 2) = YD_
Select 电压, Sum(用电量) as 用电量, 所属用电分类, 客户个数 From T
Group by 电压, 所属用电分类, 客户个数Drop table T, #表3, #num