请教一个SQL语句的写法

plutu 2013-11-25 05:46:39
现有一个入库计划表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环境
...全文
203 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
无锡阔微信息科技 2013-11-26
  • 打赏
  • 举报
 回复
引用 1 楼 fredrickhu 的回复:
GROUPING函数.GROUP BY WITH ROLLUP的运用。
顶一个
唐诗三百首 2013-11-26
  • 打赏
  • 举报
 回复
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)
*/
ChinaITOldMan 2013-11-26
  • 打赏
  • 举报
 回复
GROUPING函数.GROUP BY WITH ROLLUP的运用。
發糞塗牆 2013-11-26
  • 打赏
  • 举报
 回复 
--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          --
*/
oreoconansisu 2013-11-26
  • 打赏
  • 举报
 回复
引用 7 楼 zywcy100 的回复:
[quote=引用 1 楼 fredrickhu 的回复:] GROUPING函数.GROUP BY WITH ROLLUP的运用。
顶一个[/quote] 再顶一个
plutu 2013-11-25
  • 打赏
  • 举报
 回复
谢谢，但感觉有点小复杂，并且结果排序不大对，我感觉应该有更简洁一点的办法，请大家继续
LongRui888 2013-11-25
  • 打赏
  • 举报
 回复
呵呵，写的有点复杂： 

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	    -
*/
--小F-- 2013-11-25
  • 打赏
  • 举报
 回复
另外你的表名命名为MASTER的话 换过一个比较讲究规范的领导 你要被骂死
--小F-- 2013-11-25
  • 打赏
  • 举报
 回复
GROUPING函数.GROUP BY WITH ROLLUP的运用。
asp.net知识库
.NET关于string转换的一个小Bug Regular Expressions 完整的在.net后台执行javascript脚本集合 ASP.NET 中的正则表达式 常用的匹配正则表达式和实例 经典正则表达式 delegate vs. event 我是谁？[C#] 表达式计算引擎...
SQL语句中EXISTS的使用详解及示例
其内查询语句返回一个结果集。EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。 一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返...
请教一个复杂的多结果分组统计的SQL语句写法
TABLE有两张：一个是CATEGORY_TABLE另一个是USER_PROBLEM_TABLE 两张表是通过CATEGORY_ID外键关联 USER_PROBLEM_TABLE中有REPLAY_STATUS字段0 表示未回复，1表示回复.类型的名称CATEGORY_NAME在CATEGORY_TABLE中。 ...
查询SQL Server 数据表中不包含某一列的SQL语句该怎么写？
在数据库中我有一张包含30个字段的Customer表，现在我要查询这个表，条件是不查询Remark字段，怎样写T-SQL语句最简单，难道我要select a,b,c,d,e...n from Customer吗？？？？？有没有一个内置函数，比如 select ...
oracle中sql怎么加判断,sql条件判断语句
sql 中的if 判断 语句应该怎么写sql中的if语句写法和java中差不多，都是先定义变量再使用变量进行判断。由于你的提问不是很清晰，我就自己的看法解答下你的问题：如果你指的是查询条件的话：select 需要查询的字段，...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章