一个特殊的查询，有些麻烦，可能要牵涉到从行到列的转换

[dahuzizyd]

有两个表：
TableA ： 要用到的字段：商铺ID，Money1,Date1 ,
TableB : 要用到的字段：Money2,
TableA中的数据是表示当月每天的金额 ,比如：
12, 20051201 ， 商铺ID1
13, 20051202 ， 商铺ID1
.
.
.
XX, 20051231 商铺ID2
现在要这样：
对12月份的每天的金额显示成这样：
1,2,3,4,5,6,7,8,, ...15 ,上半月总计,16,17,18......31,下半月总计，当月合计
商铺ID1 * * * * *
商铺ID2 * * * * *
星号代表金额，是由TableB的Money2字段- TableA的Money1字段得到的值

[子陌红尘]

select
商铺ID,
[01] = b.Money2-isnull(sum(case day(a.Date1) when 1 then a.Money1 end),0),
[02] = b.Money2-isnull(sum(case day(a.Date1) when 2 then a.Money1 end),0),
...,
[14] = b.Money2-isnull(sum(case day(a.Date1) when 14 then a.Money1 end),0),
[15] = b.Money2-isnull(sum(case day(a.Date1) when 15 then a.Money1 end),0),
[上半月总计]= b.Money2-isnull(sum(case when day(a.Date1) between 16 and 31 then Money1 end),0),
[16] = b.Money2-isnull(sum(case day(a.Date1) when 16 then a.Money1 end),0),
[17] = b.Money2-isnull(sum(case day(a.Date1) when 17 then a.Money1 end),0),
...,
[30] = b.Money2-isnull(sum(case day(a.Date1) when 30 then a.Money1 end),0),
[31] = b.Money2-isnull(sum(case day(a.Date1) when 31 then a.Money1 end),0),
[下半月总计]= b.Money2-isnull(sum(case when day(a.Date1) between 16 and 31 then a.Money1 end),0),
[当月合计] = b.Money2-isnull(sum(a.Money1),0)
from
TableB b,
TableA a,
where
datediff(mi,Date1,'2005-12-01')=0
group by
商铺ID
order by
商铺ID

[子陌红尘]

不明确TableB与TableA怎样关联，假定TableB只有一条记录，则：
-----------------------------------------------------------------------------------------------------------------------------------------------
select
商铺ID,
[01] = b.Money2-isnull(sum(case when day(a.Date1) = 1 then a.Money1 end),0),
[02] = b.Money2-isnull(sum(case when day(a.Date1) = 2 then a.Money1 end),0),
...,
[14] = b.Money2-isnull(sum(case when day(a.Date1) = 14 then a.Money1 end),0),
[15] = b.Money2-isnull(sum(case when day(a.Date1) = 15 then a.Money1 end),0),
[上半月总计]= b.Money2-isnull(sum(case when day(a.Date1) between 16 and 31 then a.Money1 end),0),
[16] = b.Money2-isnull(sum(case when day(a.Date1) = 16 then a.Money1 end),0),
[17] = b.Money2-isnull(sum(case when day(a.Date1) = 17 then a.Money1 end),0),
...,
[30] = b.Money2-isnull(sum(case when day(a.Date1) = 30 then a.Money1 end),0),
[31] = b.Money2-isnull(sum(case when day(a.Date1) = 31 then a.Money1 end),0),
[下半月总计]= b.Money2-isnull(sum(case when day(a.Date1) between 16 and 31 then a.Money1 end),0),
[当月合计] = b.Money2-isnull(sum(a.Money1),0)
from
TableB b,
TableA a,
where
datediff(mi,Date1,'2005-12-01')=0
group by
a.商铺ID
order by
a.商铺ID

[dahuzizyd]

好快啊：）
不好意思，刚才没说全，
在行的最后，还要再加一个合计行，对所有商铺在每一天的金额作统计
1,2,3,4,5,6,7,8,, ...15 ,上半月总计,16,17,18......31,下半月总计，当月合计
商铺ID1 * * * * *
商铺ID2 * * * * *
合计 * * * * *

