导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

高分求一个SQL语句的写法!!

jonsonzxw 2003-08-09 11:37:19
有两个表Customers和表Exploit

表Customers中有字段:

userid company parentcompany
1 广州 广东
2 增城 广州
3 佛山 广东
4 顺德 佛山
5 南海 佛山
6 花都 广州

以上字段均为varchar

表Exploit有字段
userid datayear datamonth field1 ... field 100
1 2003 1 100 ... 50
1 2003 2 30 ... 70
2 2003 1 35 40
3 2003 1 29 34
4 2003 2 60 25
6 2003 1 10 10
2 2003 4 10 5

... ...

我想要提取 2003年度 广州 的数据,因为广州包含了增城与花都,故想要显示的结果如下:

userid field1 ... field100
广州 130 120 (12个月累加)
增城 45 45 (12个月累加)
花都 10 10 12个月累加)

因为显示的是广州及属下的数据,故佛山等其它地它的数据不要显示出来。

谢谢!!


...全文
9 点赞 收藏 21
写回复
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
erigido 2003-08-10
declare @Customers table(userid int,company nvarchar(50),parentcompany nvarchar(50))
insert into @customers select 1, '广州', '广东'
insert into @customers select 2, '增城', '广州'
insert into @customers select 3, '佛山', '广东'
insert into @customers select 4, '顺德', '佛山'
insert into @customers select 5, '南海', '佛山'
insert into @customers select 6, '花都', '广州'


declare @Exploit table(userid int,datayear varchar(4),datamonth varchar(2), field1 int, field100 int)
insert into @exploit select 1, '2003', '1', 100, 50
insert into @exploit select 1, '2003', '2', 30 , 70
insert into @exploit select 2, '2003', '1', 35 , 40
insert into @exploit select 3, '2003', '1', 29 , 34
insert into @exploit select 4, '2003', '2', 60 , 25
insert into @exploit select 6, '2003', '1', 10 , 10
insert into @exploit select 2, '2003', '4', 10 , 5

select a.userid,a.company,sum(field1) as field1,sum(field100) as field100
from
(select userid,company from @customers where company='广州' or parentcompany='广州')a,
@exploit b
where a.userid=b.userid and b.datayear='2003'
group by a.userid,a.company,b.datayear
order by a.userid
回复
nboys 2003-08-10
declare @Customers table(userid int,company nvarchar(50),parentcompany nvarchar(50))
insert into @customers select 1, '广州', '广东'
insert into @customers select 2, '增城', '广州'
insert into @customers select 3, '佛山', '广东'
insert into @customers select 4, '顺德', '佛山'
insert into @customers select 5, '南海', '佛山'
insert into @customers select 6, '花都', '广州'


declare @Exploit table(userid int,datayear varchar(4),datamonth varchar(2), field1 int, field100 int)
insert into @exploit select 1, '2003', '1', 100, 50
insert into @exploit select 1, '2003', '2', 30 , 70
insert into @exploit select 2, '2003', '1', 35 , 40
insert into @exploit select 3, '2003', '1', 29 , 34
insert into @exploit select 4, '2003', '2', 60 , 25
insert into @exploit select 6, '2003', '1', 10 , 10
insert into @exploit select 2, '2003', '4', 10 , 5


select company,sum(field1) as fielda,sum(field100) as field100 from (select distinct a.userid,a.company,a.parentcompany from @Customers a join @Exploit b on a.userid=b.userid where a.parentcompany='广州' or a.company='广州') tem
join (select * from @Exploit where datayear='2003') b on tem.userid=b.userid group by company
回复
nboys 2003-08-10
declare @Customers table(userid int,company nvarchar(50),parentcompany nvarchar(50))
insert into @customers select 1, '广州', '广东'
insert into @customers select 2, '增城', '广州'
insert into @customers select 3, '佛山', '广东'
insert into @customers select 4, '顺德', '佛山'
insert into @customers select 5, '南海', '佛山'
insert into @customers select 6, '花都', '广州'


declare @Exploit table(userid int,datayear varchar(4),datamonth varchar(2), field1 int, field100 int)
insert into @exploit select 1, '2003', '1', 100, 50
insert into @exploit select 1, '2003', '2', 30 , 70
insert into @exploit select 2, '2003', '1', 35 , 40
insert into @exploit select 3, '2003', '1', 29 , 34
insert into @exploit select 4, '2003', '2', 60 , 25
insert into @exploit select 6, '2003', '1', 10 , 10
insert into @exploit select 2, '2003', '4', 10 , 5


select company,sum(field1) as fielda,sum(field100) as field100 from (select distinct a.userid,a.company,a.parentcompany from @Customers a join @Exploit b on a.userid=b.userid where a.parentcompany='广州' or a.company='广州') tem
join @Exploit b on tem.userid=b.userid group by company


结果:

Company field1 field100
广州 130 120
花都 10 10
增城 45 45
回复
nboys 2003-08-10

select company,sum(field1) as fielda,sum(field100) as field100 from (select distinct a.userid,a.company,a.parentcompany from @Customers a join @Exploit b on a.userid=b.userid where a.parentcompany='广州' or a.company='广州') tem
join @Exploit b on tem.userid=b.userid group by company
回复
mtou 2003-08-10
select Customers.company as userid,A.field1,field2 from Customers
INNER join(select userid,sum(field1) as field1,sum(field2) as field2
from Exploit where Exploit.datayear=N'2003' group by Exploit.userid)A
on A.userid=Customers.userid
where Customers.company=N'广州' or Customers.parentcompany
=N'广州'
回复
vchoushen6 2003-08-10
select a.company,b.field1sum,...,b.field100sum
from Customers a
left outer join (select userid,sum(field1) field1sum
,...,
sum(field100) fieldsum100
from Exploit group by userid ) b
on a.userid=b.userid
where '广州' in (parentcompany,company)
回复
pengdali 2003-08-10
SELECT (select company from Customers where userid=tem.userid) company,sum(field1) field1,sum(field100) field100 from Exploit tem WHERE datayear='2003' AND userid in ( SELECT userid FROM Customers WHERE '广州' in (parentcompany,company)) GROUP BY userid
回复
yangvxin1 2003-08-10
对了,erigido。你不是搞C#的吗?有没QQ号?
相互学习学习。
回复
yangvxin1 2003-08-10
向农民学习。呵呵
回复
yoki 2003-08-09
如果第2个表名不确定,其字段个数也不确定的话可以这样

