create table #aaa
(
cust_no varchar(12),
order_no varcaar(12),
do_qty int
)insert into #aaa values('A', 'A33', 10)
insert into #aaa values('A', 'A33', 20)
insert into #aaa values('A', 'A44', 20)
insert into #aaa values('B', 'A55', 10)客户 订单数 出货数
A 2 50
B 1 10请问这句SQL如何完成?
(
cust_no varchar(12),
order_no varcaar(12),
do_qty int
)insert into #aaa values('A', 'A33', 10)
insert into #aaa values('A', 'A33', 20)
insert into #aaa values('A', 'A44', 20)
insert into #aaa values('B', 'A55', 10)客户 订单数 出货数
A 2 50
B 1 10请问这句SQL如何完成?
select cust_no ,count(distinct(order_no)),sum(do_qty) from #aaa group by cust_no
COUNT(DISTINCT order_no) order_no,
SUM(do_qty) do_qty
FROM #aaa
GROUP BY cust_no
from (select cust_no,order_no,sum(do_qty) as do_qty
from #aaa
group by cust_no,order_no) t
group by cust_no
(
cust_no varchar(12),
order_no varchar(12),
do_qty int
) insert into #aaa values('A', 'A33', 10)
insert into #aaa values('A', 'A33', 20)
insert into #aaa values('A', 'A44', 20)
insert into #aaa values('B', 'A55', 10)
SELECT cust_no,COUNT(DISTINCT order_no),SUM(do_qty)
FROM #aaa
GROUP BY cust_no
drop table #aaa
/*cust_no
------------ ----------- -----------
A 2 50
B 1 10(所影响的行数为 2 行)*/
create table #aaa
(
cust_no varchar(12),
order_no varchar(12),
do_qty int
) insert into #aaa values('A', 'A33', 10)
insert into #aaa values('A', 'A33', 20)
insert into #aaa values('A', 'A44', 20)
insert into #aaa values('B', 'A55', 10)
SELECT cust_No as 客户,COUNT(order_no)as 订单数,SUM(do_qty)as 出货数
FROM #aaa
GROUP BY cust_No
select cust_no as 客户,count(distinct order_no) as 订单数,sum(do_qty) as 出货数
from #aaa
group by cust_no
order by cust_no客户 订单数 出货数
------------ ----------- -----------
A 2 50
B 1 10(所影响的行数为 2 行)
group by cust_no
create table #aaa
(
cust_no varchar(12),
order_no varchar(12),
do_qty int
) insert into #aaa values('A', 'A33',10)
insert into #aaa values('A', 'A33',20)
insert into #aaa values('A', 'A44',20)
insert into #aaa values('B', 'A55',10)
------------------------
with C
as
(
select distinct a.cust_no,b.order_no,b.do_qty from #aaa as a
join (select order_no , sum(do_qty) as do_qty
from #aaa group by order_no)b
on a.order_no = b.order_no
)
select cust_no as 客户,count(*) 订单数,sum(do_qty) 出库数
from C group by cust_no
A 2 50
B 1 10
create table #aaa
(
cust_no varchar(12),
order_no varchar(12),
do_qty int
) insert into #aaa values('A', 'A33', 10)
insert into #aaa values('A', 'A33', 20)
insert into #aaa values('A', 'A44', 20)
insert into #aaa values('B', 'A55', 10) select cust_no 客户 , count(distinct order_no) 订单数, sum(do_qty) 出货数
from #aaa
group by cust_no
我也凑凑热闹,嘿嘿...