100分求指教 谢谢！

Question

[Shop_Id] 商店ID [DailySales_Profit] 营业额 [Write_time] 记录时间  [Shop_Industry] 所属行业[Shop_Id] [DailySales_Profit] [Write_time] [Shop_Industry]
    1             20           2009-05-12      餐饮
    1             30           2009-05-11      餐饮
    1             20           2008-05-12      餐饮
    2             15           2009-05-12      百货
    2             25           2009-05-11      百货
    2             30           2008-05-12      百货
..最终结果比如查看2009-05-12的记录
得到的是
[Shop_Id] 今天销售额  去年今天销售额   今年累计(2009-01-01到2009-05-12)  去年累计(2008-01-01到2008-05-12)
   1         20            20                    sum                             sum
   2         15            30                    sum                             sum

Answer 1

SELECT 
SHOP_ID,
(SELECT SUM(DailySales_Profit) FROM TB WHERE Write_time='2009-05-12' AND SHOP_ID=T.SHOP_ID)AS 今天销售额,
(SELECT SUM(DailySales_Profit) FROM TB WHERE Write_time='2008-05-12' AND SHOP_ID=T.SHOP_ID)AS 去年今天销售额,
(SELECT SUM(DailySales_Profit) FROM TB WHERE DATEDIFF(YY,Write_time,'2009-05-12')=0 AND SHOP_ID=T.SHOP_ID)AS 今年累计销售额,
(SELECT SUM(DailySales_Profit) FROM TB WHERE DATEDIFF(YY,Write_time,'2009-05-12')=1 AND SHOP_ID=T.SHOP_ID)AS 去年累计销售额
FROM TB T

Answer 2

sum(case when   then else end)

Answer 3

SELECT 
distinct
SHOP_ID,
(SELECT SUM(DailySales_Profit) FROM TB WHERE Write_time='2009-05-12' AND SHOP_ID=T.SHOP_ID)AS 今天销售额,
(SELECT SUM(DailySales_Profit) FROM TB WHERE Write_time='2008-05-12' AND SHOP_ID=T.SHOP_ID)AS 去年今天销售额,
(SELECT SUM(DailySales_Profit) FROM TB WHERE DATEDIFF(YY,Write_time,'2009-05-12')=0 AND SHOP_ID=T.SHOP_ID)AS 今年累计销售额,
(SELECT SUM(DailySales_Profit) FROM TB WHERE DATEDIFF(YY,Write_time,'2009-05-12')=1 AND SHOP_ID=T.SHOP_ID)AS 去年累计销售额
FROM TB T

Answer 4

select
  [Shop_Id],
  (select [DailySales_Profit] from tb where [Write_time]='2009-05-12') as 今天销售额,
  (select [DailySales_Profit] from tb where [Write_time]='2008-05-12') as 今天销售额,
  (select year([Write_time]),sum([DailySales_Profit]) as [sum] from tb group by year([Write_time])) as 今年累计,
  (select year([Write_time])-1,sum([DailySales_Profit]) as [sum] from tb group by year([Write_time])-1) as 去年累计
group by
   [Shop_Id],

Answer 5

修改一下 有点问题
select
  [Shop_Id],
  (select [DailySales_Profit] from tb where [Write_time]='2009-05-12') as 今天销售额,
  (select [DailySales_Profit] from tb where [Write_time]='2008-05-12') as 今天销售额,
  (select sum([DailySales_Profit])  from tb where DATEDIFF(YY,Write_time,'2009-05-12')=0 AND SHOP_ID=T.SHOP_ID ) as 今年累计,
  (select sum([DailySales_Profit])  from tb where DATEDIFF(YY,Write_time,'2009-05-12')=1 AND SHOP_ID=T.SHOP_ID ) as 去年累计
from
  tb t

Answer 6

笨办法一个declare @TB table([Shop_Id] int,[DailySales_Profit] int,[Write_time] datetime,[Shop_Industry] varchar(4))
insert @TB
select 1,20,'2009-05-12','餐饮' union all
select 1,30,'2009-05-11','餐饮' union all
select 1,20,'2008-05-12','餐饮' union all
select 2,15,'2009-05-12','百货' union all
select 2,25,'2009-05-11','百货' union all
select 2,30,'2008-05-12','百货'
DECLARE @DATE DATETIME
SET @DATE = '2009-5-12'SELECT DISTINCT T1.Shop_Id, T1.DailySales_Profit, T2.DailySales_Profit, T3.SUM1, T4.SUM1
FROM @TB T1 INNER JOIN (
SELECT Shop_Id, DailySales_Profit
FROM @TB
WHERE [Write_time] = DATEADD(Y,-1,@DATE)
) AS T2 ON T1.Shop_Id = T2.Shop_Id
INNER JOIN (
SELECT Shop_Id, SUM(DailySales_Profit) AS SUM1
FROM @TB
WHERE [Write_time] BETWEEN '2009-1-1' AND @DATE
GROUP BY Shop_Id
) AS T3 ON T1.Shop_Id = T3.Shop_Id
INNER JOIN (
SELECT Shop_Id, SUM(DailySales_Profit) AS SUM1
FROM @TB
WHERE [Write_time] BETWEEN '2008-1-1' AND DATEADD(Y,-1,@DATE)
GROUP BY Shop_Id
) AS T4 ON T1.Shop_Id = T4.Shop_Id
WHERE [Write_time] = @DATE--------------------(6 row(s) affected)
Shop_Id     DailySales_Profit DailySales_Profit SUM1        SUM1
----------- ----------------- ----------------- ----------- -----------
1           20                30                50          50
2           15                25                40          55(2 row(s) affected)

