问题描述
- SQL语句distinct把case when then 把相同的数据给去掉了
-
我SQL语句是这样的 sum(distinct case when e.ifxydw='1' and a.delstate ='0' then b.money else null end) as Xysr, 也碰到了跟上面一样的问题，b.money只要数据相同他就把我的给去掉了，这样我改怎么解决？求大神指教
解决方案
把条件是e.ifxydw='1' and a.delstate ='0' 查出来后SUM一下就不行了。
select sum(money) as Xysr from table where e.ifxydw='1' and a.delstate ='0'
解决方案二：
整段SQL语句就是这样的 select a.jhrq Jhrq, count(distinct case when a.viproomtypeid !='0' and a.delstate ='0' then a.id else 0 end) as YtCount,
count (distinct case when a.PaymentId ='1004' or a.PaymentId ='1005' and a.delstate ='0' then a.id else null end)as CashCount,
sum(distinct case when a.delstate ='0' and b.paymentid='1004' or b.paymentid='1005' then b.money else 0 end ) as Xjsr,
count(distinct case when b.itemsid = '1033' and b.price='400' and a.delstate ='0' and b.paymentid='1004' or b.paymentid='1005' then a.id else null end )as Xjxt,
count(distinct case when b.itemsid = '1033' and b.price='800' and a.delstate ='0' and b.paymentid='1004' or b.paymentid='1005' then a.id else null end )as Xt,
count(distinct case when b.itemsid = '1037' and b.price='750' and a.delstate ='0' and b.paymentid='1004' or b.paymentid='1005' then a.id else null end )as Xjdt,
count(distinct case when b.itemsid = '1037' and b.price='1500' and a.delstate ='0' and b.paymentid='1004' or b.paymentid='1005' then a.id else null end )as Dt,
sum(distinct case when b.itemsid = '1036' and a.delstate ='0' and b.paymentid='1004' or b.paymentid='1005' then b.money else 0 end) as PackMoney,
sum(distinct case when b.itemsid = '1034' and a.delstate ='0' and b.paymentid='1004' or b.paymentid='1005' then b.money else 0 end )as Lead,
count(distinct case when e.ifxydw='1' and a.delstate ='0' and a.webusersubid = c.id then a.id else null end) as XyCount,
sum(distinct case when e.ifxydw='1' and a.delstate ='0' then b.money else null end) as Xysr,
count(distinct case when b.itemsid = '1033' and a.delstate ='0' and b.price='400' and e.ifxydw='1' then a.id else null end )as Xydzxt,
count(distinct case when b.itemsid = '1033' and a.delstate ='0' and b.price='800' and e.ifxydw='1' then a.id else null end )as Xyxt,
count(distinct case when b.itemsid = '1037' and a.delstate ='0' and b.price='750' and e.ifxydw='1' then a.id else null end )as Xydzdt,
count(distinct case when b.itemsid = '1037' and a.delstate ='0' and b.price='1500' and e.ifxydw='1' then a.id else null end )as Xydt,
sum(distinct case when b.itemsid = '1036' and a.delstate ='0' and e.ifxydw='1' then b.money else 0 end) as XyPackMoney,
sum(distinct case when b.itemsid = '1034' and a.delstate ='0' and e.ifxydw='1' then b.money else 0 end )as XyLead,
count(distinct case when d.customerlevelid='1001' or d.customerlevelid='1002' and a.webuserid = d.webuserid and d.delstate='0' then a.id else null end)as VipCount,
count(distinct case when d.customerlevelid='1003' and a.webuserid = d.webuserid and d.delstate='0' then a.id else null end)as CipCount,
count(distinct case when a.hadtypeid='1010' and a.delstate='0' then a.id else null end ) as YhCount,
count(distinct case when a.delstate='0' then a.id else null end) as SumCount
from ml_serviceorder a left join ML_OrderItems b on a.id=b.orderid left join ml_webusersub c on a.webusersubid = c.id
left join ML_Customer d on a.webuserid = d.webuserid left join ML_webuser e on c.webuserid = e.id group by a.jhrq
Order by a.Jhrq desc
解决方案三：
这SQL效率得成什么样呢。
用union的方式。
每个字段都是一个子句。
解决方案四：
既然取sum，为什么还有distinct呢，或者用group by 呢
解决方案五：
distinct 去掉重复的数据，你又不要去掉，你到底要哪样。
贴出完整的sql
完整的数据
以及你希望的结果
解决方案六：
好比我数据库的数据是这样的
ID money
01 100
02 100
因为我是多表连查，所以在查出来的时候相同的数据会出来几次，我就用了distinct，但用distinct会把求money的总和的时候他会把相同的数据去掉，
求和求出来就只有100了，这个怎么解决？求大神指教一下呀
解决方案七：
你关联多表，又不想加WHERE语句防止某些关联表中不符合条件的记录不出来，那么你把条件加到JOIN的ON里面。
如果你的查询不是非常长的话，可以贴出来，说明一下是数据库类型和意图，方便提供解决方案。
解决方案八：
就是对数据进行统计，有多张表关联的，我本来是用distinct把相同数据去掉，结果是把所有相同的数据都给去掉了
就好比这样的表
ID money
01 100
02 100
这是两条数据，多表联查这两条数据就会出来多次，然后我是想要把其他出来的次数给去了，而不是要money下面有两条相同的数据就去掉一个
解决方案九：
distinct 就是取“不同的”，既然不符合你的要求，那就不用就是了
多对多的统计时，应先做预查询以排除干扰