SQL select ：关于SQL子查询 的一个问题

Question

有如下的SQL 语句：
SELECT   fAcc.id, fAcc.bankAccId, fAcc.closeDate, 
         fAcc.closeBalance, fAcc.isActive,
         SUM(fTake.[value]) as financeValue,
         (fAcc.closeBalance + SUM(fTake.[value])) as balance
FROM      dbo.finance_account AS fAcc INNER JOIN
          dbo.finance_taking AS fTake ON fTake.financeAccId = fAcc.Id AND
                                         fTake.[date] > fAcc.closeDate 
GROUP BY fAcc.id, fAcc.bankAccId, fAcc.closeDate, fAcc.closeBalance, fAcc.isActive,fAcc.viewLevel这里 GROUP BY 子句后面列出的一大串完全是笨拙得让人抓急：在select 子句中每增加 finance_account 的一列那就要在 GROUP BY 子句中同时添加一次。怎么写这个 select 才好? (不想用聚合函数，因为这里的join 条件以及需要聚合的列都是很有可能要有变化的，一但用了函数，那么就可能因为该函数不可控而以后不好修改了)SQLselect

Answer 1

你这个group by不多啊，还有更长的你没见过呢，而且这个写法不是你看的不爽就可以不要的，有些写法它就是这样规定的。

Answer 2

你是想知道简化的写法？只是写法？
SELECT   
id
,bankAccId
,closeDate
,closeBalance
,isActive
,viewLevel
,SUM([value]) as financeValue
,(fAcc.closeBalance + SUM(fTake.[value])) as balance
FROM (
select fAcc.id id
,fAcc.bankAccId bankAccId
,fAcc.closeDate closeDate
,fAcc.closeBalance closeBalance
,fAcc.isActive isActive
,fAcc.viewLevel viewLevel
,fTake.[value] [value]
from dbo.finance_account AS fAcc 
INNER JOIN dbo.finance_taking AS fTake 
ON fTake.financeAccId = fAcc.Id AND fTake.[date] > fAcc.closeDate 
) t
GROUP BY 
id
,bankAccId
,closeDate
,closeBalance
,isActive
,viewLevel

Answer 3

最后那个SUM没改，重发一下，顺便说下GROUP BY是必须把所有列写上的，这是规定，再不爽也没办法，我们写的复杂几倍的SQL都有的是，关键还是排列的要好看好找
SELECT   
id
,bankAccId
,closeDate
,closeBalance
,isActive
,viewLevel
,SUM([value]) as financeValue
,(closeBalance + SUM([value])) as balance
FROM (
select fAcc.id id
,fAcc.bankAccId bankAccId
,fAcc.closeDate closeDate
,fAcc.closeBalance closeBalance
,fAcc.isActive isActive
,fAcc.viewLevel viewLevel
,fTake.[value] [value]
from dbo.finance_account AS fAcc 
INNER JOIN dbo.finance_taking AS fTake 
ON fTake.financeAccId = fAcc.Id AND fTake.[date] > fAcc.closeDate 
) t
GROUP BY 
id
,bankAccId
,closeDate
,closeBalance
,isActive
,viewLevel

Answer 4

不用写这么一大串group by 我想到的方法如下，但是有个限制：
SELECT   fAcc.id, fAcc.bankAccId, fAcc.closeDate, 
         fAcc.closeBalance, fAcc.isActive,
         fTake.value as financeValue,
         (fAcc.closeBalance + fTake.value) as balance
FROM      dbo.finance_account AS fAcc INNER JOIN
          (
           SELECT financeAccId, 
                  SUM(value) as value
             FROM dbo.finance_taking 
             GROUP BY financeAccId 
          ) AS fTake ON 
               fTake.financeAccId = fAcc.Id 
            --AND fTake.[date] > fAcc.closeDate  --但是这个条件就不能用了，不能将它搬到子查询里面去好像如果写成这样：
SELECT   fAcc.id, fAcc.bankAccId, fAcc.closeDate, 
         fAcc.closeBalance, fAcc.isActive,
         fTake.value as financeValue,
         (fAcc.closeBalance + fTake.value) as balance
FROM      dbo.finance_account AS fAcc INNER JOIN
          (
           SELECT financeAccId, SUM(value) as value
             FROM dbo.finance_taking as t
             WHERE t.date > fAcc.closeDate  -- 这句跨不过去
             GROUP BY financeAccId 
          ) AS fTake ON fTake.financeAccId = fAcc.Id 其实已经差不多就是聚合函数了；函数可以传递参数进去，可子查询貌似不给力。不知还有其他办法没？

Answer 5