Answer 7

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Shop_Id] int,[DailySales_Profit] int,[Write_time] datetime,[Shop_Industry] varchar(4))
insert [TB]
select 1,20,'2009-05-12','餐饮' union all
select 1,30,'2009-05-11','餐饮' union all
select 1,20,'2008-05-12','餐饮' union all
select 2,15,'2009-05-12','百货' union all
select 2,25,'2009-05-11','百货' union all
select 2,30,'2008-05-12','百货'declare @s datetime
set @s='2009-05-12'select  [Shop_Id],
今天销售额=sum(case when datediff(dd,Write_time,@s)=0 then DailySales_Profit else 0 end),
去年今天销售额=sum(case when datediff(yy,Write_time,@s)=1 then DailySales_Profit else 0 end),
今年累计=sum(case when datediff(dd,Write_time,@s) between 1 and datepart(dy,@s) then DailySales_Profit else 0 end),
去年累计=sum(case when datediff(dd,Write_time,dateadd(yy,-1,@s)) between 1 and datepart(dy,dateadd(yy,-1,@s)) then DailySales_Profit else 0 end)
from [TB]
group by Shop_Id

Answer 8

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Shop_Id] int,[DailySales_Profit] int,[Write_time] datetime,[Shop_Industry] varchar(4))
insert [tb]
select 1,20,'2009-05-12','餐饮' union all
select 1,30,'2009-05-11','餐饮' union all
select 1,20,'2008-05-12','餐饮' union all
select 2,15,'2009-05-12','百货' union all
select 2,25,'2009-05-11','百货' union all
select 2,30,'2008-05-12','百货'
 
---查询---
declare @dt datetime
set @dt='2009-05-12'
select 
  [Shop_Id],
  今天销售额=sum(DailySales_Profit),
  去年今天销售额=(select sum(DailySales_Profit) from tb where Shop_Id=t.Shop_Id and year(Write_time)=year(t.Write_time)-1 and month(Write_time)=month(t.Write_time) and day(Write_time)=day(t.Write_time)),
  今年累计=(select sum(DailySales_Profit) from tb where Shop_Id=t.Shop_Id and year(Write_time)=year(t.Write_time) and Write_time<=@dt),
  去年累计=(select sum(DailySales_Profit) from tb where Shop_Id=t.Shop_Id and year(Write_time)=year(t.Write_time)-1 and Write_time<=dateadd(yy,-1,@dt))
from
  tb t
where 
  datediff(dd,Write_time,@dt)=0
group by
  [Shop_Id]
  ,Write_time---结果---
Shop_Id     今天销售额       去年今天销售额     今年累计        去年累计        
----------- ----------- ----------- ----------- ----------- 
1           20          20          50          20
2           15          30          40          30（所影响的行数为 2 行）

Answer 9

/*
最终结果比如查看2009-05-12的记录 
得到的是 
[Shop_Id] 今天销售额  去年今天销售额  今年累计(2009-01-01到2009-05-12)  去年累计(2008-01-01到2008-05-12) 
  1        20            20                    sum                            sum 
  2        15            30                    sum                            sum
*/--> 测试数据: @tb
declare @tb table ([Shop_Id] int,[DailySales_Profit] int,[Write_time] datetime,[Shop_Industry] varchar(4))
insert into @tb
select 1,20,'2009-05-12','餐饮' union all
select 1,30,'2009-05-11','餐饮' union all
select 1,20,'2008-05-12','餐饮' union all
select 2,15,'2009-05-12','百货' union all
select 2,25,'2009-05-11','百货' union all
select 2,30,'2008-05-12','百货'declare @time datetime set @time='2009-5-12'select [Shop_Id],
[今天销售额]=max(case when datediff(day,[Write_time],@time)=0 then [DailySales_Profit] else 0 end),
[去年今天销售额]=max(case when datediff(year,[Write_time],@time)=1 then [DailySales_Profit] else 0 end),
[今年累计]=sum(case when datediff(year,[Write_time],@time)=0 and datediff(day,[Write_time],@time)>=0  then [DailySales_Profit] else 0 end),
[去年累计]=sum(case when datediff(year,[Write_time],@time)=1 and datediff(day,[Write_time],dateadd(year,-1,@time))>=0  then [DailySales_Profit] else 0 end )
from @tb
group by [Shop_Id]Shop_Id     今天销售额       去年今天销售额     今年累计        去年累计
----------- ----------- ----------- ----------- -----------
1           20          20          50          20
2           15          30          40          30(2 行受影响)

