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

korall 2013-05-09 03:26:46

有如下的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 条件以及需要聚合的列都是很有可能要有变化的，一但用了函数，那么就可能因为该函数不可控而以后不好修改了)

...全文

217 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

korall 2013-05-11

打赏
举报

本帖结贴，引用的答案如下：

引用 11 楼 daiyueqiang 的回复:

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

谢谢 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

[/quote] ）。同时 14 楼的 rockyljt提供的信息和 17楼 Beirut 提供的方案也能解决问题，尤其是Beirut 说的apply 方式:

引用 17 楼 Beirut 的回复:

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 financeValue from dbo.finance_taking AS fTake where fTake.financeAccId = fAcc.Id AND fTake.[date] > fAcc.closeDate ) app

简洁清晰，应该说正是我所找的，但是因为对SQL 以及 SQL server不是很熟悉，以前我从没用过这个apply，所以之前压根没有想到过apply,去查了一下，很方便的语法。 17楼，你的方案也能简化写法，丹我觉得用其他方法更加正常，同样谢谢最后谢谢本帖所有的参与者。至于join方案和 aplly 方案的效率，我不是老手，无法做出比较好的对比，看以后的经验吧

korall 2013-05-11

打赏
举报

引用 17 楼 Beirut 的回复:

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 financeValue from dbo.finance_taking AS fTake where fTake.financeAccId = fAcc.Id AND fTake.[date] > fAcc.closeDate ) app

谢谢

korall 2013-05-11

打赏
举报

引用 14 楼 rockyljt 的回复:

指定临时命名的结果集，这些结果集称为公用表表达式 (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 )

谢谢

黄_瓜 2013-05-10

打赏
举报

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 financeValue from dbo.finance_taking AS fTake where fTake.financeAccId = fAcc.Id AND fTake.[date] > fAcc.closeDate ) app

---涛声依旧--- 2013-05-10

打赏
举报

解决问题就好，然后再考虑效率

黄_瓜 2013-05-10

打赏
举报

改成 cross apply

daiyueqiang2045 2013-05-10

打赏
举报

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

youqi1984 2013-05-10

打赏
举报

觉得静态SQL更好

korall 2013-05-10

打赏
举报

引用 8 楼 tangguangqiang 的回复:


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)

我想要的不仅仅是这样；不过这确实能解决问题

---涛声依旧--- 2013-05-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 tb with 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

---涛声依旧--- 2013-05-10

打赏
举报

指定临时命名的结果集，这些结果集称为公用表表达式 (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 )

korall 2013-05-10

打赏
举报

引用 11 楼 daiyueqiang 的回复:

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

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

习惯性蹭分 2013-05-09

打赏
举报

上面写错了个地方。晕死


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)

习惯性蹭分 2013-05-09

打赏
举报

引用 6 楼 korall 的回复:

[quote=引用 4 楼 tangguangqiang 的回复:] 要不用动态拼接。

啥意思？怎么弄？[/quote]


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)

不知道这样是不是楼主想要的。

korall 2013-05-09

打赏
举报

引用 4 楼 tangguangqiang 的回复:

要不用动态拼接。

啥意思？怎么弄？

korall 2013-05-09

打赏
举报

不用写这么一大串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 其实已经差不多就是聚合函数了；函数可以传递参数进去，可子查询貌似不给力。不知还有其他办法没？

习惯性蹭分 2013-05-09

打赏
举报

要不用动态拼接。

guguda2008 2013-05-09

打赏
举报

最后那个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

guguda2008 2013-05-09

打赏
举报

你是想知道简化的写法？只是写法？

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