group by 问题 解决 立马结贴

happy664618843 2015-05-19 04:41:03 


SELECT '2015Budget' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,

c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus

FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID

inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode

and  c.BudgetVersion='2015Budget'

union all 

SELECT '2015LE1' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,

c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus

FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID

inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode

and  c.BudgetVersion='2015LE1'

查询出来的结果是：


我想将两个不同版本号的同一个项目号放在一起。两个版本的项目负责人和项目成本中心不一样。

比如：
项目号 项目描述 项目负责人 项目成本中心
AM150840 MOVE/黑吉大区管理团队 刘曙光 V61501
AM150840 MOVE/黑吉大区管理团队 骆宽 L24101
...全文
176 15 打赏 收藏 转发到动态 举报
写回复
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
还在加载中灬 2015-05-20
  • 打赏
  • 举报
 回复
那把ORDER BY 移到外面，里面不能有ORDER BY，放到最外面即可
happy664618843 2015-05-20
  • 打赏
  • 举报
 回复
引用 6 楼 ky_min 的回复:
忘了说了，把里面的ORDER BY子句去掉
order by 去掉不行啊，兄弟。两个版本相同的项目不放在一起了。都分开了
happy664618843 2015-05-20
  • 打赏
  • 举报
 回复
引用 6 楼 ky_min 的回复:
忘了说了，把里面的ORDER BY子句去掉
order by 去掉 还能把两个不同版本相同的项目放在一起吗？
还在加载中灬 2015-05-20
  • 打赏
  • 举报
 回复
忘了说了，把里面的ORDER BY子句去掉
happy664618843 2015-05-20
  • 打赏
  • 举报
 回复
引用 4 楼 ky_min 的回复:
--方法1
;WITH T AS(
	...
)
SELECT * FROM T T1
WHERE EXISTS(SELECT 1 FROM T T2 WHERE T1.ProjectCode=T2.ProjectCode
	AND T1.版本号<>T2.版本号)
--方法2
;WITH T AS(
	...
)
,T2 AS(
	SELECT *,COUNT(1)OVER(PARTITION BY ProjectCode)C
	FROM T
)
SELECT * FROM T2 WHERE C>1
我就你这个方案1实验了下： WITH T AS( SELECT '2015Budget' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees where EmployeeID=c.ProjectOwner) ChineseName, c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter , isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus FROM vw_getOpenProjectList a left join MDE_Employees as b on a.AppEmployeeID=b.EmployeeID inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode and c.BudgetVersion='2015Budget' union all SELECT '2015LE1' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees where EmployeeID=c.ProjectOwner) ChineseName, c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter , isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus FROM vw_getOpenProjectList a left join MDE_Employees as b on a.AppEmployeeID=b.EmployeeID inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode and c.BudgetVersion='2015LE1' ORDER BY ProjectCode ) SELECT * FROM T T1 WHERE EXISTS(SELECT 1 FROM T T2 WHERE T1.ProjectCode=T2.ProjectCode AND T1.版本号<>T2.版本号) 报错 as below: Msg 1033, Level 15, State 1, Line 15 除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
还在加载中灬 2015-05-20
  • 打赏
  • 举报
 回复 
--方法1
;WITH T AS(
	...
)
SELECT * FROM T T1
WHERE EXISTS(SELECT 1 FROM T T2 WHERE T1.ProjectCode=T2.ProjectCode
	AND T1.版本号<>T2.版本号)
--方法2
;WITH T AS(
	...
)
,T2 AS(
	SELECT *,COUNT(1)OVER(PARTITION BY ProjectCode)C
	FROM T
)
SELECT * FROM T2 WHERE C>1
happy664618843 2015-05-20
  • 打赏
  • 举报
 回复
引用 1 楼 ky_min 的回复:
是ORDER BY吧
SELECT '2015Budget' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,
c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus
FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID
inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode
and  c.BudgetVersion='2015Budget'
union all 
SELECT '2015LE1' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,
c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus
FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID
inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode
and  c.BudgetVersion='2015LE1'  
ORDER BY ProjectCode
亲 咱过虑出在2015Budget和2015LE1共同有的项目号？
happy664618843 2015-05-20
  • 打赏
  • 举报
 回复
引用 14 楼 ky_min 的回复:
create Proc Pro_CompareProjectInfoByBudVersion ( @budgetVersion1 varchar(20), @budgetVersion2 varchar(20) ) AS WITH T AS
谢谢，解决了
还在加载中灬 2015-05-20
  • 打赏
  • 举报
 回复
create Proc Pro_CompareProjectInfoByBudVersion ( @budgetVersion1 varchar(20), @budgetVersion2 varchar(20) ) AS WITH T AS
happy664618843 2015-05-20
  • 打赏
  • 举报
 回复
存储过程不能用WITH？
引用 12 楼 ky_min 的回复:
那就再加一个排序条件 order by T1.ProjectCode,T1.版本号
create Proc Pro_CompareProjectInfoByBudVersion
(
 @budgetVersion1 varchar(20),
 @budgetVersion2 varchar(20)
)
WITH T AS(
  SELECT @budgetVersion1 as budgetVersion, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,
c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus
FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID
inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode
and  c.BudgetVersion='2015Budget' 
union  all 

SELECT  @budgetVersion2 as budgetVersion, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,
c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus
FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID
inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode
and  c.BudgetVersion='2015LE1'   

)
SELECT * FROM T T1
WHERE EXISTS(SELECT 1 FROM T T2 WHERE T1.ProjectCode=T2.ProjectCode
    AND T1.budgetVersion<>T2.budgetVersion  ) order by T1.ProjectCode,T1.budgetVersion
