34,575
社区成员
发帖
与我相关
我的任务
分享求 sql 语句
商品表 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
...全文
请发表友善的回复…
发表回复
华夏小卒 2009-09-07
- 打赏
- 举报
[Quote=引用 9 楼 wfliu 的回复:]
非常感谢各位 ！能否评价一下以上各种方式的优劣 ？ 谢谢 ！
[/Quote]
大概看了一下，楼上大侠用的都是一种语法
都是连接，下面这2种是一样的
非常感谢各位 ！能否评价一下以上各种方式的优劣 ？ 谢谢 ！
[/Quote]
大概看了一下，楼上大侠用的都是一种语法
都是连接，下面这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,
spname
2、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
无锡阔微信息科技 2009-09-07
- 打赏
- 举报
[Quote=引用 2 楼 htl258 的回复:]
SQL code/*---------------------------------
-- 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
¡
[/Quote]
同意
SQL code/*---------------------------------
-- 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
¡
[/Quote]
同意
ChinaITOldMan 2009-09-07
- 打赏
- 举报
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
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
xiaoliaoyun 2009-09-07
- 打赏
- 举报
SELECT yw.fpid,khname AS khid,sp.spname AS spid,yw.dj,yw.sl,yw.je
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
zpzpyesterday 2009-09-06
- 打赏
- 举报
各个查询语句的优劣可以参照查询计划。
wfliu 2009-09-06
- 打赏
- 举报
非常感谢各位 ！能否评价一下以上各种方式的优劣 ？ 谢谢 ！
dawugui 2009-09-06
- 打赏
- 举报
--用的htl258的数据
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 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.dj
drop 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 行）
*/百年树人 2009-09-06
- 打赏
- 举报
---测试数据---
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.00
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
/**
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 行）
**/
--小F-- 2009-09-06
- 打赏
- 举报
----------------------------------------------------------------
-- 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 行受影响)
*/
dawugui 2009-09-06
- 打赏
- 举报
select 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.dj
水族杰纶 2009-09-06
- 打赏
- 举报
-->==============================================
-->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 行受影响
*/dawugui 2009-09-06
- 打赏
- 举报
select c.fpid , b.khid , a.spid , 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.khid , a.spid , a.dj
htl258_Tony 2009-09-06
- 打赏
- 举报
/*---------------------------------
-- 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)
---------------------------------*/
--> 生成测试数据表:yw
If 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]
--> 生成测试数据表:sp
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
--Select * from [sp]
--> 生成测试数据表:kh
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
--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 行受影响)
*/百年树人 2009-09-06
- 打赏
- 举报
select
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
