独特的SQL查询问题 [英] distinct sql query problem

问题描述

我有一张桌子

i am having a table

cid in_rid  in_rc rid   rcom       date             mid
1   104     0     106     0       2012-11-07      108
2   103     0     106     0       2012-11-07      108
3   102     0     106     0       2012-11-07      108
4   101     0     106     0       2012-11-07      108
5   104     1     107    100      2012-11-07      109
6   103     2     107    100      2012-11-07      109
7   102     3     107    100      2012-11-07      109
8   101     4     107    100      2012-11-07      109
9   105     1     106    100      2012-11-07      110
10  102     2     106    100      2012-11-07      110
11  101     3     106    100      2012-11-07      110
12  101     1     107    100      2012-11-07      111
NULL    NULL    NULL    NULL    NULL    NULL    NULL

我想运行一个查询以获取总rcom，而无需重复mid

就像在rid = 107上我想要100 + 100，但显示400 + 100

i want to run a query to get total rcom with no repeatation of mid

like on rid=107 i want 100+100 but its showing 400+100

my query is select sum(select distinct(mem_id),rcom from referal_commission where rid=107) from referal_commission

但我想要rcom的总和
有人可以帮忙吗

but i want sum of rcom
can someone plzz help

推荐答案

由于您的表列和查询几乎不匹配
我认为不需要使用group by来区分..

我已经根据您的需求使用2列为您的需求创建了一个测试查询

我想运行查询以获取不重复中间的总rcom"

像这样:

Since your table column and query has few mismatch
I think use of distinct with group by is not required..

I have created a test query for your requirement in sort using 2 columns on basis of your requirement

"i want to run a query to get total rcom with no repeatation of mid"

Like this:

create table #temp(
rcom int,
mid int
)
insert into #temp(rcom,mid)values(0,108)
insert into #temp(rcom,mid)values(0,108)
insert into #temp(rcom,mid)values(100,109)
insert into #temp(rcom,mid)values(100,109)
insert into #temp(rcom,mid)values(100,110)
insert into #temp(rcom,mid)values(100,110)

select * from #temp

select SUM(rcom) as sum_rcom,mid from #temp group by mid

输出为:

sum_rcom mid
0 108
400109
400110

and Output is:

sum_rcom mid
0 108
400 109
400 110

Is this helpful..?

您是否尝试过在Mid列上应用Group by.

Have you try to apply Group by on rid, Mid column.

这篇关于独特的SQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！