帮忙写个查询语句，谢谢

xxmiaoyong126com 2010-06-30 02:44:49

if object_id('[customer]') is not null drop table [customer]
go
create table customer(fid varchar(20) ,name varchar(20))--客户表
insert into customer values('c1' , '客户1' )
insert into customer values('c2' , '客户2' )
insert into customer values('c3' , '客户3' )
insert into customer values('c4' , '客户4' )
insert into customer values('c5' , '客户5' )
if object_id('[sell]') is not null drop table [sell]
go
create table sell(customer varchar(20) ,mtr varchar(20) , mtime datetime , number decimal(18,2))--销售表
insert into sell values('c1' , 'm1' ,'2010-06-10 00:00:00.000' ,1)
insert into sell values('c1' , 'm2' ,'2010-06-10 00:00:00.000' ,2)
insert into sell values('c2' , 'm1' ,'2010-06-10 00:00:00.000' ,3)
insert into sell values('c2' , 'm3' ,'2010-06-10 00:00:00.000' ,4)
insert into sell values('c2' , 'm2' ,'2010-06-10 00:00:00.000' ,5)
insert into sell values('c3' , 'm2' ,'2010-06-10 00:00:00.000' ,6)
insert into sell values('c2' , 'm2' ,'2010-07-10 00:00:00.000' ,7)
insert into sell values('c2' , 'm3' ,'2010-06-10 00:00:00.000' ,8)
insert into sell values('c4' , 'm2' ,'2010-06-1 00:00:00.000' ,8)
insert into sell values('c1' , 'm1' ,'2010-08-10 00:00:00.000' ,1)
insert into sell values('c1' , 'm2' ,'2010-06-10 00:00:00.000' ,2)
insert into sell values('c2' , 'm1' ,'2010-05-10 00:00:00.000' ,3)
insert into sell values('c2' , 'm3' ,'2010-06-10 00:00:00.000' ,4)
insert into sell values('c2' , 'm2' ,'2010-06-10 00:00:00.000' ,5)
insert into sell values('c3' , 'm2' ,'2010-05-10 00:00:00.000' ,6)
insert into sell values('c2' , 'm2' ,'2010-05-10 00:00:00.000' ,7)
insert into sell values('c2' , 'm3' ,'2010-06-12 00:00:00.000' ,8)
insert into sell values('c4' , 'm2' ,'2010-06-11 00:00:00.000' ,8)
insert into sell values('c4' , 'm4' ,'2010-06-11 00:00:00.000' ,8)
insert into sell values('c4' , 'm4' ,'2010-06-11 00:00:00.000' ,8)
insert into sell values('c4' , 'm4' ,'2010-06-11 00:00:00.000' ,8)

if object_id('[Contrac]') is not null drop table [Contrac]--合同表
go
create table Contrac(customer varchar(20) , stime datetime , quantity int)
insert into Contrac values('c1' ,'2010-06-01 00:00:00.000' ,10)
insert into Contrac values('c2' ,'2010-06-01 00:00:00.000' ,20)
insert into Contrac values('c3' ,'2010-06-01 00:00:00.000' ,30)
insert into Contrac values('c1' ,'2010-07-01 00:00:00.000' ,50)
insert into Contrac values('c2' ,'2010-07-01 00:00:00.000' ,60)
insert into Contrac values('c3' ,'2010-07-01 00:00:00.000' ,70)
go

只查询M1,M2,M3这3种物料2010-6-10发货的数量，结果如下
客户 M1 M2 M3 日累计月累计本月合同量完成率上月累计年累计
客户1 1.00 4.00 0.00 m1+m2+m3 5.00 10 0.5000 0.00 6.00
客户2 3.00 10.00 16.00 m1+m2+m3 37.00 20 1.8500 10.00 54.00
客户3 0.00 6.00 0.00 m1+m2+m3 6.00 30 0.2000 6.00 12.00
客户4 0.00 0.00 0.00 m1+m2+m3 40.00 0 0.0000 0.00 40.00

完成率是月累计/本月合同量

...全文

232 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

Dream_1986 2010-06-30

打赏
举报

路过，做个记号先

振乾 2010-06-30

打赏
举报