Answer 10

这个看的清楚
/*
最终结果比如查看2009-05-12的记录 
得到的是 
[Shop_Id] 今天销售额  去年今天销售额  今年累计(2009-01-01到2009-05-12)  去年累计(2008-01-01到2008-05-12) 
  1        20            20                    sum                            sum 
  2        15            30                    sum                            sum
*/--> 测试数据: @tb
declare @tb table ([Shop_Id] int,[DailySales_Profit] int,[Write_time] datetime,[Shop_Industry] varchar(4))
insert into @tb
select 1,20,'2009-05-12','餐饮' union all
select 1,30,'2009-05-11','餐饮' union all
select 1,20,'2008-05-12','餐饮' union all
select 2,15,'2009-05-12','百货' union all
select 2,25,'2009-05-11','百货' union all
select 2,20,'2009-05-13','百货' union all ---添加test，
select 2,30,'2008-05-12','百货'declare @time datetime set @time='2009-5-12'select [Shop_Id],
[今天销售额]=max(case when datediff(day,[Write_time],@time)=0 then [DailySales_Profit] else 0 end),
[去年今天销售额]=max(case when datediff(year,[Write_time],@time)=1 then [DailySales_Profit] else 0 end),
[今年累计]=sum(case when datediff(year,[Write_time],@time)=0 and datediff(day,[Write_time],@time)>=0  then [DailySales_Profit] else 0 end),
[去年累计]=sum(case when datediff(year,[Write_time],@time)=1 and datediff(day,[Write_time],dateadd(year,-1,@time))>=0  then [DailySales_Profit] else 0 end )
from @tb
group by [Shop_Id]Shop_Id     今天销售额       去年今天销售额     今年累计        去年累计
----------- ----------- ----------- ----------- -----------
1           20          20          50          20
2           15          30          40(看这个)          30(2 行受影响)

Answer 11

declare @s datetime
set @s='2009-05-12'select  [Shop_Id],
今天销售额=sum(case when datediff(dd,Write_time,@s)=0 then DailySales_Profit else 0 end),
去年今天销售额=sum(case when datediff(yy,Write_time,@s)=1 then DailySales_Profit else 0 end),
今年累计=sum(case when datediff(dd,Write_time,@s) between 0 and datepart(dy,@s) then DailySales_Profit else 0 end),
去年累计=sum(case when datediff(dd,dateadd(yy,-1,@s),Write_time) between 0 and datepart(dd,dateadd(yy,-1,@s)) then DailySales_Profit else 0 end)
from [TB]
group by Shop_Id
/*
Shop_Id     今天销售额       去年今天销售额     今年累计        去年累计        
----------- ----------- ----------- ----------- ----------- 
1           20          20          50          20
2           15          30          40          30（所影响的行数为 2 行）*/

Answer 12

两种思路，一是建立一个临时表或表变量，然后一列列插入或更新，最后得到所有的记录。
二是用子查询，其实每一　列就是单独一个查询，前一种方法思路清晰，但步骤有些繁琐，第二种SQL语句会比较长，以后维护起来比较困难。
效率吗，可以比较一下。

Answer 13

DECLATE @write_time DATETIME
SET @write_time ='2009-05-12'
SELECT ship_id,
SUM(CASE WHEN DATEDIFF(dd,write_time, @write_time)=0 THEN sales ELSE 0 END) sales_today,
SUM(CASE WHEN DATEADD(yy,-1,@write_time) = write_time THEN sales ELSE 0 END) sales_pre_year_today,
SUM(CASE WHEN write_time BETWEEN CONVERT(VARCHAR(5),@write_time,120) + '01-01' AND @write_time THEN sales ELSE 0 END) sales_YTD,
SUM(CASE WHEN write_time BETWEEN DATEADD(yy,-1,CONVERT(VARCHAR(5),@write_time,120) + '01-01') AND DATEADD(yy,-1,@write_time) THEN sales ELSE 0 END) sales_pre_YTD
FROM tb
WHERE write_time BETWEEN DATEADD(yy,-1,CONVERT(VARCHAR(5),@write_time,120) + '01-01') AND DATEADD(yy,-1,@write_time)
OR
write_time BETWEEN BETWEEN CONVERT(VARCHAR(5),@write_time,120) + '01-01' AND @write_time
GROUP BY ship_id