高分求一个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个月累加)

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

谢谢!!


...全文
32 21 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
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','广州'

zjcxc 元老 2003-08-09
  • 打赏
  • 举报
回复
你的表字段不固定的话,就写个存储过程,用动态生成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
  • 打赏
  • 举报
回复
你可以做个视图吗?
加载更多回复(1)

34,837

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