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
...全文
180 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
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
SQL Server数据库基础到精通课程
变量定义，字段约束、等等，Select 、Where、Top、Group By 、Order By，Distinct、Declare等，详细可看图片和课程，本课程带有完整的SQL数据库课程课件，代码有注释解析等等，学习起来简单易懂，易上手直接操作，...
python series和dataframe groupby操作以后多了一列索引的问题
晚上加班搞了2个小时没弄明白，回家以后网上一搜，看到一篇帖子，立马解决了我的问题 对series和dataframe进行groupby操作以后的对象总是多了一行索引，以前就遇到过这个问题i，一直没弄明白，原来是一个参数没弄对 df.groupby(by=‘A’, group_keys=False) 只要加group_keys=False这个参数就可以了，顺便说一下，by这个参数不光可以是columns，还可以是axis_name（行或者列的name） 哎，还是太菜了。。。 ...
linux mysql 8.0环境group by强校验，windows 目前没发现，可能也有同样的问题
linux mysql 8.0环境group by强校验，windows 目前没发现，可能也有同样的问题
MAC中Mysql的GROUP BY关闭only_full_group_by模式方法 Variable ‘sql_mode‘ can‘t be set
Variable ‘sql_mode‘ can‘t be set to the value of ‘NO_AUTO_CR 有一天公司同事告诉我 select的时候group by中的字段可以不包含select的字段。好绕口，就是简单来说你查询的字段可以在group by中不出现，这么描述懂了吧。那接下来我们一起看看在MAC 环境下怎么设置。 就我个人而言，我之前安装的5.7 然后同事说都有8版本的MYSQL 了，我丢。我立马下载了新的版本，我安装的是8.0.22版本 言归正传，首先，你需要安装MYS.
sql的学习（having,group by, sq的执行顺序，自定义group by）
1. having与sum的区别？ having:对上面已经分组的数据进行过滤的条件 （对分组后的数据进行统计） 2. group by all? 3. group by group by的话 会把第一条遇到的数据，作为展示进行筛选出来。 group by entity_id;把相同entity_id重复的合并 常用的意义： 4.聚合函数first？ 5. co...
智能路由器

679

社区成员

913

社区内容

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

加载中

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

试试用AI创作助手写篇文章吧

+ 用AI写文章