---------------------------------
A B C D 最小包数 数量
1 1 1 1 20 100
1 1 1 1 20 200
1 1 1 1 30 300
1 1 1 1 50 400
1 1 1 1 50 100
1 1 1 1 50 200
1 1 1 1 50 300
2 2 2 2 30 300
2 2 2 2 30 300
2 2 2 2 30 300
---------------------------------
最小包数是根据数目出现的频率次数最高来决定，相同的时候取第一个，
数量根据A,B,C,D汇总成总数后。sql语句怎么写？
A B C D 最小包数 总数
1 1 1 1 50 1600
2 2 2 2 30 900
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-12 17:01:59
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([A] int,[B] int,[C] int,[D] int,[最小包数] int,[数量] int)
insert [huang]
select 1,1,1,1,20,100 union all
select 1,1,1,1,20,200 union all
select 1,1,1,1,30,300 union all
select 1,1,1,1,50,400 union all
select 1,1,1,1,50,100 union all
select 1,1,1,1,50,200 union all
select 1,1,1,1,50,300 union all
select 2,2,2,2,30,300 union all
select 2,2,2,2,30,300 union all
select 2,2,2,2,30,300
--------------生成数据--------------------------
select a,b,c,d,MAX([最小包数])最小包数,SUM([数量]) 总数
from [huang]
GROUP BY a,b,c,d
----------------结果----------------------------
/*
a b c d 最小包数 总数
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 50 1600
2 2 2 2 30 900
*/
------解决方案--------------------
select a,b,c,d,max(最小包数) as 最小包数,sum(数量) as 数量 from 表 group by a,b,c,d
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-03-12 17:02:55
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)