我的SQL语句
SELECT 1 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, SOTH_XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHID, SOTH_XSYUNIONSELECT 2 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHIDUNIONSELECT 3 AS XH,datepart(year,SOTH_YWRQ) as ND,SOTH_BMID, ''AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMIDUNIONSELECT 4 AS XH,datepart(year,SOTH_YWRQ) as ND,''AS SOTH_BMID, ''AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ)ORDER BY datepart(year,SOTH_YWRQ), SOTH_KHID,SOTH_BMID, XH希望先对XSY(销售员)进行合计 然后是客户(SOTH_KHID) 再是部门 最后是年度分别进行合计 但结果部门和年度的合计出现在最前,
请问应如何写才对 ?谢谢!
SELECT 1 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, SOTH_XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHID, SOTH_XSYUNIONSELECT 2 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHIDUNIONSELECT 3 AS XH,datepart(year,SOTH_YWRQ) as ND,SOTH_BMID, ''AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMIDUNIONSELECT 4 AS XH,datepart(year,SOTH_YWRQ) as ND,''AS SOTH_BMID, ''AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ)ORDER BY datepart(year,SOTH_YWRQ), SOTH_KHID,SOTH_BMID, XH希望先对XSY(销售员)进行合计 然后是客户(SOTH_KHID) 再是部门 最后是年度分别进行合计 但结果部门和年度的合计出现在最前,
请问应如何写才对 ?谢谢!
if exists (select * from dbo.sysobjects where id = object_id('WL') and sysstat & 0xf = 3)
drop table WL
GO
create table WL
(
WL_WLID char(20) not null,
WL_MC char(40) null,
WL_JLDW char(4) null,
WL_ABC char(1) null
)
GO
alter table WL with nocheck add
CONSTRAINT PK_WL PRIMARY KEY CLUSTERED
(WL_WLID) ON [PRIMARY]
GOif exists (select * from dbo.sysobjects where id = object_id('BM') and sysstat & 0xf = 3)
drop table BM
GO
create table BM
(
BM_BMID char(8) not null,
BM_MC char(24) null,
BM_FZR char(30) null,
BM_RS float null,
BM_TYBZ char(1) null,
BM_BZ varchar(250) null
)
GO
alter table BM with nocheck add
CONSTRAINT DF_BM_BM_RS default(0) for BM_RS
GO
alter table BM with nocheck add
CONSTRAINT PK_BM PRIMARY KEY CLUSTERED
(BM_BMID) ON [PRIMARY]
GO
drop table KH
GO
create table KH
(
KH_KHID char(8) not null,
KH_QC varchar(60) null,
KH_JC char(30) null,
KH_XSY char(30) null,
KH_TYBZ char(1) null,
KH_BZ varchar(250) null
)
GO
alter table KH with nocheck add
CONSTRAINT PK_KH PRIMARY KEY CLUSTERED
(KH_KHID) ON [PRIMARY]
GOif exists (select * from dbo.sysobjects where id = object_id('SOTH') and sysstat & 0xf = 3)
drop table SOTH
GO
create table SOTH
(
SOTH_THDID char(10) not null,
SOTH_BMID char(8) null,
SOTH_KHID char(8) null,
SOTH_YWRQ datetime null,
SOTH_XSY char(30) null,
SOTH_ZJE float null,
SOTH_ZY char(40) null
)
GO
alter table SOTH with nocheck add
CONSTRAINT DF_SOTH_SOTH_ZJE default(0) for SOTH_ZJE
GO
alter table SOTH with nocheck add
CONSTRAINT PK_SOTH PRIMARY KEY CLUSTERED
(SOTH_THDID) ON [PRIMARY]
GOif exists (select * from dbo.sysobjects where id = object_id('SOTHMX') and sysstat & 0xf = 3)
drop table SOTHMX
GO
create table SOTHMX
(
SOTHMX_THDID char(10) not null,
SOTHMX_XH float not null,
SOTHMX_WLID char(20) null,
SOTHMX_YFSL float null,
SOTHMX_SFSL float null,
SOTHMX_JG float null,
SOTHMX_JE float null,
SOTHMX_YKPJE float null,
SOTHMX_BZ varchar(250) null
)
GO
alter table SOTHMX with nocheck add
CONSTRAINT DF_SOTHMX_SOTHMX_XH default(0) for SOTHMX_XH,
CONSTRAINT DF_SOTHMX_SOTHMX_YFSL default(0) for SOTHMX_YFSL,
CONSTRAINT DF_SOTHMX_SOTHMX_SFSL default(0) for SOTHMX_SFSL,
CONSTRAINT DF_SOTHMX_SOTHMX_JG default(0) for SOTHMX_JG,
CONSTRAINT DF_SOTHMX_SOTHMX_JE default(0) for SOTHMX_JE,
CONSTRAINT DF_SOTHMX_SOTHMX_YKPJE default(0) for SOTHMX_YKPJE
GO
alter table SOTHMX with nocheck add
CONSTRAINT PK_SOTHMX PRIMARY KEY CLUSTERED
(SOTHMX_THDID, SOTHMX_XH) ON [PRIMARY]
GOif exists (select * from dbo.sysobjects where id = object_id('XSHZ') and sysstat & 0xf = 3)
drop table XSHZ
GO
create table XSHZ
(
XSHZ_YEAR float not null,
XSHZ_BMID char(8) not null,
XSHZ_KHID char(8) not null,
XSHZ_XSY char(30) not null,
XSHZ_XSSL float null,
XSHZ_XSJE float null,
XSHZ_YKPJE float null,
XSHZ_WKPJE float null
)
GO
alter table XSHZ with nocheck add
CONSTRAINT DF_XSHZ_XSHZ_YEAR default(0) for XSHZ_YEAR,
CONSTRAINT DF_XSHZ_XSHZ_XSSL default(0) for XSHZ_XSSL,
CONSTRAINT DF_XSHZ_XSHZ_XSJE default(0) for XSHZ_XSJE,
CONSTRAINT DF_XSHZ_XSHZ_YKPJE default(0) for XSHZ_YKPJE,
CONSTRAINT DF_XSHZ_XSHZ_WKPJE default(0) for XSHZ_WKPJE
GO
alter table XSHZ with nocheck add
CONSTRAINT PK_XSHZ PRIMARY KEY CLUSTERED
(XSHZ_YEAR, XSHZ_BMID, XSHZ_KHID, XSHZ_XSY) ON [PRIMARY]
GOinsert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0100101', 'KS电梯曳引机', '台', 'A')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0100102', 'KS-1PG电梯曳引机', '台', 'A')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0100103', 'KS-2PP电梯曳引机', '台', 'A')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0100104', 'KS-3RG电梯曳引机', '台', 'A')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0100105', 'GS-PP2W电梯曳引机', '台', 'A')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0100106', 'TS-Y20W电梯曳引机', '台', 'A')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0200101', '350W导轮', '台', 'B')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0200102', '750W导轮', '台', 'B')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0200103', '850W导轮', '台', 'B')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0300101', 'DF-37型钢材', '公斤', 'C')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0300102', 'KF-77型钢材', '公斤', 'C')
insert into WL(WL_WLID, WL_MC, WL_JLDW, WL_ABC)
values('0300103', 'BS-68型钢材', '公斤', 'C')
GOinsert into BM(BM_BMID, BM_MC, BM_FZR, BM_RS, BM_TYBZ, BM_BZ)
values('10001', '销售一部', '张星', 20, 'N', null)
insert into BM(BM_BMID, BM_MC, BM_FZR, BM_RS, BM_TYBZ, BM_BZ)
values('10002', '销售二部', '刘力', 32, 'N', null)
insert into BM(BM_BMID, BM_MC, BM_FZR, BM_RS, BM_TYBZ, BM_BZ)
values('10003', '销售三部', '李军', 23, 'N', null)
insert into BM(BM_BMID, BM_MC, BM_FZR, BM_RS, BM_TYBZ, BM_BZ)
values('10004', '销售四部', '肇东杨', 28, 'Y', '已停用')
GOinsert into KH(KH_KHID, KH_QC, KH_JC, KH_XSY, KH_TYBZ, KH_BZ)
values('KH0001', '常熟市电梯曳引机厂', '常熟曳引机', '陈勇', 'N', null)
insert into KH(KH_KHID, KH_QC, KH_JC, KH_XSY, KH_TYBZ, KH_BZ)
values('KH0002', '常熟市梅李镇鼓风机厂', '常熟鼓风机', '王浩', 'N', null)
insert into KH(KH_KHID, KH_QC, KH_JC, KH_XSY, KH_TYBZ, KH_BZ)
values('KH0003', '常熟市蒋巷镇常盛集团', '常熟常盛', '张华', 'N', null)
insert into KH(KH_KHID, KH_QC, KH_JC, KH_XSY, KH_TYBZ, KH_BZ)
values('KH0004', '常熟市纺织机械厂', '常熟纺机', '王玮', 'N', null)
insert into KH(KH_KHID, KH_QC, KH_JC, KH_XSY, KH_TYBZ, KH_BZ)
values('KH0005', '常熟市通润集团开关厂', '常熟开关厂', '江永生', 'Y', '已停用')
GOinsert into SOTH(SOTH_THDID, SOTH_BMID, SOTH_KHID, SOTH_YWRQ, SOTH_XSY, SOTH_ZJE, SOTH_ZY)
values('THD0000001', '10001', 'KH0001', '2006-02-04', '陈勇', 0, null)
insert into SOTHMX(SOTHMX_THDID, SOTHMX_XH, SOTHMX_WLID, SOTHMX_YFSL, SOTHMX_SFSL, SOTHMX_JG, SOTHMX_JE, SOTHMX_YKPJE, SOTHMX_BZ)
values('THD0000001', 1, '0100101', 5, 3, 1200, 3600, 3000, '导轮直径100cm')
insert into SOTHMX(SOTHMX_THDID, SOTHMX_XH, SOTHMX_WLID, SOTHMX_YFSL, SOTHMX_SFSL, SOTHMX_JG, SOTHMX_JE, SOTHMX_YKPJE, SOTHMX_BZ)
values('THD0000001', 2, '0100102', 10, 10, 1500, 15000, 12000, '导轮直径110cm')
GOinsert into SOTH(SOTH_THDID, SOTH_BMID, SOTH_KHID, SOTH_YWRQ, SOTH_XSY, SOTH_ZJE, SOTH_ZY)
values('THD0000002', '10001', 'KH0001', '2006-01-24', '王华', 0, null)
insert into SOTHMX(SOTHMX_THDID, SOTHMX_XH, SOTHMX_WLID, SOTHMX_YFSL, SOTHMX_SFSL, SOTHMX_JG, SOTHMX_JE, SOTHMX_YKPJE, SOTHMX_BZ)
values('THD0000002', 1, '0100101', 4, 3, 1200, 3600, 3000, '')
insert into SOTHMX(SOTHMX_THDID, SOTHMX_XH, SOTHMX_WLID, SOTHMX_YFSL, SOTHMX_SFSL, SOTHMX_JG, SOTHMX_JE, SOTHMX_YKPJE, SOTHMX_BZ)
values('THD0000002', 2, '0100102', 10, 8, 1500, 12000, 11000, null)
GOinsert into SOTH(SOTH_THDID, SOTH_BMID, SOTH_KHID, SOTH_YWRQ, SOTH_XSY, SOTH_ZJE, SOTH_ZY)
values('THD0000003', '10002', 'KH0002', '2006-03-24', '王浩', 20800, null)
insert into SOTHMX(SOTHMX_THDID, SOTHMX_XH, SOTHMX_WLID, SOTHMX_YFSL, SOTHMX_SFSL, SOTHMX_JG, SOTHMX_JE, SOTHMX_YKPJE, SOTHMX_BZ)
values('THD0000003', 1, '0100103', 12, 8, 1200, 9600, 9500, null)
insert into SOTHMX(SOTHMX_THDID, SOTHMX_XH, SOTHMX_WLID, SOTHMX_YFSL, SOTHMX_SFSL, SOTHMX_JG, SOTHMX_JE, SOTHMX_YKPJE, SOTHMX_BZ)
values('THD0000003', 2, '0100104', 7, 7, 1600, 11200, 11200, '导轮直径110cm')
GOinsert into SOTH(SOTH_THDID, SOTH_BMID, SOTH_KHID, SOTH_YWRQ, SOTH_XSY, SOTH_ZJE, SOTH_ZY)
values('THD0000004', '10002', 'KH0005', '2006-04-24', '李巍', 23200, null)
insert into SOTHMX(SOTHMX_THDID, SOTHMX_XH, SOTHMX_WLID, SOTHMX_YFSL, SOTHMX_SFSL, SOTHMX_JG, SOTHMX_JE, SOTHMX_YKPJE, SOTHMX_BZ)
values('THD0000004', 1, '0100103', 12, 10, 1200, 12000, 10000, '导轮直径96cm')
insert into SOTHMX(SOTHMX_THDID, SOTHMX_XH, SOTHMX_WLID, SOTHMX_YFSL, SOTHMX_SFSL, SOTHMX_JG, SOTHMX_JE, SOTHMX_YKPJE, SOTHMX_BZ)
values('THD0000004', 2, '0100104', 7, 7, 1600, 11200, 11200, '导轮直径110cm')
GO
请帮忙写下,谢谢了.
3 2006 10001 24 34200 29000 5200
3 2006 10002 32 44000 41900 2100
1 2006 10001 KH0001 陈勇 13 18600 15000 3600
1 2006 10001 KH0001 王华 11 15600 14000 1600
2 2006 10001 KH0001 24 34200 29000 5200
1 2006 10002 KH0002 王浩 15 20800 20700 100
2 2006 10002 KH0002 15 20800 20700 100
1 2006 10002 KH0005 李巍 17 23200 21200 2000
2 2006 10002 KH0005 17 23200 21200 2000上面是现在的结果
下面是想要的结果
谢谢了1 2006 10001 KH0001 陈勇 13 18600 15000 3600
1 2006 10001 KH0001 王华 11 15600 14000 1600
2 2006 10001 KH0001 24 34200 29000 5200
3 2006 10001 24 34200 29000 5200
1 2006 10002 KH0002 王浩 15 20800 20700 100
2 2006 10002 KH0002 15 20800 20700 100
1 2006 10002 KH0005 李巍 17 23200 21200 2000
2 2006 10002 KH0005 17 23200 21200 2000
3 2006 10002 32 44000 41900 2100
4 2006 56 78200 70900 7300
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE) JE, SUM(SOTHMX_YKPJE) YKPJE, SUM(SOTHMX_JE-SOTHMX_YKPJE) SOTHMX_YKPJE
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHID, SOTH_XSYUNIONSELECT 2 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHIDUNIONSELECT 3 AS XH,datepart(year,SOTH_YWRQ) as ND,SOTH_BMID, 'KH空'AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMIDUNIONSELECT 4 AS XH,datepart(year,SOTH_YWRQ) as ND, '空' AS SOTH_BMID, 'KH空'AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL)<>0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ) ORDER BY ND,SOTH_BMID,SOTH_KHID1 2006 10001 KH0001 陈勇 13.0 18600.0 15000.0 3600.0
1 2006 10001 KH0001 王华 11.0 15600.0 14000.0 1600.0
2 2006 10001 KH0001 24.0 34200.0 29000.0 5200.0
3 2006 10001 KH空 24.0 34200.0 29000.0 5200.0
1 2006 10002 KH0002 王浩 15.0 20800.0 20700.0 100.0
2 2006 10002 KH0002 15.0 20800.0 20700.0 100.0
1 2006 10002 KH0005 李巍 17.0 23200.0 21200.0 2000.0
2 2006 10002 KH0005 17.0 23200.0 21200.0 2000.0
3 2006 10002 KH空 32.0 44000.0 41900.0 2100.0
4 2006 空 KH空 56.0 78200.0 70900.0 7300.0
你看最后的几个字段,不是
ORDER BY ND,SOTH_BMID,SOTH_KHID
吗?
那XH为何不在order by 之中呢?