SELECT fcustomername as Customer, (SELECT SUM(total) as total1 FROM VIEW_ALL WHERE fcustomername like fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername), (SELECT SUM(total) as total2 FROM VIEW_ALL WHERE fcustomername like fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername) FROM VIEW_ALL group by fcustomername 不能用别名
SELECT fcustomername as Customer, (SELECT SUM(total) as total1 FROM VIEW_ALL a WHERE a.fcustomername like a.Customer and a.fdate >=to_date( '2008-1-1','yyyy-mm-dd') AND a.fdate <= to_date( '2008-11-20','yyyy-mm-dd')GROUP BY a.fcustomername), (SELECT SUM(total) as total2 FROM VIEW_ALL b WHERE b.fcustomername like b.Customer and b.fdate >=to_date( '2007-1-1','yyyy-mm-dd') AND b.fdate <= to_date( '2007-11-20','yyyy-mm-dd')GROUP BY b.fcustomername) FROM VIEW_ALL group by fcustomername 是不是上面这样,日期格式不对
try SELECT fcustomername as Customer, ( SELECT SUM(total) as total1 FROM VIEW_ALL WHERE fcustomername like VIEW_ALL.fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername), (SELECT SUM(total) as total2 FROM VIEW_ALL WHERE fcustomername like VIEW_ALL.fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername) FROM VIEW_ALL group by fcustomername
SELECT fcustomername as Customer, (SELECT SUM(total) as total1 FROM VIEW_ALL WHERE fcustomername like t.fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername), (SELECT SUM(total) as total2 FROM VIEW_ALL WHERE fcustomername like t.fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername) FROM VIEW_ALL t group by fcustomername 加个表别名t,里面用t.fcustomername
declare @t table(FcustomerName nvarchar(10), total int, fdate datetime) insert @t select N'客户1' , 1000 , '2007-10-20' insert @t select N'客户2' , 1500 , '2007-10-15' insert @t select N'客户1' , 2100 , '2008-10-12' insert @t select N'客户2' , 2000 , '2008-10-20' insert @t select N'客户1' , 1200 , '2007-10-18' insert @t select N'客户2' , 1500 , '2008-10-25' SELECT fcustomername as Customer, (SELECT SUM(total) as total1 FROM @t WHERE fcustomername =T. fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername), (SELECT SUM(total) as total2 FROM @t WHERE fcustomername =T.fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername) FROM @t T group by fcustomername /*Customer ---------- ----------- ----------- 客户1 2100 2200 客户2 3500 1500*/
SELECT fcustomername as Customer, (SELECT SUM(total) as total1 FROM VIEW_ALL WHERE fcustomername =T.fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername), (SELECT SUM(total) as total2 FROM VIEW_ALL WHERE fcustomername=T.fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername) FROM VIEW_ALL T group by fcustomername
SELECT fcustomername , (SELECT SUM(total) FROM VIEW_ALL WHERE fcustomername like t.FCustomerName and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername) as total1, (SELECT SUM(total) FROM VIEW_ALL WHERE fcustomername like t.FCustomerName and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername) as total2 FROM VIEW_ALL t group by fcustomername order by total1 desc可以了,只是执行效率好像很低哦,
如何取得total1和total2的差值和比率呢?discount和rate
total1和total2的数据类型是decimal,可能为空,那怎么运算啊?
if object_id('[view_all]') is not null drop table [view_all] go create table [view_all]([fcustomername] varchar(5),[total] int,[fdate] datetime) insert [view_all] select '客户1',1000,'2007-10-20' union all select '客户2',1500,'2007-10-15' union all select '客户1',2100,'2008-10-12' union all select '客户2',2000,'2008-10-20' union all select '客户1',1200,'2007-10-18' union all select '客户2',1500,'2008-10-25'select * from [view_all]select fcustomername,total1,total2,discount=total1-total2,rate=cast(1.0*(total1-total2)/total2 as decimal(18,2)) from ( select fcustomername ,total1=sum(case when fdate >= '2007-1-1' AND fdate <= '2007-11-20' then total else 0 end) ,total2=sum(case when fdate >= '2008-1-1' AND fdate <= '2008-11-20' then total else 0 end) from view_all group by fcustomername ) t --测试结果: /* fcustomername total1 total2 discount rate ------------- ----------- ----------- ----------- ----------- 客户1 2200 2100 100 0.05 客户2 1500 3500 -2000 -0.57(2 行受影响) */
select fcustomername,total1,total2,discount=total1-total2,rate=cast(1.0*(total1-total2)/total2 as decimal(18,2)) from ( select fcustomername ,total1=sum(case when fdate >= '2007-1-1' AND fdate <= '2007-11-20' then isnull(total,0) else 0 end) ,total2=sum(case when fdate >= '2008-1-1' AND fdate <= '2008-11-20' then isnull(total,0) else 0 end) from view_all group by fcustomername ) t
DECLARE @t TABLE([FcustomerName] NVARCHAR(3),[total] INT,[fdate] DATETIME) INSERT @t SELECT N'客户1',1000,'2007-10-20' UNION ALL SELECT N'客户2',1500,'2007-10-15' UNION ALL SELECT N'客户1',2100,'2008-10-12' UNION ALL SELECT N'客户2',2000,'2008-10-20' UNION ALL SELECT N'客户1',1200,'2007-10-18' UNION ALL SELECT N'客户2',1500,'2008-10-25' /************/ /*Test Data*/ /*****fc*****/ /*2008-11-21*/ /************/ SELECT *,total1-total2 discount, rate=RTRIM(CAST((total1-total2) * 1.0/total2*100 AS NUMERIC(10,2))) + '%' FROM ( SELECT FCUstomerName,SUM(CASE WHEN DATEPART(yy,fdate)=2007 THEN total ELSE 0 END) total1, SUM(CASE WHEN DATEPART(yy,fdate)=2008 THEN total ELSE 0 END) total2 FROM @t GROUP BY FCUstomerName ) x /* 客户1 2200 2100 100 4.76% 客户2 1500 3500 -2000 -57.14% */
(SELECT SUM(total) as total1
FROM VIEW_ALL
WHERE fcustomername like fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername),
(SELECT SUM(total) as total2
FROM VIEW_ALL
WHERE fcustomername like fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername)
FROM VIEW_ALL group by fcustomername 不能用别名
(SELECT SUM(total) as total1
FROM VIEW_ALL a
WHERE a.fcustomername like a.Customer and a.fdate >=to_date( '2008-1-1','yyyy-mm-dd') AND a.fdate <= to_date( '2008-11-20','yyyy-mm-dd')GROUP BY a.fcustomername),
(SELECT SUM(total) as total2
FROM VIEW_ALL b
WHERE b.fcustomername like b.Customer and b.fdate >=to_date( '2007-1-1','yyyy-mm-dd') AND b.fdate <= to_date( '2007-11-20','yyyy-mm-dd')GROUP BY b.fcustomername)
FROM VIEW_ALL group by fcustomername 是不是上面这样,日期格式不对
SELECT
fcustomername as Customer,
(
SELECT SUM(total) as total1
FROM VIEW_ALL
WHERE fcustomername like VIEW_ALL.fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername),
(SELECT SUM(total) as total2
FROM VIEW_ALL
WHERE fcustomername like VIEW_ALL.fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername)
FROM VIEW_ALL group by fcustomername
(SELECT SUM(total) as total1 FROM VIEW_ALL WHERE fcustomername like t.fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername),
(SELECT SUM(total) as total2 FROM VIEW_ALL WHERE fcustomername like t.fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername)
FROM VIEW_ALL t
group by fcustomername 加个表别名t,里面用t.fcustomername
客户1 1000 2007-10-20
客户2 1500 2007-10-15
客户1 2100 2008-10-12
客户2 2000 2008-10-20
客户1 1200 2007-10-18
客户2 1500 2008-10-25我要的结果表是:FcustomerName total1 total2 discount rate
客户1 2200 2100 100 (2200-2100)/2100
客户2 1500 3500 -2000 (1500-3500)/3500注:total1(2007年时间段)和total2(2008年时间段)是两个时间段内的销售金额total之和
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。报这个错
报错:服务器: 消息 512,级别 16,状态 1,行 1
子查询返回的值多于一个。当子查询跟随在 =、!=、 <、 <=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。 子查询值不是唯一的
insert @t select N'客户1' , 1000 , '2007-10-20'
insert @t select N'客户2' , 1500 , '2007-10-15'
insert @t select N'客户1' , 2100 , '2008-10-12'
insert @t select N'客户2' , 2000 , '2008-10-20'
insert @t select N'客户1' , 1200 , '2007-10-18'
insert @t select N'客户2' , 1500 , '2008-10-25'
SELECT fcustomername as Customer,
(SELECT SUM(total) as total1
FROM @t
WHERE fcustomername =T. fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername),
(SELECT SUM(total) as total2
FROM @t
WHERE fcustomername =T.fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername)
FROM @t T group by fcustomername
/*Customer
---------- ----------- -----------
客户1 2100 2200
客户2 3500 1500*/
(SELECT SUM(total) as total1
FROM VIEW_ALL
WHERE fcustomername =T.fcustomername and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername),
(SELECT SUM(total) as total2
FROM VIEW_ALL
WHERE fcustomername=T.fcustomername and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername)
FROM VIEW_ALL T group by fcustomername
(SELECT SUM(total)
FROM VIEW_ALL
WHERE fcustomername like t.FCustomerName and fdate >= '2008-1-1' AND fdate <= '2008-11-20' GROUP BY fcustomername) as total1,
(SELECT SUM(total)
FROM VIEW_ALL
WHERE fcustomername like t.FCustomerName and fdate >= '2007-1-1' AND fdate <= '2007-11-20' GROUP BY fcustomername) as total2
FROM VIEW_ALL t group by fcustomername order by total1 desc可以了,只是执行效率好像很低哦,
go
create table [view_all]([fcustomername] varchar(5),[total] int,[fdate] datetime)
insert [view_all]
select '客户1',1000,'2007-10-20' union all
select '客户2',1500,'2007-10-15' union all
select '客户1',2100,'2008-10-12' union all
select '客户2',2000,'2008-10-20' union all
select '客户1',1200,'2007-10-18' union all
select '客户2',1500,'2008-10-25'select * from [view_all]select fcustomername,total1,total2,discount=total1-total2,rate=cast(1.0*(total1-total2)/total2 as decimal(18,2))
from
(
select fcustomername
,total1=sum(case when fdate >= '2007-1-1' AND fdate <= '2007-11-20' then total else 0 end)
,total2=sum(case when fdate >= '2008-1-1' AND fdate <= '2008-11-20' then total else 0 end)
from view_all
group by fcustomername
) t
--测试结果:
/*
fcustomername total1 total2 discount rate
------------- ----------- ----------- ----------- -----------
客户1 2200 2100 100 0.05
客户2 1500 3500 -2000 -0.57(2 行受影响)
*/
select fcustomername,total1,total2,discount=total1-total2,rate=cast(1.0*(total1-total2)/total2 as decimal(18,2))
from
(
select fcustomername
,total1=sum(case when fdate >= '2007-1-1' AND fdate <= '2007-11-20' then isnull(total,0) else 0 end)
,total2=sum(case when fdate >= '2008-1-1' AND fdate <= '2008-11-20' then isnull(total,0) else 0 end)
from view_all
group by fcustomername
) t
INSERT @t SELECT N'客户1',1000,'2007-10-20'
UNION ALL SELECT N'客户2',1500,'2007-10-15'
UNION ALL SELECT N'客户1',2100,'2008-10-12'
UNION ALL SELECT N'客户2',2000,'2008-10-20'
UNION ALL SELECT N'客户1',1200,'2007-10-18'
UNION ALL SELECT N'客户2',1500,'2008-10-25'
/************/
/*Test Data*/
/*****fc*****/
/*2008-11-21*/
/************/
SELECT *,total1-total2 discount,
rate=RTRIM(CAST((total1-total2) * 1.0/total2*100 AS NUMERIC(10,2))) + '%'
FROM
(
SELECT FCUstomerName,SUM(CASE WHEN DATEPART(yy,fdate)=2007 THEN total ELSE 0 END) total1,
SUM(CASE WHEN DATEPART(yy,fdate)=2008 THEN total ELSE 0 END) total2
FROM @t GROUP BY FCUstomerName
) x
/*
客户1 2200 2100 100 4.76%
客户2 1500 3500 -2000 -57.14%
*/