啥意思？怎么弄？declare @val varchar(max)
declare @sql varchar(max)
set @val='facc.id,facc.bankaccid,facc.closedate,
facc.closebalance,facc.isactive'
set @sql='select '+@sql+',sum(ftake.[value]) as financevalue,(
facc.closebalance+sum(ftake.[value])) as balance from dbo.finance_account as
facc inner join dbo.finance_taking as ftake on ftake.financeaccid=
facc.id and ftake.[date]>facc.closedate group by '+@sql
exec (@sql)不知道这样是不是楼主想要的。

Answer 6

上面写错了个地方。晕死declare @val varchar(max)
declare @sql varchar(max)
set @val='facc.id,facc.bankaccid,facc.closedate,
facc.closebalance,facc.isactive'
set @sql='select '+@val+',sum(ftake.[value]) as financevalue,(
facc.closebalance+sum(ftake.[value])) as balance from dbo.finance_account as
facc inner join dbo.finance_taking as ftake on ftake.financeaccid=
facc.id and ftake.[date]>facc.closedate group by '+@val
exec(@sql)

Answer 7

SELECT   fAcc.id, fAcc.bankAccId, fAcc.closeDate, 
          fAcc.closeBalance, fAcc.isActive,
          fTake.value as financeValue,
          (fAcc.closeBalance + fTake.value) as balance
 FROM      dbo.finance_account AS fAcc INNER JOIN
           (
             SELECT financeAccId, SUM(value) as value
              FROM dbo.finance_taking as t inner join finance_account t1 on t.financeAccId=t1.Id
              WHERE t.date > fAcc.closeDate  -- 这句就OK 或者使用cte的方式
              GROUP BY financeAccId 
           ) AS fTake ON fTake.financeAccId = fAcc.Id 
           

Answer 8

谢谢。不知道这样效率怎么样？如果表 fAcc 比较大，会不会出现效率问题？ 还有啥是cte ?

Answer 9

指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。语法[ WITH <common_table_expression> [ ,...n ] ]<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

Answer 10

/*举例
表a中数居如下
code   newcode  num2
a1      123     22
a2      456     23
a3      a1      
a4      a2
a5      a3当necode=code时把num2进行更新。
想要的结果code   newcode  num2
a1      123     22
a2      456     23
a3      a1      22
a4      a2      23
a5      a3      22
*/
create table tb (code varchar(10),newcode varchar(10),num2 int)
insert into tb
select 'a1',      '123',     22 union all select 
'a2',      '456',     23 union all select 
'a3',      'a1',null       union all select 
'a4',      'a2',null union all select 
'a5',      'a3',null
go
--select * from tbwith cte as(
select * from tb where num2 is not null
union all
select t.code,t.newcode,c.num2 from cte c inner join tb t on t.newcode=c.code
)
select * from cte order by code

Answer 11

SELECT   fAcc.id, fAcc.bankAccId, fAcc.closeDate, 
         fAcc.closeBalance, fAcc.isActive,
         app.financeValue,
         (fAcc.closeBalance +  app.financeValue) as balance
FROM      dbo.finance_account AS fAcc 
cross apply 
(select SUM(fTake.[value]) as financeValuefrom   dbo.finance_taking AS fTake where fTake.financeAccId = fAcc.Id AND
                                         fTake.[date] > fAcc.closeDate 
) app 

Answer 12

本帖结贴，引用的答案如下：谢谢 daiyueqiang 提供的参考，11楼答案设为最佳（其中SQL里面有一些笔误,但无妨碍,我修正了如下：SELECT   fAcc.id, fAcc.bankAccId, fAcc.closeDate, 
          fAcc.closeBalance, fAcc.isActive,
          fTake.value as financeValue,
          (fAcc.closeBalance + fTake.value) as balance
 FROM      dbo.finance_account AS fAcc INNER JOIN
           (
             SELECT financeAccId, SUM(value) as value
              FROM dbo.finance_taking as t inner join finance_account t1 on t.financeAccId=t1.Id
              WHERE t.date > t1.closeDate  -- 这句就OK 或者使用cte的方式
              GROUP BY financeAccId 
           ) AS fTake ON fTake.financeAccId = fAcc.Id 
           
）。
同时 14 楼的 rockyljt提供的信息和 17楼 Beirut 提供的方案也能解决问题，尤其是Beirut 说的apply 方式:
简洁清晰，应该说正是我所找的，但是因为对SQL 以及 SQL server不是很熟悉，以前我从没用过这个apply，所以之前压根没有想到过apply,去查了一下，很方便的语法。
17楼，你的方案也能简化写法，丹我觉得用其他方法更加正常，同样谢谢最后谢谢本帖所有的参与者。至于join方案 和 aplly 方案的效率，我不是老手，无法做出比较好的对比，看以后的经验吧