Employees table:
id last_name trainer sales
1 Hardy Bob 3
2 Wallace Greg 3
3 Green Bob 2
4 Marsh Andy 2
5 Brown Greg 0
Sales table:
cust_id sold_by sales_amt
1 1 $6,000
2 1 $6,000
3 4 $8,000
4 2 $4,000
5 2 $6,000
6 3 $7,000
7 4 $4,000
8 1 $6,000
9 2 $7,000
10 3 $9,000要查询的结果是:(1)
emp_id last_name trainer sales cust_id sold_by sales_amt
1 Hardy Bob 3 1 1 $6,000
1 Hardy Bob 3 2 1 $6,000
1 Hardy Bob 3 8 1 $6,000
3 Green Bob 2 6 3 $7,000
3 Green Bob 2 10 3 $9,000
2 Wallace Greg 3 4 2 $4,000
2 Wallace Greg 3 5 2 $6,000
2 Wallace Greg 3 9 2 $7,000
4 Marsh Andy 2 3 4 $8,000
4 Marsh Andy 2 7 4 $4,000结果(2)
trainer count(*) sum(sales_amt) avg(sales_amt)
Bob 5 $34,000 $6,800.0000
Greg 3 $17,000 $5,666.6666
Andy 2 $12,000 S6,000.0000求上面两个结果的语句,,
小弟做了很久也做不出来,总觉得不可能实现,
第二个写出来结果不同,有四条,,
但我买的一本书上就是这么个结果,没SQL语句,,
哪位高手能告诉我SQL语句!!下在是我做出来的第二个结果
Bob 2 $16,000 $8,000.0000
Bob 3 $18,000 $6,000.0000
Greg 3 $17,000 $5,666.6666
Andy 2 $12,000 S6,000.0000
id last_name trainer sales
1 Hardy Bob 3
2 Wallace Greg 3
3 Green Bob 2
4 Marsh Andy 2
5 Brown Greg 0
Sales table:
cust_id sold_by sales_amt
1 1 $6,000
2 1 $6,000
3 4 $8,000
4 2 $4,000
5 2 $6,000
6 3 $7,000
7 4 $4,000
8 1 $6,000
9 2 $7,000
10 3 $9,000要查询的结果是:(1)
emp_id last_name trainer sales cust_id sold_by sales_amt
1 Hardy Bob 3 1 1 $6,000
1 Hardy Bob 3 2 1 $6,000
1 Hardy Bob 3 8 1 $6,000
3 Green Bob 2 6 3 $7,000
3 Green Bob 2 10 3 $9,000
2 Wallace Greg 3 4 2 $4,000
2 Wallace Greg 3 5 2 $6,000
2 Wallace Greg 3 9 2 $7,000
4 Marsh Andy 2 3 4 $8,000
4 Marsh Andy 2 7 4 $4,000结果(2)
trainer count(*) sum(sales_amt) avg(sales_amt)
Bob 5 $34,000 $6,800.0000
Greg 3 $17,000 $5,666.6666
Andy 2 $12,000 S6,000.0000求上面两个结果的语句,,
小弟做了很久也做不出来,总觉得不可能实现,
第二个写出来结果不同,有四条,,
但我买的一本书上就是这么个结果,没SQL语句,,
哪位高手能告诉我SQL语句!!下在是我做出来的第二个结果
Bob 2 $16,000 $8,000.0000
Bob 3 $18,000 $6,000.0000
Greg 3 $17,000 $5,666.6666
Andy 2 $12,000 S6,000.0000
emp_id=sold_by第一步有一个字段打错了
id应为emp_id
--1
select e.emp_id,e.last_name,e.trainer,e.sales,s.cust_id,s.sold_by,s.sales_amt
from
employees e
inner join
sales s
on e.empid=s.sold_by--2select trainer,count(*) as [count(*)],sum(sales_amt) as [sum(sales_amt)],avg(sales_amt) as [avg(sales_amt)]
from
(
select e.trainer,s.sales_amt
from
employees e
inner join
sales s
on e.empid=s.sold_by
)t
group by trainer
--结果2是对结果1的进一步汇总
--建立Employees
Declare @Employees Table(Emp_id int,Last_name varchar(50),Trainer varchar(50),Sales Int)
Insert @Employees Select 1,'Hardy','Bob',3
Union All Select 2,'Wallace','Greg',3
Union All Select 3,'Green','Bob',2
Union All Select 4,'Marsh','Andy',2
Union All Select 5,'Brown','Greg',0
--建立@Sales
Declare @Sales Table(Cust_id int,Sold_by int,Sales_amt DEC(8,4))
Insert @Sales Select 1,1,6000
Union All Select 2,1,6000
Union All Select 3,4,8000
Union All Select 4,2,4000
Union All Select 5,2,6000
Union All Select 6,3,7000
Union All Select 7,4,4000
Union All Select 8,1,6000
Union All Select 9,2,7000
Union All Select 10,3,9000---结果1语句
Select A.*,B.*
From @Employees A Inner Join @Sales B
On A.Emp_id=B.Sold_By
---结果2语句
Select
C.Trainer,
Count(1) as [Count(*)],
SUM(Sales_amt) as [SUM(Sales_amt)],
AVG(Sales_amt) as [AVG(Sales_amt)]
From
(
Select A.Trainer,B.Sales_amt
From @Employees A
Inner Join @Sales B
On A.Emp_id=B.Sold_by
) C
Group By C.Trainer
Order by [SUM(Sales_amt)] Desc
你们说可能不,,
另给分