求高人解答SQL问题

[witeem]

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表里有一条数据。
查询的结果是，
但是我想要的结果是
求高人帮我解答。

[witeem]

引用 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表中的数据不是固定不变的

[xiaodongni]

引用 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]

引用 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]

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]

引用 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]

你的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]

引用 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都只有一条数据参与统计

[witeem]

input表

[xiaodongni]

信息真乱，这点数据看了半天才看懂。 你这个连接只有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

[xiaodongni]

给楼下的提供下测试代码

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 )

[witeem]

引用 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吗？这样大家用起来也方便。

谢谢，问题已经解决了。