商品表 sp
spid spname dw dj
1001 青啤 瓶 5.00
1002 燕京 瓶 4.00
客户表 kh
khid khname
101 赵
102 钱 业务表 yw
autoid fpid khid spid dj sl je
1 001 101 1001 5.00 2 10.00
2 001 101 1002 4.00 3 12.00
3 002 102 1001 5.00 2 10.00
4 002 102 1002 4.00 3 12.00
5 001 101 1001 5.00 3 15.00有上面三个表,取得下面分组求和结果,怎样写sql语句效率最好 ?fpid khid spid dj sl je
001 赵 青啤 5.00 5 25.00
001 赵 燕京 4.00 3 12.00
002 钱 青啤 5.00 2 10.00
002 钱 燕京 4.00 3 12.00
spid spname dw dj
1001 青啤 瓶 5.00
1002 燕京 瓶 4.00
客户表 kh
khid khname
101 赵
102 钱 业务表 yw
autoid fpid khid spid dj sl je
1 001 101 1001 5.00 2 10.00
2 001 101 1002 4.00 3 12.00
3 002 102 1001 5.00 2 10.00
4 002 102 1002 4.00 3 12.00
5 001 101 1001 5.00 3 15.00有上面三个表,取得下面分组求和结果,怎样写sql语句效率最好 ?fpid khid spid dj sl je
001 赵 青啤 5.00 5 25.00
001 赵 燕京 4.00 3 12.00
002 钱 青啤 5.00 2 10.00
002 钱 燕京 4.00 3 12.00
yw.fpid,
kh.khnae,
sp.spname,
yw.dj,
sum(sl) as sl,
sum(je) as je
from
yw
inner join kh on yw.khid=kh.khid
inner join sp on yw.spid=sp.spid
group by
yw.fpid,
kh.khnae,
sp.spname,
yw.dj
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-06 23:25:04
-- Version: Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86)
Dec 17 2008 15:19:45
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 6.0 (Build 6001: Service Pack 1)---------------------------------*/
--> 生成测试数据表:ywIf not object_id('[yw]') is null
Drop table [yw]
Go
Create table [yw]([autoid] int,[fpid] varchar(3),[khid] int,[spid] int,[dj] decimal(18,2),[sl] int,[je] decimal(18,2))
Insert [yw]
Select 1,'001',101,1001,5.00,2,10.00 union all
Select 2,'001',101,1002,4.00,3,12.00 union all
Select 3,'002',102,1001,5.00,2,10.00 union all
Select 4,'002',102,1002,4.00,3,12.00 union all
Select 5,'001',101,1001,5.00,3,15.00
Go
--Select * from [yw]--> 生成测试数据表:spIf not object_id('[sp]') is null
Drop table [sp]
Go
Create table [sp]([spid] int,[spname] nvarchar(2),[dw] nvarchar(1),[dj] decimal(18,2))
Insert [sp]
Select 1001,N'青啤',N'瓶',5.00 union all
Select 1002,N'燕京',N'瓶',4.00
Go
--Select * from [sp]--> 生成测试数据表:khIf not object_id('[kh]') is null
Drop table [kh]
Go
Create table [kh]([khid] int,[khname] nvarchar(1))
Insert [kh]
Select 101,N'赵' union all
Select 102,N'钱'
Go
--Select * from [kh]-->SQL查询如下:
select a.[fpid],[khid]=c.[khname],spid=b.spname,max(a.dj) dj,sum(a.sl) sl,sum(a.je) je
from [yw] a
join sp b on a.spid=b.spid
join kh c on a.khid=c.khid
group by a.[fpid],c.[khname],b.spname
/*
fpid khid spid dj sl je
---- ---- ---- --------------------------------------- ----------- ---------------------------------------
001 赵 青啤 5.00 5 25.00
001 赵 燕京 4.00 3 12.00
002 钱 青啤 5.00 2 10.00
002 钱 燕京 4.00 3 12.00(4 行受影响)*/
from yw c , kh b , sp a
where c.khid = b.khid and c.spid = a.spid
group by c.fpid , b.khid , a.spid , a.dj
-->Title:生成测试数据
-->Author:wufeng4552【水族杰纶】
-->Environment: MSSQL2005
-->Date:2009-09-06
-->==============================================
if not object_id('sp') is null
drop table sp
Go
Create table sp([spid] int,[spname] nvarchar(2),[dw] nvarchar(1),[dj] decimal(18,2))
Insert sp
select 1001,N'青啤',N'瓶',5.00 union all
select 1002,N'燕京',N'瓶',4.00
Go
if not object_id('kh') is null
drop table kh
Go
Create table kh([khid] int,[khname] nvarchar(1))
Insert kh
select 101,N'赵' union all
select 102,N'钱'
Go
if not object_id('yw') is null
drop table yw
Go
Create table yw([autoid] int,[fpid] nvarchar(3),
[khid] int,[spid] int,[dj] decimal(18,2),[sl] int,[je] decimal(18,2))
Insert yw
select 1,N'001',101,1001,5.00,2,10.00 union all
select 2,N'001',101,1002,4.00,3,12.00 union all
select 3,N'002',102,1001,5.00,2,10.00 union all
select 4,N'002',102,1002,4.00,3,12.00 union all
select 5,N'001',101,1001,5.00,3,15.00
Go
select [fpid],
khid=khname,
spid=spname,
dj=max(yw.dj),
sl=sum(yw.sl),
je=sum(yw.je)
from yw ,kh,sp
where yw.[khid]=kh.[khid]
and yw.[spid]=sp.[spid]
group by [fpid],
khname,
spname
/*
fpid khid spid dj sl je
---- ---- ---- --------------------------------------- ----------- ---------------------------------------
001 赵 青啤 5.00 5 25.00
001 赵 燕京 4.00 3 12.00
002 钱 青啤 5.00 2 10.00
002 钱 燕京 4.00 3 12.00(4 行受影响
*/
from yw c , kh b , sp a
where c.khid = b.khid and c.spid = a.spid
group by c.fpid , b.khname , a.spname , a.dj
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-06 23:25:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[ sp]
if object_id('[sp]') is not null drop table [sp]
go
create table [sp]([spid] int,[spname] varchar(4),[dw] varchar(2),[dj] numeric(3,2))
insert [sp]
select 1001,'青啤','瓶',5.00 union all
select 1002,'燕京','瓶',4.00
--> 测试数据:[kh]
if object_id('[kh]') is not null drop table [kh]
go
create table [kh]([khid] int,[khname] varchar(2))
insert [kh]
select 101,'赵' union all
select 102,'钱'
--> 测试数据:[yw]
if object_id('[yw]') is not null drop table [yw]
go
create table [yw]([autoid] int,[fpid] varchar(3),[khid] int,[spid] int,[dj] numeric(3,2),[sl] int,[je] numeric(4,2))
insert [yw]
select 1,'001',101,1001,5.00,2,10.00 union all
select 2,'001',101,1002,4.00,3,12.00 union all
select 3,'002',102,1001,5.00,2,10.00 union all
select 4,'002',102,1002,4.00,3,12.00 union all
select 5,'001',101,1001,5.00,3,15.00
--------------开始查询--------------------------
select
c.fpid,a.khname,b.spname,c.dj,sum(sl) as sl,sum(je) as je
from
yw c
inner join
kh a
on
c.khid=a.khid
inner join
sp b
on
c.spid=b.spid
group by
c.fpid,a.khname,b.spname,c.dj
----------------结果----------------------------
/* fpid khname spname dj sl je
---- ------ ------ --------------------------------------- ----------- ---------------------------------------
001 赵 青啤 5.00 5 25.00
001 赵 燕京 4.00 3 12.00
002 钱 青啤 5.00 2 10.00
002 钱 燕京 4.00 3 12.00(4 行受影响)
*/
if object_id('[sp1]') is not null drop table [sp1]
go
create table [sp1]([spid] int,[spname] varchar(4),[dw] varchar(2),[dj] numeric(3,2))
insert [sp1]
select 1001,'青啤','瓶',5.00 union all
select 1002,'燕京','瓶',4.00
if object_id('[kh]') is not null drop table [kh]
go
create table [kh]([khid] int,[khname] varchar(2))
insert [kh]
select 101,'赵' union all
select 102,'钱'
if object_id('[yw]') is not null drop table [yw]
go
create table [yw]([autoid] int,[fpid] varchar(3),[khid] int,[spid] int,[dj] numeric(3,2),[sl] int,[je] numeric(4,2))
insert [yw]
select 1,'001',101,1001,5.00,2,10.00 union all
select 2,'001',101,1002,4.00,3,12.00 union all
select 3,'002',102,1001,5.00,2,10.00 union all
select 4,'002',102,1002,4.00,3,12.00 union all
select 5,'001',101,1001,5.00,3,15.00select
yw.fpid,
kh.khname,
sp1.spname,
yw.dj,
sum(yw.sl) as sl,
sum(yw.je) as je
from
yw
inner join kh on yw.khid=kh.khid
inner join sp1 on yw.spid=sp1.spid
group by
yw.fpid,
kh.khname,
sp1.spname,
yw.dj/**
fpid khname spname dj sl je
---- ------ ------ ----- ----------- ----------------------------------------
001 赵 青啤 5.00 5 25.00
001 赵 燕京 4.00 3 12.00
002 钱 青啤 5.00 2 10.00
002 钱 燕京 4.00 3 12.00(所影响的行数为 4 行)
**/
drop table sp
Go
Create table sp([spid] int,[spname] nvarchar(2),[dw] nvarchar(1),[dj] decimal(18,2))
Insert sp
select 1001,N'青啤',N'瓶',5.00 union all
select 1002,N'燕京',N'瓶',4.00
Go
if not object_id('kh') is null
drop table kh
Go
Create table kh([khid] int,[khname] nvarchar(1))
Insert kh
select 101,N'赵' union all
select 102,N'钱'
Go
if not object_id('yw') is null
drop table yw
Go
Create table yw([autoid] int,[fpid] nvarchar(3),
[khid] int,[spid] int,[dj] decimal(18,2),[sl] int,[je] decimal(18,2))
Insert yw
select 1,N'001',101,1001,5.00,2,10.00 union all
select 2,N'001',101,1002,4.00,3,12.00 union all
select 3,N'002',102,1001,5.00,2,10.00 union all
select 4,N'002',102,1002,4.00,3,12.00 union all
select 5,N'001',101,1001,5.00,3,15.00
Goselect c.fpid , b.khname , a.spname , a.dj , sum(c.sl) sl , sum(a.dj*.c.sl) je
from yw c , kh b , sp a
where c.khid = b.khid and c.spid = a.spid
group by c.fpid , b.khname , a.spname , a.djdrop table yw, kh , sp/*
fpid khname spname dj sl je
---- ------ ------ -------------------- ----------- ----------------------------------------
001 赵 青啤 5.00 5 25.00
001 赵 燕京 4.00 3 12.00
002 钱 青啤 5.00 2 10.00
002 钱 燕京 4.00 3 12.00(所影响的行数为 4 行)
*/
大概看了一下,楼上大侠用的都是一种语法
都是连接,下面这2种是一样的
1、select [fpid],
khid=khname,
spid=spname,
dj=max(yw.dj),
sl=sum(yw.sl),
je=sum(yw.je)
from yw ,kh,sp
where yw.[khid]=kh.[khid]
and yw.[spid]=sp.[spid]
group by [fpid],
khname,
spname2、select
yw.fpid,
kh.khname,
sp1.spname,
yw.dj,
sum(yw.sl) as sl,
sum(yw.je) as je
from
yw
inner join kh on yw.khid=kh.khid
inner join sp1 on yw.spid=sp1.spid
group by
yw.fpid,
kh.khname,
sp1.spname,
yw.dj
FROM (SELECT fpid,khid,spid,dj,SUM(sl)AS sl,SUM(je)AS je
FROM dbo.yw
GROUP BY fpid,khid,spid,dj)AS yw
INNER JOIN dbo.sp ON yw.spid = sp.spid
INNER JOIN dbo.kh ON yw.khid = kh.khid
from [yw] a
join sp b on a.spid=b.spid
join kh c on a.khid=c.khid
group by a.[fpid],c.[khname],b.spname