34,575
社区成员
发帖
与我相关
我的任务
分享
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
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
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 行)
*/
---测试数据---
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 行)
**/
----------------------------------------------------------------
-- 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 行受影响)
*/
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
-->==============================================
-->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 行受影响
*/
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
/*---------------------------------
-- 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 行受影响)
*/
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