请教一个SQL语句的写法

[plutu]

现有一个入库计划表master和实际入库明细表details，一个计划表可以对应多次入库，现要形成一个计划入库量对应实际入库量的统计表，示例数据如下：
create table master(ID int,计划号 varchar(5),计划数量 int)
insert into master values(1,'00001',10)
insert into master values(2,'00002',20)
insert into master values(3,'00003',30)
insert into master values(4,'00004',30)

create table detail(ID int,计划号 varchar(5),入库数量 int,操作员 char(6))
insert into detail values(1,'00001',4,'张三')
insert into detail values(2,'00001',6,'张三')
insert into detail values(3,'00002',10,'张三')
insert into detail values(4,'00002',10,'李四')
insert into detail values(5,'00003',30,'李四')
insert into detail values(6,'00004',10,'张三')
insert into detail values(7,'00004',10,'李四')
要形成的统计结果为：
计划号 计划数量 入库数量 操作员
0001 10 4 张三
6 张三
小计 10 10 ---
0002 20 10 张三
10 李四
小计 20 20 ---
0003 30 30 张三
小计 30 30 ---
0004 30 10 张三
10 李四
小计 30 20 ---
总计 90 80 ---
请问这个SQL语句怎么实现？在SQL Server2000环境

[--小F--]

GROUPING函数.GROUP BY WITH ROLLUP的运用。

[--小F--]

另外你的表名命名为MASTER的话 换过一个比较讲究规范的领导 你要被骂死

[LongRui888]

呵呵，写的有点复杂：

if OBJECT_ID('tempdb..#temp') is not null
   drop table #temp
   
select m.计划号,
       m.计划数量,
       d.入库数量,
       d.操作员,
       d.id
       into #temp       
from [master] m
left join [detail] d
       on m.计划号 = d.计划号



select case when (select COUNT(*) from #temp t2
                  where t2.计划号 = t1.计划号
                        and t2.id <= t1.id) = 1
                 then t1.计划号
            else null
       end as 计划号,
       
       case when (select COUNT(*) from #temp t2
                  where t2.计划号 = t1.计划号
                        and t2.id <= t1.id) = 1
                 then t1.计划数量
            else null
       end as 计划数量,
       
       入库数量,
       操作员
from #temp t1

union all

select '小计',sum(计划数量) ,(select sum(入库数量) 
                             from [detail] d where m.计划号 = d.计划号),'-'
from [master] m
group by 计划号

union all

select '总计',sum(计划数量),(select sum(入库数量) from [detail] d ),'-'
from [master] m
/*

计划号	计划数量	入库数量	操作员
00001	10	    4	    张三
NULL	NULL	6	    张三
00002	20	    10	    张三
NULL	NULL	10	    李四
00003	30	    30	    李四
00004	30	    10	    张三
NULL	NULL	10	    李四
小计	    10	    10	    -
小计	    20	    20	    -
小计	    30	    30	    -
小计	    30	    20	    -
总计	    90	    80	    -
*/

[plutu]

谢谢，但感觉有点小复杂，并且结果排序不大对，我感觉应该有更简洁一点的办法，请大家继续

[oreoconansisu]

引用 7 楼 zywcy100 的回复:

[quote=引用 1 楼 fredrickhu 的回复:] GROUPING函数.GROUP BY WITH ROLLUP的运用。

顶一个[/quote] 再顶一个

[發糞塗牆]

--create table [master](ID int,计划号 varchar(5),计划数量 int)
--insert into master values(1,'00001',10)
--insert into master values(2,'00002',20)
--insert into master values(3,'00003',30)
--insert into master values(4,'00004',30)

--create table detail(ID int,计划号 varchar(5),入库数量 int,操作员 char(6))
--insert into detail values(1,'00001',4,'张三')
--insert into detail values(2,'00001',6,'张三')
--insert into detail values(3,'00002',10,'张三')
--insert into detail values(4,'00002',10,'李四')
--insert into detail values(5,'00003',30,'李四')
--insert into detail values(6,'00004',10,'张三')
--insert into detail values(7,'00004',10,'李四')



