SQL计算问题

Question

请问select dazhbiha,xihaaaaa,tupahaaa,mazhaaaa,huzhbiha from tw_reciss_packing where dazhbiha='DIS1108250211'
我想知道根据相同的huzhbiha，算出xihaaaaa,tupahaaa的count数，mazhaaaa的SUM数，
xihaaaaa,tupahaaa必须是不重复的才相加
写来写去就是不对，急啊，求教~！

Answer 1

select 
COUNT(DISTINCT tupahaaa),COUNT(DISTINCT mazhaaaa),
huzhbiha 
from tw_reciss_packing 
where dazhbiha='DIS1108250211'
GROUP BY huzhbiha

Answer 2

select count(distinct xihaaaaa),count(distinct tupahaaa),sum(distinct mazhaaaa),huzhbiha 
 from tw_reciss_packing where dazhbiha='DIS1108250211'
group by huzhbiha 

Answer 3

select 
COUNT(DISTINCT tupahaaa),sum(DISTINCT mazhaaaa),
huzhbiha 
from tw_reciss_packing 
where dazhbiha='DIS1108250211'
GROUP BY huzhbiha

Answer 4

select
  huzhbiha,
  count(xihaaaaa),
  count(tupahaaa),
  sum(mazhaaaa)
from
   tw_reciss_packing 
where
   dazhbiha='DIS1108250211'
group by
   huzhbiha

Answer 5

select
  huzhbiha,
  count(distinct xihaaaaa),
  count(distinct tupahaaa),
  sum(distinct mazhaaaa)
from
   tw_reciss_packing 
where
   dazhbiha='DIS1108250211'
group by
   huzhbiha

Answer 6

xihaaaaa,tupahaaa必须是不重复的才相加
--------------
這樣?測測看是不是這樣，只顯示不重復的
select huzhbiha,
COUNT(DISTINCT tupahaaa),sum(DISTINCT mazhaaaa)
from tw_reciss_packing  AS a
where a.dazhbiha='DIS1108250211'
AND NOT EXISTS(SELECT 1 FROM tw_reciss_packing WHERE huzhbiha=a.huzhbiha AND xihaaaaa=a.xihaaaaa AND tupahaaa=a.tupahaaa HAVING COUNT(1)>1 )

Answer 7

谢谢，如果huzhbiha有A,B,C,D 4列，把A以外的3列在合并呢？

Answer 8

也就是查询出的结果然后有A,B,C,D 4列，把A以外的3列在合并，最终只呈现2列数据

Answer 9

這樣?
select huzhbiha,
CASE WHEN huzhbiha='A'  THEN COUNT(xihaaaaa) else COUNT(DISTINCT xihaaaaa) end AS xihaaaaa,
CASE WHEN huzhbiha='A'  THEN COUNT(tupahaaa) else COUNT(DISTINCT tupahaaa) end AS tupahaaa,
CASE WHEN huzhbiha='A'  THEN sum(xihaaaaa) ELSE sum(DISTINCT mazhaaaa)  end AS tupahaaa
from tw_reciss_packing 
where dazhbiha='DIS1108250211'
and huzhbiha<>'A'
GROUP BY huzhbiha