求高人解答SQL问题

2014-09-20 10:35:40 198 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

三级等保网络拓扑图实战：从零搭建符合标准的医院安全架构本文详细阐述了如何从零开始设计并搭建符合三级等保标准的医院网络安全架构。文章基于“一个中心，三重防护”的核心框架，深入解析了医院网络分区设计、安全域划分、关键安全设备选型与配置等实战要点，并提供了绘制专业网络拓扑图的技巧与等保测评准备指南，旨在帮助医院IT人员系统化地构建既合规又实用的纵深防御体系。阅读详情

SELECT [One_project],[Two_project],[Three_project],[XM_ID],[TR_Year],SUM (S_TR) AS S_TRCount,SUM (ZY_TR) AS ZY_TRCount,SUM (HY_TR) AS HY_TRCount,SUM (QT_TR) AS QT_TRCount,SUM(TR_SUM) AS sum_TRCount,GROUPING(XM_ID),GROUPING(TR_Year) FROM [input] join Input_User on input.ID = Input_User.inputID  WHERE Input_User.HeadID =" + userID + "and XM_ID IS not NULL GROUP BY One_project,Two_project,Three_project,XM_ID,TR_Year WITH rollup HAVING XM_ID IS NOT NULL

其中Input_User表中有五条数据

，input表里有一条数据

。
查询的结果是

，
但是我想要的结果是

求高人帮我解答。

...全文

在Matlab中开发AutoSAR模型的流程（自下而上）前面介绍了AutoSAR软件组件开发的两种方式，其中自下向上的方式，是从模型出发，流程如下。阅读详情

witeem

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

witeem 2014-09-21

打赏
举报

引用 8 楼 alimake 的回复:

你的400是怎么来的。 with input_user as (select 7 as id, 7 inputid,2 as headid,3 as memberid union all select 8 as id, 7 inputid,2 as headid,6 as memberid union all select 9 as id, 7 inputid,2 as headid,4 as memberid union all select 10 as id, 7 inputid,2 as headid,4 as memberid union all select 11 as id, 7 inputid,2 as headid,4 as memberid ), cte1 as (select inputid,MIN(id) as minid from input_user group by inputid), 我这个分组不就是去掉IDPUTID重复的数据吗对于INPUTID重复的数据值保留ID最小的一行。如果你还需要HEARID的信息。不用分组。用个基于OVER的的MIN函数就OK 了。咋部队。你发出来这个EXCEL表。不能在SQL里面写个CTE吗？这样大家用起来也方便。

谢谢，问题已经解决了。

xiaodongni 2014-09-21

打赏
举报

给楼下的提供下测试代码


with  input_user as
(select 7 as id, 7 inputid,2 as headid,3 as memberid union all
 select 8 as id, 7 inputid,2 as headid,6 as memberid union all
 select 9 as id, 7 inputid,2 as headid,4 as memberid union all
 select 10 as id, 7 inputid,2 as headid,4 as memberid union all
 select 11 as id, 7 inputid,2 as headid,4 as memberid ),
 input as 
 (select 7 as id,56 as xm_id,2010 as tr_year ,
 400.00 as s_tr, null as zy_tr,null as hy_tr,null as qt_tr ,
 400.00 as tr_sum, '子项目'as one_project,'adminaid' as two_project
 , 'BB' as three_project
 , 1 as tr_moth )

xiaodongni 2014-09-21

打赏
举报

信息真乱，这点数据看了半天才看懂。你这个连接只有input.id=input_user.inputid1对多的连接。在求和肯定是2000了。你在WHERE 后面价格input_user.id=7 1对1的连接就可以了。


SELECT [One_project],[Two_project],[Three_project],[XM_ID],[TR_Year],
 SUM (S_TR) AS S_TRCount,
 SUM (convert(int,ZY_TR)) AS ZY_TRCount,
 SUM (convert(int,HY_TR)) AS HY_TRCount,
 SUM (convert(int,QT_TR)) AS QT_TRCount,
 SUM(TR_SUM) AS sum_TRCount,GROUPING(XM_ID),GROUPING(TR_Year) 
 FROM [input] join Input_User on input.ID = Input_User.inputID  
 WHERE Input_User.HeadID =2 
 GROUP BY One_project,Two_project,Three_project,
 XM_ID,TR_Year WITH rollup 
 having xm_id is not null

witeem 2014-09-21

打赏
举报

input表

witeem 2014-09-21

打赏
举报

引用 8 楼 alimake 的回复:

你的400是怎么来的。

with input_user as
(select 7 as id, 7 inputid,2 as headid,3 as memberid union all
select 8 as id, 7 inputid,2 as headid,6 as memberid union all
select 9 as id, 7 inputid,2 as headid,4 as memberid union all
select 10 as id, 7 inputid,2 as headid,4 as memberid union all
select 11 as id, 7 inputid,2 as headid,4 as memberid ),
cte1 as
(select inputid,MIN(id) as minid from input_user
group by inputid),
我这个分组不就是去掉IDPUTID重复的数据吗对于INPUTID重复的数据值保留ID最小的一行。如果你还需要HEARID的信息。
不用分组。用个基于OVER的的MIN函数就OK 了。咋部队。
你发出来这个EXCEL表。不能在SQL里面写个CTE吗？这样大家用起来也方便。

那我就多给出一组数据吧input表

input_user表

执行SQL语句后结果是

我要实现的是input_user表里面的每个inputID都只有一条数据参与统计

xiaodongni 2014-09-21

打赏
举报