1:创建存储过程

create proc GetRecord(@TableName varchar(50),@Year varchar(4),@City varchar(50))
as
begin
declare @sql varchar(8000)
select @sql=''

DECLARE xcursor CURSOR
FOR
select name from syscolumns
where id=object_id('exploit') and name<>'datamonth' and name<>'userid' and name<>'datayear'

DECLARE @name varchar(40)
OPEN xcursor
FETCH NEXT FROM xcursor INTO @name
WHILE (@@fetch_status =0)
BEGIN
SELECT @sql=@sql +','+'sum('+@name +') as ' +@name
FETCH NEXT FROM xcursor INTO @name
END
CLOSE xcursor
DEALLOCATE xcursor

if @sql<>''
begin
select @sql='select a.userid,a.company '+@sql
select @sql=@sql+' from(select userid,company from customers where company='''+@city+''' or parentcompany='''+@city+''') a,'
select @sql=@sql+@tablename +' b where a.userid=b.userid and b.datayear='''+ @year +''' group by a.userid,a.company,b.datayear order by a.userid'
end
exec (@sql)
end

2:调用存储过程(第1个参数表示表名,第2个参数表示你要统计的年份,第3个参数表示你要统计的城市)
GetRecord 'Exploit','2003','广州'

回复
你的表字段不固定的话,就写个存储过程,用动态生成SQL语句的方法来实现啦.

--创建存储过程
create proc test
@tbname varchar(200) --要查询的表名
as
set nocount on
declare @sql varchar(8000)
set @sql='select a.company as userid'
select @sql=@sql+',sum(['+name+']) as ['+name+']' from syscolumns
where id=object_id(@tbname) and left(name,5)='field'
set @sql=@sql+' from Customers a,'+@tbname+' b where a.userid=b.userid group by a.company'
exec(@sql)
go


--调用存储过程取得结果
exec test 'Exploit'
回复
yoki 2003-08-09
declare @Customers table(userid int,company nvarchar(50),parentcompany nvarchar(50))
insert into @customers select 1, '广州', '广东'
insert into @customers select 2, '增城', '广州'
insert into @customers select 3, '佛山', '广东'
insert into @customers select 4, '顺德', '佛山'
insert into @customers select 5, '南海', '佛山'
insert into @customers select 6, '花都', '广州'


declare @Exploit table(userid int,datayear varchar(4),datamonth varchar(2), field1 int, field100 int)
insert into @exploit select 1, '2003', '1', 100, 50
insert into @exploit select 1, '2003', '2', 30 , 70
insert into @exploit select 2, '2003', '1', 35 , 40
insert into @exploit select 3, '2003', '1', 29 , 34
insert into @exploit select 4, '2003', '2', 60 , 25
insert into @exploit select 6, '2003', '1', 10 , 10
insert into @exploit select 2, '2003', '4', 10 , 5

select a.userid,a.company,sum(field1) as field1,sum(field100) as field100
from
(select userid,company from @customers where company='广州' or parentcompany='广州')a,
@exploit b
where a.userid=b.userid and b.datayear='2003'
group by a.userid,a.company,b.datayear
order by a.userid
回复
river168 2003-08-09
SELECT sum(field1),... from Exploit WHERE datayear='2003' AND userid in( SELECT * FROM Customers WHERE parentcompany='广州' OR company='广州') GROUP BY userid
回复
jonsonzxw 2003-08-09
但有一个问题可能没讲清,100个字段是不定的,因我有5个象Exploit这样的表,有的只有20个字段,有的是50个,故我想用一个SQL语句表示这5个表,故sum(field1),...,sum(field100)可能不好定,有没有象sum(*)的语句??
回复
river168 2003-08-09
SELECT sum(field1),... from Exploit WHERE datayear='2003' AND userid in( SELECT * FROM WHERE parentcompany='广州' OR company='广州') GROUP BY userid
回复
jonsonzxw 2003-08-09
好些人回答,谢谢,我都来试试先
回复
mjhnet 2003-08-09
select Customers.company,sum(field1),...,sum(field100)
from Exploit
left outer join
Customers on Customers.userid = Exploit.userid
where Customers.datamonth = '2003' and Customers.parentcompany = '广州' or Customers.company = '广州'
group by Customers.company
回复
txlicenhe 2003-08-09
Select '广州' as userid,sum(field1) as field1,sum(field2) as field2...
sum(field100) as field100 from exploit a
join customers b on a.userid = b.userid
where '广州' in (b.company,b.parentcompany)
union all
Select '增城',sum(field1),sum(field2)... sum(field100) from exploit a
join customers b on a.userid = b.userid
where b.company = '增城'
union All
Select '花都',sum(field1),sum(field2)... sum(field100) from exploit a
join customers b on a.userid = b.userid
where b.company = '花都'


回复
jonsonzxw 2003-08-09
能写SQL语句吗? 在线等待!
回复
yanfeishen79 2003-08-09
你可以做个视图吗?
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告