3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
高分求一个SQL语句的写法！！
有两个表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个月累加）
因为显示的是广州及属下的数据，故佛山等其它地它的数据不要显示出来。
谢谢！！
表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个月累加）
因为显示的是广州及属下的数据，故佛山等其它地它的数据不要显示出来。
谢谢！！
...全文
请发表友善的回复…
发表回复
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
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
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
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'广州'
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)
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','广州'
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'
--创建存储过程
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
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
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 = '花都'
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
你可以做个视图吗？