还在加载中灬 2015-05-20
  • 打赏
  • 举报
 回复
那就再加一个排序条件 order by T1.ProjectCode,T1.版本号
卖水果的net 2015-05-20
  • 打赏
  • 举报
 回复 
order by T1.ProjectCode, 版本号
happy664618843 2015-05-20
  • 打赏
  • 举报
 回复
引用 9 楼 ky_min 的回复:
那把ORDER BY 移到外面，里面不能有ORDER BY，放到最外面即可

亲 我按照你的方案1执行，并且 order by 移到外面。但出现结果有点差别
WITH T AS(

  SELECT '2015Budget' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,

c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus

FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID

inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode

and  c.BudgetVersion='2015Budget' 

union  all 



SELECT '2015LE1' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,

c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus

FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID

inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode

and  c.BudgetVersion='2015LE1'   



)

SELECT * FROM T T1

WHERE EXISTS(SELECT 1 FROM T T2 WHERE T1.ProjectCode=T2.ProjectCode

    AND T1.版本号<>T2.版本号  ) order by T1.ProjectCode

查询出来数据有点不对。咱是
2015Budget
2015LE1
2015LE1
2015Budget 这种格式不对。


需要展示结果应该是
2015Budget ....
2015LE1
2015Budget
2015LE1
.....
shoppo0505 2015-05-19
  • 打赏
  • 举报
 回复
你把原来的一个SQL语句作为subselect，在外面再order by
还在加载中灬 2015-05-19
  • 打赏
  • 举报
 回复
是ORDER BY吧
SELECT '2015Budget' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,
c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus
FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID
inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode
and  c.BudgetVersion='2015Budget'
union all 
SELECT '2015LE1' as 版本号, a.AutoID, a.ProjectCode,ProjectDescription, (select ChineseName from MDE_Employees  where EmployeeID=c.ProjectOwner)  ChineseName,
c.Costcenter,AccountingName, CompanyName,typeName, ProjectStausName,c.BudgetAmount BudgetAmount,(ISNULL(c.BudgetAmount,a.BudgetAmount)-RecordedAmount-PromisedAmount-ISNULL(AccruedAmount,0)-isnull(FrozenAmount,0)) as BalanceAmount, PromisedAmount, RecordedAmount,AccruedAmount, FromDate,ToDate, (case when UseCostcenter='0' then '所有成本中心'else UseCostcenter end) UseCostcenter ,  isnull((AppEmployeeID+'-'+b.ChineseName),'') AppEmployeeID,ApplicationDate,'己批准' as ProjectStatus
FROM vw_getOpenProjectList a  left join  MDE_Employees as b on a.AppEmployeeID=b.EmployeeID
inner JOIN tbProjectBudgetInfo c ON a.ProjectCode=c.ProjectCode
and  c.BudgetVersion='2015LE1'  
ORDER BY ProjectCode
相关推荐
结贴之后就再没人理我了
在这里发过两个求助贴，两次结贴后都又追问了问题。但是让我不解的是，为什么结贴之后的问题就再没人回我了？ 原来论坛的每个版块的文章都会自动分类，区别待解决的，已结贴的，精华贴等贴子的不同属性。默认的分类是“待解决”。当我结贴之后，帖子进入已结贴，当然没人会再去已结贴分类中翻了。
游戏引擎遇到贴图问题 求解
给精灵贴图时，精灵背景互相遮挡，求解？   已解决，结贴！
nginx+thinkphp下解决不支持pathinfo模式
这篇文章主要介绍了nginx+thinkphp下解决不支持pathinfo模式的相关资料,需要的朋友可以参考下 nginx环境问题弄了两天，发现网上很多人的帖子要么复制粘贴，要么就是没有结贴，还自己写一句“哈哈，我自己解决了”之类的，这点我就鄙视这些发帖的同胞了，你说你问题问了，问题解决了也不给个解释。还在搜索的前几页，想没想过需要答案的人的感受呢。满心欢喜的去看答案，发现没有结贴。好吧，不多说，...
php 不支持 pathinfo,nginx+thinkphp下解决不支持pathinfo模式
这篇文章主要介绍了nginx+thinkphp下解决不支持pathinfo模式的相关资料,需要的朋友可以参考下nginx环境问题弄了两天，发现网上很多人的帖子要么复制粘贴，要么就是没有结贴，还自己写一句“哈哈，我自己解决了”之类的，这点我就鄙视这些发帖的同胞了，你说你问题问了，问题解决了也不给个解释。还在搜索的前几页，想没想过需要答案的人的感受呢。满心欢喜的去看答案，发现没有结贴。好吧，不多说，进...
结贴的问题
每次结贴 都很头痛。我觉得技术交流群不要这样结贴给分的机制
智能路由器

675

社区成员

911

社区内容

发帖
与我相关
我的任务
社区描述
智能路由器通常具有独立的操作系统，包括OpenWRT、eCos、VxWorks等，可以由用户自行安装各种应用，实现网络和设备的智能化管理。
linuxpython 技术论坛（原bbs）
社区管理员
  • 智能路由器社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告