你的400是怎么来的。 with input_user as (select 7 as id, 7 inputid,2 as headid,3 as memberid union all select 8 as id, 7 inputid,2 as headid,6 as memberid union all select 9 as id, 7 inputid,2 as headid,4 as memberid union all select 10 as id, 7 inputid,2 as headid,4 as memberid union all select 11 as id, 7 inputid,2 as headid,4 as memberid ), cte1 as (select inputid,MIN(id) as minid from input_user group by inputid), 我这个分组不就是去掉IDPUTID重复的数据吗对于INPUTID重复的数据值保留ID最小的一行。如果你还需要HEARID的信息。不用分组。用个基于OVER的的MIN函数就OK 了。咋部队。你发出来这个EXCEL表。不能在SQL里面写个CTE吗？这样大家用起来也方便。

witeem 2014-09-21

打赏
举报

引用 6 楼 alimake 的回复:



with  input_user as

(select 7 as id, 7 inputid,2 as headid,3 as memberid union all

 select 8 as id, 7 inputid,2 as headid,6 as memberid union all

 select 9 as id, 7 inputid,2 as headid,4 as memberid union all

 select 10 as id, 7 inputid,2 as headid,4 as memberid union all

 select 11 as id, 7 inputid,2 as headid,4 as memberid ),

 cte1 as 

 (select inputid,MIN(id) as minid from input_user 

 group by inputid),、

--CTE1就是对INPUT_USER进行处理的。取出来每个INPUTID对于的最小ID，你后面只要和这个表进行连接就OK 了。

看来你还没有理解我的意思，Input_User表中可能还会有

我最终想要的结果是input表进行分组统计。我想要去掉Input_User表里面有些字段值是重复的数据

xiaodongni 2014-09-21

打赏
举报


with  input_user as
(select 7 as id, 7 inputid,2 as headid,3 as memberid union all
 select 8 as id, 7 inputid,2 as headid,6 as memberid union all
 select 9 as id, 7 inputid,2 as headid,4 as memberid union all
 select 10 as id, 7 inputid,2 as headid,4 as memberid union all
 select 11 as id, 7 inputid,2 as headid,4 as memberid ),
 cte1 as 
 (select inputid,MIN(id) as minid from input_user 
 group by inputid),、
--CTE1就是对INPUT_USER进行处理的。取出来每个INPUTID对于的最小ID，你后面只要和这个表进行连接就OK 了。

witeem 2014-09-21

打赏
举报

引用 4 楼 alimake 的回复:

[quote=引用 3 楼 u012224214 的回复:] [quote=引用 2 楼 alimake 的回复:] 给楼下的提供下测试代码


with  input_user as
(select 7 as id, 7 inputid,2 as headid,3 as memberid union all
 select 8 as id, 7 inputid,2 as headid,6 as memberid union all
 select 9 as id, 7 inputid,2 as headid,4 as memberid union all
 select 10 as id, 7 inputid,2 as headid,4 as memberid union all
 select 11 as id, 7 inputid,2 as headid,4 as memberid ),
 input as 
 (select 7 as id,56 as xm_id,2010 as tr_year ,
 400.00 as s_tr, null as zy_tr,null as hy_tr,null as qt_tr ,
 400.00 as tr_sum, '子项目'as one_project,'adminaid' as two_project
 , 'BB' as three_project
 , 1 as tr_moth )

这只是一部分数据而已，还有其他的数据。Input_User表中的数据不是固定不变的[/quote] 这个没影响了你可以把表INPut_user处理下。对inputid进行分组。每组只保留最小的一个。这样还是1对1的关系啊[/quote] 请问该怎么写

xiaodongni 2014-09-21

打赏
举报

引用 3 楼 u012224214 的回复:

[quote=引用 2 楼 alimake 的回复:] 给楼下的提供下测试代码


with  input_user as
(select 7 as id, 7 inputid,2 as headid,3 as memberid union all
 select 8 as id, 7 inputid,2 as headid,6 as memberid union all
 select 9 as id, 7 inputid,2 as headid,4 as memberid union all
 select 10 as id, 7 inputid,2 as headid,4 as memberid union all
 select 11 as id, 7 inputid,2 as headid,4 as memberid ),
 input as 
 (select 7 as id,56 as xm_id,2010 as tr_year ,
 400.00 as s_tr, null as zy_tr,null as hy_tr,null as qt_tr ,
 400.00 as tr_sum, '子项目'as one_project,'adminaid' as two_project
 , 'BB' as three_project
 , 1 as tr_moth )

这只是一部分数据而已，还有其他的数据。Input_User表中的数据不是固定不变的[/quote] 这个没影响了你可以把表INPut_user处理下。对inputid进行分组。每组只保留最小的一个。这样还是1对1的关系啊

witeem 2014-09-21

打赏
举报

引用 2 楼 alimake 的回复:

给楼下的提供下测试代码


with  input_user as
(select 7 as id, 7 inputid,2 as headid,3 as memberid union all
 select 8 as id, 7 inputid,2 as headid,6 as memberid union all
 select 9 as id, 7 inputid,2 as headid,4 as memberid union all
 select 10 as id, 7 inputid,2 as headid,4 as memberid union all
 select 11 as id, 7 inputid,2 as headid,4 as memberid ),
 input as 
 (select 7 as id,56 as xm_id,2010 as tr_year ,
 400.00 as s_tr, null as zy_tr,null as hy_tr,null as qt_tr ,
 400.00 as tr_sum, '子项目'as one_project,'adminaid' as two_project
 , 'BB' as three_project
 , 1 as tr_moth )

这只是一部分数据而已，还有其他的数据。Input_User表中的数据不是固定不变的

求高人解答SQL问题

相关推荐