--SELECT m.ID,m.计划号,m.计划数量,d.入库数量,d.操作员 INTO #t 
--FROM [master] m LEFT JOIN detail d ON d.计划号 = m.计划号
SELECT 计划号,计划数量,入库数量,操作员
FROM
(
select 计划号 AS a计划号,--=case when n1=1 AND n2=1 then 计划号 else null END,
        计划号=case when n1=1 AND n2=1 then 计划号 else null END,
		计划数量=case when  n1=1 AND n2=1 then 计划数量 else null END,入库数量,操作员
from 
(
select *,n1=(select count(*) from #t b where  a.计划号=b.计划号 AND a.计划数量=b.计划数量 AND  b.入库数量<=a.入库数量 AND a.操作员<=b.操作员)
       ,n2=(select count(*) from #t b where a.计划号=b.计划号 AND a.计划数量=b.计划数量  and b.入库数量<=a.入库数量 AND a.操作员<=b.操作员)
from #t a
)t
UNION ALL 
SELECT 计划号 a计划号, '小计' 计划号 ,计划数量,SUM(入库数量)入库数量,'--'AS '操作员'
FROM #t a
GROUP BY 计划号,计划数量)a
ORDER BY a计划号,CASE WHEN ISNUMERIC(计划号)=1 THEN 2 WHEN 计划号 IS NULL THEN 1  ELSE 0 END desc

/*
计划号   计划数量        入库数量        操作员
----- ----------- ----------- ------
00001 10          4           张三  
NULL  NULL        6           张三  
小计    10          10          --
00002 20          10          张三  
NULL  NULL        10          李四  
小计    20          20          --
00003 30          30          李四  
小计    30          30          --
00004 30          10          张三  
NULL  NULL        10          李四  
小计    30          20          --
*/

[ChinaITOldMan]

GROUPING函数.GROUP BY WITH ROLLUP的运用。

[唐诗三百首]

SQL2000的方法,

create table [master](ID int,计划号 varchar(5),计划数量 int)
insert into master values(1,'00001',10)
insert into master values(2,'00002',20)
insert into master values(3,'00003',30)
insert into master values(4,'00004',30)

create table detail(ID int,计划号 varchar(5),入库数量 int,操作员 char(6))
insert into detail values(1,'00001',4,'张三')
insert into detail values(2,'00001',6,'张三')
insert into detail values(3,'00002',10,'张三')
insert into detail values(4,'00002',10,'李四')
insert into detail values(5,'00003',30,'李四')
insert into detail values(6,'00004',10,'张三')
insert into detail values(7,'00004',10,'李四')


select case when aid is not null and bid is not null and 
                 not exists(select 1 from detail u where u.计划号=t.计划号 and u.ID<t.bid) then 计划号
            when aid is not null and bid is null then '小计'
            when aid is null and bid is null then '总计' else '' end '计划号',
       case when aid is not null and bid is null then
                 (select top 1 rtrim(计划数量) from [master] u where u.ID=t.aid)
            when aid is null and bid is null then
                 (select rtrim(sum(计划数量)) from [master])     
            when aid is not null and bid is not null and 
                 not exists(select 1 from detail u where u.计划号=t.计划号 and u.ID<t.bid) then rtrim(计划数量)
            else '' end '计划数量',
       sum(入库数量) '入库数量',
       case when aid is not null and bid is not null then max(操作员)
            when aid is null or bid is null then '---' end '操作员'
from
(select a.ID 'aid',b.ID 'bid',a.计划号,a.计划数量,b.入库数量,b.操作员
 from [master] a
 left join detail b on a.计划号=b.计划号) t
group by aid,bid,计划号,计划数量
with rollup
having grouping(计划数量)=0 or grouping(bid)=1

/*
计划号   计划数量     入库数量      操作员
----- ------------ ----------- ------
00001    10           4          张三  
                      6          张三  
小计     10           10          ---   
00002    20          10          张三  
                     10          李四  
小计     20           20          ---   
00003   30           30          李四  
小计     30           30          ---   
00004   30           10          张三  
                     10          李四  
小计     30           20          ---   
总计     90           80          ---   

(12 row(s) affected)
*/

[无锡阔微信息科技]

引用 1 楼 fredrickhu 的回复:

GROUPING函数.GROUP BY WITH ROLLUP的运用。

顶一个