[dahuzizyd]

先让我好好理解下，我笨：（

[子陌红尘]

select
商铺ID,
[01] = b.Money2-isnull(sum(case when day(a.Date1) = 1 then a.Money1 end),0),
[02] = b.Money2-isnull(sum(case when day(a.Date1) = 2 then a.Money1 end),0),
...,
[14] = b.Money2-isnull(sum(case when day(a.Date1) = 14 then a.Money1 end),0),
[15] = b.Money2-isnull(sum(case when day(a.Date1) = 15 then a.Money1 end),0),
[上半月总计]= b.Money2-isnull(sum(case when day(a.Date1) between 16 and 31 then a.Money1 end),0),
[16] = b.Money2-isnull(sum(case when day(a.Date1) = 16 then a.Money1 end),0),
[17] = b.Money2-isnull(sum(case when day(a.Date1) = 17 then a.Money1 end),0),
...,
[30] = b.Money2-isnull(sum(case when day(a.Date1) = 30 then a.Money1 end),0),
[31] = b.Money2-isnull(sum(case when day(a.Date1) = 31 then a.Money1 end),0),
[下半月总计]= b.Money2-isnull(sum(case when day(a.Date1) between 16 and 31 then a.Money1 end),0),
[当月合计] = b.Money2-isnull(sum(a.Money1),0)
from
TableB b,
TableA a,
where
datediff(mi,Date1,'2005-12-01')=0
group by a.商铺ID with rollup
order by a.商铺ID

[子陌红尘]

select
商铺ID,
[01] = isnull(sum(case when day(a.Date1) = 1 then b.Money2-a.Money1 end),0),
[02] = isnull(sum(case when day(a.Date1) = 2 then b.Money2-a.Money1 end),0),
...,
[14] = isnull(sum(case when day(a.Date1) = 14 then b.Money2-a.Money1 end),0),
[15] = isnull(sum(case when day(a.Date1) = 15 then b.Money2-a.Money1 end),0),
[上半月总计]= isnull(sum(case when day(a.Date1) between 16 and 31 then b.Money2-a.Money1 end),0),
[16] = isnull(sum(case when day(a.Date1) = 16 then b.Money2-a.Money1 end),0),
[17] = isnull(sum(case when day(a.Date1) = 17 then b.Money2-a.Money1 end),0),
...,
[30] = isnull(sum(case when day(a.Date1) = 30 then b.Money2-a.Money1 end),0),
[31] = isnull(sum(case when day(a.Date1) = 31 then b.Money2-a.Money1 end),0),
[下半月总计]= isnull(sum(case when day(a.Date1) between 16 and 31 then b.Money2-a.Money1 end),0),
[当月合计] = isnull(sum(b.Money2-a.Money1),0)
from
TableB b,
TableA a,
where
datediff(mi,Date1,'2005-12-01')=0
group by a.商铺ID with rollup
order by a.商铺ID

[dahuzizyd]

TableA和TableB使用日期进行关联

[dahuzizyd]

用group By的时候提示a.Money1,b.Money2,a.Date1不在聚合函数内？

[子陌红尘]

把两个表的表结构完整的贴出来，提供少量测试数据。

[dahuzizyd]

方便留下联系方式吗？我把东西发过去

[ccty3239968]

我看过用WHILE写@的，才10条不到的代码，迟些发上来，

[dahuzizyd]

期待楼上的代码

[子陌红尘]

邮件请发到：[e-mail]

[dahuzizyd]

TableA中的数据是表示当月每天的金额 ,比如：
12, 20051201 ， 商铺ID1
13, 20051202 ， 商铺ID1
.
.
.
XX, 20051231 商铺ID1
12, 20051201 ， 商铺ID2
13, 20051202 ， 商铺ID2
.
.
.
XX, 20051231 商铺ID2