[Quote=引用 11 楼 xyj052 的回复:]
SQL code
Declare @date datetime
set @date='2010-06-10'
select a.name,a.fid ,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(b.日累计,0) as '日累计',isnull(d.月累计,0) as '月累计',isnull(c.quant……
[/Quote]

强悍。

xxmiaoyong126com 2010-06-30

打赏
举报

这个，不知道你会不会生气，
因为客户5没有销售记录，没有在SELL表出现过，
所以报表是不需要统计这个客户的。

claro 2010-06-30

打赏
举报

[Quote=引用 9 楼 xxmiaoyong126com 的回复:]

我想问1下，这样多次的left join 结果集表，

是否特别影响SQL语句的执行效率？
[/Quote]是。前提是关联table没有合理的索引。

xyj052 2010-06-30

打赏
举报

Declare @date datetime

set @date='2010-06-10'

select a.name,a.fid ,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(b.日累计,0) as '日累计',isnull(d.月累计,0) as '月累计',isnull(c.quantity ,0)as '本月合同量',isnull(cast(cast(d.月累计 as decimal(18,2))/cast(c.quantity as decimal(18,2)) as decimal(18,2)),0) as '完成率',

isnull(e.上月累计,0) as 上月累计,isnull(f.年累计,0) as '年累计'

 from 

(select * from customer) a  

  left join (

select   customer,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(m1,0)+isnull(m2,0)+isnull(m3,0) as '日累计' from 

	(select b.customer,  mtr,number from sell b  where mtime=@date ) a pivot

	( sum(number)  for mtr in ([m1],[m2],[m3])) as pvt

  ) b on a.fid=b.customer

 left join  

  (select customer,sum(number) as '月累计'  from sell where left(convert(nvarchar(10),mtime,102),7)=left(convert(nvarchar(10),@date,102),7) group by customer )d on a.fid=d.customer

 left join 

  (select customer,sum(quantity) as quantity from Contrac where left(convert(nvarchar(10),stime,102),7)=left(convert(nvarchar(10),@date,102),7) group by customer)c on a.fid=c.customer

left join

  (select customer,sum(number) as '上月累计'  from sell where left(convert(nvarchar(10),mtime,102),7)=left(convert(nvarchar(10),dateadd(month,-1,@date),102),7) group by customer )e on a.fid=e.customer

left join 

  (select customer,sum(number) as '年累计'  from sell where left(convert(nvarchar(10),mtime,102),4)=left(convert(nvarchar(10),@date,102),4) group by customer )f on a.fid=f.customer



name                 fid                  m1                                      m2                                      m3                                      日累计                                     月累计                                     本月合同量       完成率                                     上月累计                                    年累计

-------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------

客户1                  c1                   1.00                                    4.00                                    0.00                                    5.00                                    5.00                                    10          0.50                                    0.00                                    6.00

客户2                  c2                   3.00                                    10.00                                   16.00                                   29.00                                   37.00                                   20          1.85                                    10.00                                   54.00

客户3                  c3                   0.00                                    6.00                                    0.00                                    6.00                                    6.00                                    30          0.20                                    6.00                                    12.00

客户4                  c4                   0.00                                    0.00                                    0.00                                    0.00                                    40.00                                   0           0.00                                    0.00                                    40.00

客户5                  c5                   0.00                                    0.00                                    0.00                                    0.00                                    0.00                                    0           0.00                                    0.00                                    0.00



(5 行受影响)

晕了，这次终于对了.........

xyj052 2010-06-30

打赏
举报

Declare @date datetime

set @date='2010-06-10'

select a.name,a.fid ,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(b.日累计,0),isnull(d.月累计,0),isnull(c.quantity ,0)as '本月合同量',isnull(cast(cast(d.月累计 as decimal(18,2))/cast(c.quantity as decimal(18,2)) as decimal(18,2)),0) as '完成率',

isnull(e.上月累计,0) as 上月累计,isnull(f.年累计,0)

 from 

(select * from customer) a  

  left join (

select   customer,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(m1,0)+isnull(m2,0)+isnull(m3,0) as '日累计' from 

	(select b.customer,  mtr,number from sell b  where mtime=@date ) a pivot

	( sum(number)  for mtr in ([m1],[m2],[m3])) as pvt

  ) b on a.fid=b.customer

 left join  

  (select customer,sum(number) as '月累计'  from sell where left(convert(nvarchar(10),mtime,102),7)=left(convert(nvarchar(10),@date,102),7) group by customer )d on a.fid=d.customer

 left join 

  (select customer,sum(quantity) as quantity from Contrac where left(convert(nvarchar(10),stime,102),7)=left(convert(nvarchar(10),@date,102),7) group by customer)c on a.fid=c.customer

left join

  (select customer,sum(number) as '上月累计'  from sell where left(convert(nvarchar(10),mtime,102),7)=left(convert(nvarchar(10),dateadd(month,-1,@date),102),7) group by customer )e on a.fid=e.customer

left join 

  (select customer,sum(number) as '年累计'  from sell where left(convert(nvarchar(10),mtime,102),4)=left(convert(nvarchar(10),@date,102),4) group by customer )f on a.fid=f.customer





name                 fid                  m1                                      m2                                      m3                                                                                                                      本月合同量       完成率                                     上月累计                                    

-------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------

客户1                  c1                   1.00                                    4.00                                    0.00                                    5.00                                    5.00                                    10          0.50                                    0.00                                    6.00

客户2                  c2                   3.00                                    10.00                                   16.00                                   29.00                                   37.00                                   20          1.85                                    10.00                                   54.00

客户3                  c3                   0.00                                    6.00                                    0.00                                    6.00                                    6.00                                    30          0.20                                    6.00                                    12.00

客户4                  c4                   0.00                                    0.00                                    0.00                                    0.00                                    40.00                                   0           0.00                                    0.00                                    40.00

客户5                  c5                   0.00                                    0.00                                    0.00                                    0.00                                    0.00                                    0           0.00                                    0.00                                    0.00



(5 行受影响)

有个字段取错了，这样就没问题了

xxmiaoyong126com 2010-06-30

打赏
举报

我想问1下，这样多次的left join 结果集表，

是否特别影响SQL语句的执行效率？

xyj052 2010-06-30

打赏
举报

Declare @date datetime

set @date='2010-06-10'

select a.name,b.customer ,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(b.日累计,0),isnull(d.月累计,0),isnull(c.quantity ,0)as '本月合同量',isnull(cast(cast(d.月累计 as decimal(18,2))/cast(c.quantity as decimal(18,2)) as decimal(18,2)),0) as '完成率',

isnull(e.上月累计,0) as 上月累计,isnull(f.年累计,0)

 from 

(select * from customer) a  

  left join (

select   customer,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(m1,0)+isnull(m2,0)+isnull(m3,0) as '日累计' from 

	(select b.customer,  mtr,number from sell b  where mtime=@date ) a pivot

	( sum(number)  for mtr in ([m1],[m2],[m3])) as pvt

  ) b on a.fid=b.customer

 left join  

  (select customer,sum(number) as '月累计'  from sell where left(convert(nvarchar(10),mtime,102),7)=left(convert(nvarchar(10),@date,102),7) group by customer )d on a.fid=d.customer

 left join 

  (select customer,sum(quantity) as quantity from Contrac where left(convert(nvarchar(10),stime,102),7)=left(convert(nvarchar(10),@date,102),7) group by customer)c on a.fid=c.customer

left join

  (select customer,sum(number) as '上月累计'  from sell where left(convert(nvarchar(10),mtime,102),7)=left(convert(nvarchar(10),dateadd(month,-1,@date),102),7) group by customer )e on a.fid=e.customer

left join 

  (select customer,sum(number) as '年累计'  from sell where left(convert(nvarchar(10),mtime,102),4)=left(convert(nvarchar(10),@date,102),4) group by customer )f on a.fid=f.customer

结果：
name customer m1 m2 m3 本月合同量完成率上月累计
-------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------
客户1 c1 1.00 4.00 0.00 5.00 5.00 10 0.50 0.00 6.00
客户2 c2 3.00 10.00 16.00 29.00 37.00 20 1.85 10.00 54.00
客户3 c3 0.00 6.00 0.00 6.00 6.00 30 0.20 6.00 12.00
客户4 NULL 0.00 0.00 0.00 0.00 40.00 0 0.00 0.00 40.00
客户5 NULL 0.00 0.00 0.00 0.00 0.00 0 0.00 0.00 0.00

(5 行受影响)

xyj052 2010-06-30

打赏
举报

Declare @date datetime

set @date='2010-06-10'

select a.name,b.customer ,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(b.日累计,0),isnull(d.月累计,0),isnull(c.quantity ,0)as '本月合同量',isnull(cast(cast(d.月累计 as decimal(18,2))/cast(c.quantity as decimal(18,2)) as decimal(18,2)),0) as '完成率',

isnull(e.上月累计,0) as 上月累计,isnull(f.年累计,0)

 from 

(select * from customer) a  

  left join (

select   customer,isnull(m1,0) as m1,isnull(m2,0) as m2,isnull(m3,0) as m3,isnull(m1,0)+isnull(m2,0)+isnull(m3,0) as '日累计' from 

	(select b.customer,  mtr,number from sell b  where mtime=@date ) a pivot

	( sum(number)  for mtr in ([m1],[m2],[m3])) as pvt

  ) b on a.fid=b.customer

 left join  

  (select customer,sum(number) as '月累计'  from sell where left(convert(nvarchar(10),mtime,102),7)=left(convert(nvarchar(10),@date,102),7) group by customer )d on a.fid=d.customer

 left join 

  (select customer,sum(quantity) as quantity from Contrac where left(convert(nvarchar(10),stime,102),7)=left(convert(nvarchar(10),@date,102),7) group by customer)c on a.fid=c.customer

left join

  (select customer,sum(number) as '上月累计'  from sell where left(convert(nvarchar(10),mtime,102),7)=left(convert(nvarchar(10),dateadd(month,-1,@date),102),7) group by customer )e on a.fid=e.customer

left join 

  (select customer,sum(number) as '年累计'  from sell where left(convert(nvarchar(10),mtime,102),4)=left(convert(nvarchar(10),@date,102),4) group by customer )f on a.fid=f.customer

这样就没问题了
结果如下：
name customer m1 m2 m3 本月合同量完成率上月累计
-------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------
客户1 c1 1.00 4.00 0.00 5.00 5.00 10 0.50 0.00 6.00
客户2 c2 3.00 10.00 16.00 29.00 37.00 20 1.85 10.00 54.00
客户3 c3 0.00 6.00 0.00 6.00 6.00 30 0.20 6.00 12.00
客户4 NULL 0.00 0.00 0.00 0.00 40.00 0 0.00 0.00 40.00
客户5 NULL 0.00 0.00 0.00 0.00 0.00 0 0.00 0.00 0.00

(5 行受影响)

xxmiaoyong126com 2010-06-30

打赏
举报

TO:xys_777
你的代码跟我的要求有点不一样，
你不能where convert(varchar(10),b.mtime,120)='2010-06-10'
客户4 虽然在'2010-06-10'这天没销售，但是也要在报表里显示出来，因为月累计和年累计等字段是有数据的
客户4 0.00 0.00 0.00 m1+m2+m3 40.00 0 0.0000 0.00 40.00

xxmiaoyong126com 2010-06-30

打赏
举报

其实我也有这个困惑，就是代码的可维护性，
可是SQLSERVER很多函数用起来还是很方便直观的啊
如果把数据取过来，再用ASP程序去算数据的话，
感觉很难，没SQLSERVER处理来的简单。

临时表或许是个不错的办法，
下次用用看，

永生天地 2010-06-30

打赏
举报

我也觉得这样一点都不通用

永生天地 2010-06-30

打赏
举报

select a.name,

sum(case when b.mtr='m1' then number else 0 end) as M1,

sum(case when b.mtr='m2' then number else 0 end) as M2,

sum(case when b.mtr='m3' then number else 0 end) as M3,

sum(case when b.mtr in ('m1','m2','m3') then number else 0 end) as 日累计,

d.月累计,

c.quantity 本月合同量,

d.月累计*1.0/c.quantity 完成率,

e.上月累计, 

f.年累计

from [customer] a left join [sell] b on a.fid=b.customer

	left join [Contrac] c on a.fid=c.customer and datediff(month,c.stime,b.mtime)=0

	left join (select customer,convert(varchar(7),mtime,120)+'-01' mtime,

				sum(case when mtr in ('m1','m2','m3') then number else 0 end) 月累计

				from [sell] group by customer,convert(varchar(7),mtime,120)) d 

				on a.fid=d.customer and datediff(month,d.mtime,b.mtime)=0

	left join (select customer,convert(varchar(7),mtime,120)+'-01' mtime,

				sum(case when mtr in ('m1','m2','m3') then number else 0 end) 上月累计

				from [sell] group by customer,convert(varchar(7),mtime,120)) e 

				on a.fid=e.customer and datediff(month,e.mtime,b.mtime)=1

	left join (select customer,convert(varchar(4),mtime,120)+'-01-01' mtime,

				sum(case when mtr in ('m1','m2','m3') then number else 0 end) 年累计

				from [sell] group by customer,convert(varchar(4),mtime,120)) f 

				on a.fid=f.customer and datediff(year,f.mtime,b.mtime)=0

where convert(varchar(10),b.mtime,120)='2010-06-10'

group by 

a.name,

convert(varchar(10),b.mtime,120),

d.月累计,

c.quantity ,

e.上月累计, 

f.年累计

--结果

/*

name                 M1                                      M2                                      M3                                      日累计                                     月累计                                     本月合同量       完成率                                     上月累计                                    年累计

-------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------

客户1                  1.00                                    4.00                                    0.00                                    5.00                                    5.00                                    10          0.500000                                NULL                                    6.00

客户2                  3.00                                    10.00                                   16.00                                   29.00                                   37.00                                   20          1.850000                                10.00                                   54.00

客户3                  0.00                                    6.00                                    0.00                                    6.00                                    6.00                                    30          0.200000                                6.00                                    12.00



(3 行受影响)



*/