列出如下sql语句
select convert(varchar(10),min(rkrq),120) as rkrqs,convert(varchar(10),max(rkrq),120) as rkrqe,pjmc,ggxh,ghs,jjdj,sum(rksl-isnull(thsl,0)) as sjrksl,sum(jjje-isnull(thjjje,0)) as sjjjje,max(pjbm) as pjbm from part_in
where ghs<>'' and rkrq>='2007-12-26' and rkrq<='2008-11-25'
and rkbz=1
group by pjmc,ggxh,jjdj,ghs
union
select '','小计',pjmc,'','',0,sum(rksl-isnull(thsl,0)) as sjrksl,sum(jjje-isnull(thjjje,0)) as sjjjje,'' from part_in
where ghs<>'' and rkrq>='2007-12-26' and rkrq<='2008-11-25'
and rkbz=1 group by pjmc
union
select '合计','','','','',0,0,sum(jjje-isnull(thjjje,0)) as sjjjje,'' from part_in
where ghs<>'' and rkrq>='2007-12-26' and rkrq<='2008-11-25'
and rkbz=1
order by pjmc,ggxh,ghs,jjdj出现了如下的问题:小计数据在每项数据的上部,试了一下,原来是按pjmc,ggxh排序的,ggxh为空,没办法在小计的规格型号里加了一个内容才可以。
另外我想只取小计里sjjjje数据大于100的应该怎么写呢?请教各位。
select convert(varchar(10),min(rkrq),120) as rkrqs,convert(varchar(10),max(rkrq),120) as rkrqe,pjmc,ggxh,ghs,jjdj,sum(rksl-isnull(thsl,0)) as sjrksl,sum(jjje-isnull(thjjje,0)) as sjjjje,max(pjbm) as pjbm from part_in
where ghs<>'' and rkrq>='2007-12-26' and rkrq<='2008-11-25'
and rkbz=1
group by pjmc,ggxh,jjdj,ghs
union
select '','小计',pjmc,'','',0,sum(rksl-isnull(thsl,0)) as sjrksl,sum(jjje-isnull(thjjje,0)) as sjjjje,'' from part_in
where ghs<>'' and rkrq>='2007-12-26' and rkrq<='2008-11-25'
and rkbz=1 group by pjmc
union
select '合计','','','','',0,0,sum(jjje-isnull(thjjje,0)) as sjjjje,'' from part_in
where ghs<>'' and rkrq>='2007-12-26' and rkrq<='2008-11-25'
and rkbz=1
order by pjmc,ggxh,ghs,jjdj出现了如下的问题:小计数据在每项数据的上部,试了一下,原来是按pjmc,ggxh排序的,ggxh为空,没办法在小计的规格型号里加了一个内容才可以。
另外我想只取小计里sjjjje数据大于100的应该怎么写呢?请教各位。
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25'
and rkbz=1 group by pjmc having sum(jjje-isnull(thjjje,0)) >100看看行不行?
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25'
and rkbz=1 group by pjmc having sum(jjje-isnull(thjjje,0)) >100
看行不行
如下表:
rkrq pjmc ggxh 供应商 rksl jjdj jjje thsl thjjje
---------- ------------ ---------- ------------- --------- ------ -------- ------- ----------
2008-12-10 油箱 修复 海青东源农 1.00 50.00 50.00 .00 .00
2008-12-10 水箱 (修复) 海青东源农 1.00 50.00 50.00 .00 .00
2008-12-11 轮胎 (翻新) 剀柏轮胎有限公司 6.00 380.00 2280.00 .00 .00
2008-12-11 草帽垫 祥盛汽车配 100.00 .50 50.00 .00 .00
2008-12-12 连杆瓦 (0.00)铜 祥盛汽车配 1.00 75.00 75.00 .00 .00
2008-12-12 活塞 滨州 吉成汽车配件有限 12.00 35.00 420.00 .00 .00
2008-12-13 飞轮壳 Z1A2-38-FL 一汽贸易有限公司 1.00 580.00 580.00 .00 .00
2008-12-14 曲轴皮带轮 汽贸易有限公司 1.00 440.00 440.00 .00 .00
2008-12-14 起动机 神电 鑫辉煌汽 1.00 620.00 620.00 .00 .00
2008-12-15 起动机衬套 前 鑫辉煌汽 10.00 1.00 10.00 .00 .00
2008-12-15 起动机衬套 中、大 鑫辉煌汽 8.00 1.50 12.00 .00 .00
2008-12-15 起动机衬套 后、小 鑫辉煌汽 10.00 1.00 10.00 .00 .00
2008-12-15 缸套 统筹后 一汽贸易有限公司 18.00 88.00 1584.00 .00 .00
2008-12-15 连杆瓦 原厂/铜铅 一汽贸易有限公司 10.00 72.00 720.00 .00 .00
2008-12-15 气门导管 一汽贸易有限公司 60.00 3.70 222.00 .00 .00
2008-12-15 气门油封 一汽贸易有限公司 96.00 2.80 268.80 .00 .00
2008-12-15 气门挺柱 一汽贸易有限公司 84.00 9.20 772.80 .00 .00
2008-12-15 排气门座圈 一汽贸易有限公司 48.00 3.70 177.60 .00 .00
2008-12-15 进气门座圈 一汽贸易有限公司 48.00 3.70 177.60 .00 .00
2008-12-15 主轴瓦 铜铅(锡611 一汽贸易有限公司 10.00 47.00 470.00 .00 .00(所影响的行数为 20 行)
现在用了我上面的语句以后,小计时的一栏不在每项的后面。
select '','小计',pjmc,'','',0,sum(rksl-isnull(thsl,0)) as sjrksl,sum(jjje-isnull(thjjje,0)) as sjjjje,'' from part_in
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25'
and rkbz=1 group by pjmc having sum(jjje-isnull(thjjje,0)) >100
这个语句做到把小计的数据条件列出来了,可以我想与上面分组的数据对应出来,是不是我说得不是很明白?
标题:查询指定节点及其所有子节点的函数
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-05-12
地点:广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id , @level
from tb a , @t_Level b
where a.pid = b.id and b.level = @level - 1
end
return
end
go--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇(所影响的行数为 10 行)
*/--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
002 001 广州市
004 002 天河区(所影响的行数为 2 行)
*/--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
003 001 深圳市
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇(所影响的行数为 7 行)
*/drop table tb
drop function f_cid
2008-12-10 油箱 修复 海青东源农 1.00 50.00 50.00 .00 .00
小计 50.00 50.00 .00 .00
2008-12-10 水箱 (修复) 海青东源农 1.00 50.00 50.00 .00 .00
小计
2008-12-11 轮胎 (翻新) 剀柏轮胎有限公司 6.00 380.00 2280.00 .00 .00
小计
2008-12-11 草帽垫 祥盛汽车配 100.00 .50 50.00 .00 .00
小计
2008-12-12 连杆瓦 (0.00)铜 祥盛汽车配 1.00 75.00 75.00 .00 .00
小计
2008-12-12 活塞 滨州 吉成汽车配件有限 12.00 35.00 420.00 .00 .00
小计
2008-12-13 飞轮壳 Z1A2-38-FL 一汽贸易有限公司 1.00 580.00 580.00 .00 .00
小计
2008-12-14 曲轴皮带轮 汽贸易有限公司 1.00 440.00 440.00 .00 .00
小计
2008-12-14 起动机 神电 鑫辉煌汽 1.00 620.00 620.00 .00 .00
小计
2008-12-15 起动机衬套 前 鑫辉煌汽 10.00 1.00 10.00 .00 .00
小计
2008-12-15 起动机衬套 中、大 鑫辉煌汽 8.00 1.50 12.00 .00 .00
小计
2008-12-15 起动机衬套 后、小 鑫辉煌汽 10.00 1.00 10.00 .00 .00
小计
2008-12-15 缸套 统筹后 一汽贸易有限公司 18.00 88.00 1584.00 .00 .00
小计
2008-12-15 连杆瓦 原厂/铜铅 一汽贸易有限公司 10.00 72.00 720.00 .00 .00
小计
2008-12-15 气门导管 一汽贸易有限公司 60.00 3.70 222.00 .00 .00
小计
2008-12-15 气门油封 一汽贸易有限公司 96.00 2.80 268.80 .00 .00
小计
2008-12-15 气门挺柱 一汽贸易有限公司 84.00 9.20 772.80 .00 .00
小计
2008-12-15 排气门座圈 一汽贸易有限公司 48.00 3.70 177.60 .00 .00
小计
2008-12-15 进气门座圈 一汽贸易有限公司 48.00 3.70 177.60 .00 .00
小计
2008-12-15 主轴瓦 铜铅(锡611 一汽贸易有限公司 10.00 47.00 470.00 .00 .00
小计
合计
当然,这些数据没有重复,因为数据量太大,我没法全发送上来,所以我写了上面的语句,可是出了一个问题,小计出现在明细的上面。
而且我想如果小计数小于一定的数值,就不列出来了,应该怎么做呢?
--2.如果小計部分不列出,總計也是?可在where filter掉
select rkrqs,rkrqe,pjmc,ggxh,ghs,jjdj,sjrksl,sjjjje,pjbm
from (
select convert(varchar(10),min(rkrq),120) as rkrqs,convert(varchar(10),max(rkrq),120) as rkrqe,pjmc,ggxh,ghs,jjdj,sum(rksl-isnull(thsl,0)) as sjrksl,sum(jjje-isnull(thjjje,0)) as sjjjje,max(pjbm) as pjbm, pjmc+ggxh as groupname
from part_in
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25' and rkbz=1
group by pjmc,ggxh,jjdj,ghs
union
select '','小计',pjmc,'','',0,sum(rksl-isnull(thsl,0)) as sjrksl,sum(jjje-isnull(thjjje,0)) as sjjjje,'', pjmc+'zzzzzzzzaaaaaaaa' as groupname
from part_in
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25' and rkbz=1 group by pjmc
union
select '合计','','','','',0,0,sum(jjje-isnull(thjjje,0)) as sjjjje,'',pjmc+'zzzzzzzzbbbbbbbb' as groupname
from part_in
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25' and rkbz=1
)
order by groupname,pjmc,ggxh,ghs,jjdj
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25' and rkbz=1
group by pjmc,ggxh,jjdj,ghs
union
select '','小计',pjmc,'','',0,sum(rksl-isnull(thsl,0)) as sjrksl,sum(jjje-isnull(thjjje,0)) as sjjjje,''
from part_in
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25' and rkbz=1 group by pjmc
group by pjmc
having sum(jjje-isnull(thjjje,0)) >100
union
select '合计','','','','',0,0,sum(jjje-isnull(thjjje,0)) as sjjjje,''
from part_in
where ghs <>'' and rkrq>='2007-12-26' and rkrq <='2008-11-25' and rkbz=1
order by pjmc,isnull(ggxh,0) desc,isnull(ghs,0),isnull(jjdj,0)
服务器: 消息 104,级别 15,状态 1,行 1
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Part_In]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Part_In]
GOCREATE TABLE [dbo].[Part_In] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[rkrq] [datetime] NOT NULL ,
[ghs] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[pjbm] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[cx] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[pjmc] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[ggxh] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[jldw] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[rksl] [numeric](12, 2) NOT NULL ,
[jjje] [numeric](12, 2) NOT NULL ,
[jjdj] [numeric](13, 5) NOT NULL ,
[thsl] [numeric](10, 2) NULL ,
[thcbje] [numeric](10, 2) NULL ,
[thjjje] [numeric](10, 2) NULL ,
[rkbz] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO我的想法就是想将数据按pjmc,ggxh,jjdj,ghs排序,同时每个分组有小计数据,最后是合计数据,且小计数据小于一定数值的数据不列出来,包括分组的明细。这样说是不是可以