多条链接查询sql的优化 union all 语句 如何优化提高性能 符合需求呢

select * from(
 select (select dep_name from department where dep_id= o.dep_id) depName,
 out_remark ,sum(out_total_amount) total,out_date   From out_warehouse o where
    		 dep_id in (select  dep_id from department)
    	  Group By dep_id Having Count(*) > 1
    	  ORDER BY dep_id
    	  ) t1
 union all
  select * from 
  ( select (select dep_name from department where dep_id = o.dep_id) depName,
 out_remark,sum(out_total_amount) total,out_date  From  out_warehouse o  Group By dep_id Having Count(*) = 1
  ORDER BY dep_id
    	  ) t2 
 union all
select * from (
    	  select  (select dep_name from department where dep_id = o1.dep_id) depName,out_remark,out_total_amount,out_date from out_warehouse o1 where  dep_id in
    	 ( select dep_id From out_warehouse o where
    		 dep_id in (select  dep_id from department)
    	  Group By dep_id Having Count(*) > 1
    	  ORDER BY dep_id
    	 ) order by dep_id
    	 ) p1
 union all
 select * from (
    	 select  (select dep_name from department where dep_id = o1.dep_id) depName,out_remark,out_total_amount,out_date from out_warehouse o1 where  dep_id not in
    	 ( select dep_id From out_warehouse o where
    		 dep_id in (select  dep_id from department)
    	  Group By dep_id Having Count(*) > 1
    	  ORDER BY dep_id
    	 ) order by dep_id
    	  ) p2

查询结果

depName out_remark    total out_date
维修科  9号楼修漏 16.84 2015/8/28
供水科 xxx号井用 11.2 2015/8/26
门市部 马步村修漏用5.6         2015/8/28
维修科 9号楼修漏 3.08 2015/8/10
维修科 8号楼修漏 8.16 2015/8/16
维修科 10号楼修漏 5.6     2015/8/21
供水科 xxx号井用 5.6    2015/8/26
供水科 1号楼修漏 5.6    2015/8/28
门市部 马步村修漏用5.6    2015/8/29

需要实现效果是：
查询出总的金额 后面是 详细金额数据


求教sql语句 如何实现 和 优化 ？

17 个解决方案

#1

这是上面多条链接查询结果 

#2

给点思路也可   怎么查询出这种效果呢？还有上面多条链接语句 能否优化呢？
坐等 大神  绞尽脑汁  苦思冥想  坐等 ....  

#3

可以尝试 WITH ROLLUP

#4

引用 3 楼 ACMAIN_CHM 的回复:

可以尝试 WITH ROLLUP

 好的  我试下  

#5

你那四个查询数据集，跟下面的查询结果是否一致？  

SELECT t2.dep_name, t1.out_remark, t1.total, t1.out_date FROM 
(
  SELECT dep_id, out_remark, SUM(out_total_amount) AS total, out_date   
    FROM out_warehouse 
    GROUP BY dep_id       
  UNION ALL
  SELECT dep_id, out_remark, out_total_amount, out_date 
    FROM out_warehouse
) AS t1     
LEFT JOIN department AS t2
ON t1.dep_id= t2.dep_id

#6

根据部门分组，时间升序，生成一个排序的字段出来

SELECT t.dep_name, t.out_remark, 
  t.total, t.out_date
FROM
(

  SELECT 
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS sort,
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS dep_name,
  t1.dep_id, t1.out_remark, 
  t1.out_total_amount AS total, t1.out_date
  FROM out_warehouse AS t1 
  
  UNION ALL
  SELECT '0', t2.dep_name, t1.dep_id, '费用合计', t1.total, '0'   
  FROM 
    (
     SELECT SUM(out_total_amount) AS total 
     FROM out_warehouse
     GROUP BY dep_id
    ) AS t1      
  LEFT JOIN department AS t2
  ON t1.dep_id= t2.dep_id
) AS t   
ORDER BY t.dep_id, t.sort  

#7

引用 5 楼 yangb0803 的回复:

你那四个查询数据集，跟下面的查询结果是否一致？  

SELECT t2.dep_name, t1.out_remark, t1.total, t1.out_date FROM 
(
  SELECT dep_id, out_remark, SUM(out_total_amount) AS total, out_date   
    FROM out_warehouse 
    GROUP BY dep_id       
  UNION ALL
  SELECT dep_id, out_remark, out_total_amount, out_date 
    FROM out_warehouse
) AS t1     
LEFT JOIN department AS t2
ON t1.dep_id= t2.dep_id

厉害 膜拜 

#8

引用 6 楼 yangb0803 的回复:

根据部门分组，时间升序，生成一个排序的字段出来

SELECT t.dep_name, t.out_remark, 
  t.total, t.out_date
FROM
(

  SELECT 
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS sort,
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS dep_name,
  t1.dep_id, t1.out_remark, 
  t1.out_total_amount AS total, t1.out_date
  FROM out_warehouse AS t1 
  
  UNION ALL
  SELECT '0', t2.dep_name, t1.dep_id, '费用合计', t1.total, '0'   
  FROM 
    (
     SELECT SUM(out_total_amount) AS total 
     FROM out_warehouse
     GROUP BY dep_id
    ) AS t1      
  LEFT JOIN department AS t2
  ON t1.dep_id= t2.dep_id
) AS t   
ORDER BY t.dep_id, t.sort  

引用 7 楼 tyhdxhl 的回复:

Quote: 引用 5 楼 yangb0803 的回复:

你那四个查询数据集，跟下面的查询结果是否一致？  

SELECT t2.dep_name, t1.out_remark, t1.total, t1.out_date FROM 
(
  SELECT dep_id, out_remark, SUM(out_total_amount) AS total, out_date   
    FROM out_warehouse 
    GROUP BY dep_id       
  UNION ALL
  SELECT dep_id, out_remark, out_total_amount, out_date 
    FROM out_warehouse
) AS t1     
LEFT JOIN department AS t2
ON t1.dep_id= t2.dep_id

厉害 膜拜 

加入 order by t1.dep_id,t2.dep_id,total desc   跟四条 多链接查询效果相同 。

#9

引用 7 楼 tyhdxhl 的回复:

厉害 膜拜 

是你想复杂了。。。


而且， order by t1.dep_id,t2.dep_id,total desc  排序，也跟 order by t1.dep_id, total desc   是一样的額。

#10

引用 6 楼 yangb0803 的回复:

根据部门分组，时间升序，生成一个排序的字段出来

SELECT t.dep_name, t.out_remark, 
  t.total, t.out_date
FROM
(

  SELECT 
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS sort,
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS dep_name,
  t1.dep_id, t1.out_remark, 
  t1.out_total_amount AS total, t1.out_date
  FROM out_warehouse AS t1 
  
  UNION ALL
  SELECT '0', t2.dep_name, t1.dep_id, '费用合计', t1.total, '0'   
  FROM 
    (
     SELECT SUM(out_total_amount) AS total 
     FROM out_warehouse
     GROUP BY dep_id
    ) AS t1      
  LEFT JOIN department AS t2
  ON t1.dep_id= t2.dep_id
) AS t   
ORDER BY t.dep_id, t.sort  

代码结构合理 不过报了个小错误

#11

引用 9 楼 yangb0803 的回复:

Quote: 引用 7 楼 tyhdxhl 的回复:

厉害 膜拜 

是你想复杂了。。。


而且， order by t1.dep_id,t2.dep_id,total desc  排序，也跟 order by t1.dep_id, total desc   是一样的額。

大神说得对 ！ 支持 学习 学习 ！  太感谢你啦！！！让咱把复杂东西简单化  求教 一个unknown column in 'field list' 错误。

#12

引用 11 楼 tyhdxhl 的回复:

Quote: 引用 9 楼 yangb0803 的回复:

Quote: 引用 7 楼 tyhdxhl 的回复:

厉害 膜拜 

是你想复杂了。。。


而且， order by t1.dep_id,t2.dep_id,total desc  排序，也跟 order by t1.dep_id, total desc   是一样的額。

大神说得对 ！ 支持 学习 学习 ！  太感谢你啦！！！让咱把复杂东西简单化  求教 一个unknown column in 'field list' 错误。

找不到这个列，你自己看下有没有这个列在表里

#13

SELECT t.dep_name, t.out_remark, 
  t.total, t.out_date
FROM
(
 
  SELECT 
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS sort,
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS dep_name,
  t1.dep_id, t1.out_remark, 
  t1.out_total_amount AS total, t1.out_date
  FROM out_warehouse AS t1 
   
  UNION ALL
  SELECT '0', t2.dep_name, t1.dep_id, '费用合计', t1.total, '0'   
  FROM 
    (
     SELECT dep_id, SUM(out_total_amount) AS total 
     FROM out_warehouse
     GROUP BY dep_id
    ) AS t1      
  LEFT JOIN department AS t2
  ON t1.dep_id= t2.dep_id
) AS t   
ORDER BY t.dep_id, t.sort  

在UNION ALL 数据集的查询中，將 dep_id 添加进去。 

#14

引用 12 楼 u011575570 的回复:

Quote: 引用 11 楼 tyhdxhl 的回复:

Quote: 引用 9 楼 yangb0803 的回复:

Quote: 引用 7 楼 tyhdxhl 的回复:

厉害 膜拜 

是你想复杂了。。。


而且， order by t1.dep_id,t2.dep_id,total desc  排序，也跟 order by t1.dep_id, total desc   是一样的額。

大神说得对 ！ 支持 学习 学习 ！  太感谢你啦！！！让咱把复杂东西简单化  求教 一个unknown column in 'field list' 错误。

找不到这个列，你自己看下有没有这个列在表里

这个列是存在的 自增ID

#15

引用 13 楼 yangb0803 的回复:

SELECT t.dep_name, t.out_remark, 
  t.total, t.out_date
FROM
(
 
  SELECT 
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS sort,
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS dep_name,
  t1.dep_id, t1.out_remark, 
  t1.out_total_amount AS total, t1.out_date
  FROM out_warehouse AS t1 
   
  UNION ALL
  SELECT '0', t2.dep_name, t1.dep_id, '费用合计', t1.total, '0'   
  FROM 
    (
     SELECT dep_id, SUM(out_total_amount) AS total 
     FROM out_warehouse
     GROUP BY dep_id
    ) AS t1      
  LEFT JOIN department AS t2
  ON t1.dep_id= t2.dep_id
) AS t   
ORDER BY t.dep_id, t.sort  

在UNION ALL 数据集的查询中，將 dep_id 添加进去。 

谢谢  我试了半天 没有搞定 请大神  把改好的sql放上来吧  我不吝分数的

#16

这是我的所有模拟，字段不对的，你跟着自己修改成你自己的。

CREATE TABLE `department` (
  `dep_id` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dep_name` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
--------------------------------------------------
dep_id	dep_name
1	维修科
2	供水科
3	门市部

--------------------------------------------------
CREATE TABLE `out_warehouse` (
  `dep_id` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `out_remark` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `out_total_amount` DECIMAL(18,2) DEFAULT NULL,
  `out_date` DATE DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
--------------------------------------------------
dep_id	out_remark	out_total_amount	out_date
1	9号楼修漏	3.08	2015-08-10
1	8号楼修漏	8.16	2015-08-16
1	10号楼修漏	5.60	2015-08-21
2	xxx号井用	5.60	2015-08-27
2	1号楼修漏	5.60	2015-08-28
3	马步村修漏用	5.60	2015-08-29


--------------------------------------------------
SELECT t.dep_name, t.out_remark, 
  t.total, t.out_date
FROM
( 
  SELECT 
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS sort,
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS dep_name,
  t1.dep_id, t1.out_remark, 
  t1.out_total_amount AS total, t1.out_date
  FROM out_warehouse AS t1 
    
  UNION ALL
  SELECT '0', t2.dep_name, t1.dep_id, '费用合计', t1.total, ''   
  FROM 
    (
     SELECT dep_id, SUM(out_total_amount) AS total 
     FROM out_warehouse
     GROUP BY dep_id
    ) AS t1      
  LEFT JOIN department AS t2
  ON t1.dep_id= t2.dep_id
) AS t   
ORDER BY t.dep_id, t.sort  
--------------------------------------------------

-- 结果：
dep_name	out_remark	total	out_date
维修科	费用合计	16.84	
1	9号楼修漏	3.08	2015-08-10
2	8号楼修漏	8.16	2015-08-16
3	10号楼修漏	5.60	2015-08-21
供水科	费用合计	11.20	
1	xxx号井用	5.60	2015-08-27
2	1号楼修漏	5.60	2015-08-28
门市部	费用合计	5.60	
1	马步村修漏用	5.60	2015-08-29

#17

引用 16 楼 yangb0803 的回复:

这是我的所有模拟，字段不对的，你跟着自己修改成你自己的。

CREATE TABLE `department` (
  `dep_id` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dep_name` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
--------------------------------------------------
dep_id	dep_name
1	维修科
2	供水科
3	门市部

--------------------------------------------------
CREATE TABLE `out_warehouse` (
  `dep_id` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `out_remark` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `out_total_amount` DECIMAL(18,2) DEFAULT NULL,
  `out_date` DATE DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
--------------------------------------------------
dep_id	out_remark	out_total_amount	out_date
1	9号楼修漏	3.08	2015-08-10
1	8号楼修漏	8.16	2015-08-16
1	10号楼修漏	5.60	2015-08-21
2	xxx号井用	5.60	2015-08-27
2	1号楼修漏	5.60	2015-08-28
3	马步村修漏用	5.60	2015-08-29


--------------------------------------------------
SELECT t.dep_name, t.out_remark, 
  t.total, t.out_date
FROM
( 
  SELECT 
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS sort,
  (
    SELECT COUNT(*)+1 FROM out_warehouse  
    WHERE (dep_id=t1.dep_id 
    AND out_date<t1.out_date)
    OR (dep_id=t1.dep_id
    AND out_date<t1.out_date 
    AND out_remark < t1.out_remark)
  ) AS dep_name,
  t1.dep_id, t1.out_remark, 
  t1.out_total_amount AS total, t1.out_date
  FROM out_warehouse AS t1 
    
  UNION ALL
  SELECT '0', t2.dep_name, t1.dep_id, '费用合计', t1.total, ''   
  FROM 
    (
     SELECT dep_id, SUM(out_total_amount) AS total 
     FROM out_warehouse
     GROUP BY dep_id
    ) AS t1      
  LEFT JOIN department AS t2
  ON t1.dep_id= t2.dep_id
) AS t   
ORDER BY t.dep_id, t.sort  
--------------------------------------------------

-- 结果：
dep_name	out_remark	total	out_date
维修科	费用合计	16.84	
1	9号楼修漏	3.08	2015-08-10
2	8号楼修漏	8.16	2015-08-16
3	10号楼修漏	5.60	2015-08-21
供水科	费用合计	11.20	
1	xxx号井用	5.60	2015-08-27
2	1号楼修漏	5.60	2015-08-28
门市部	费用合计	5.60	
1	马步村修漏用	5.60	2015-08-29

dep_name out_remark total out_date
维修科 费用合计 16.84
1 9号楼修漏 3.08 2015-08-10
2 8号楼修漏 8.16 2015-08-16
3 10号楼修漏 5.60 2015-08-21
供水科 费用合计 11.20
1 xxx号井用 5.60 2015-08-27
2 1号楼修漏 5.60 2015-08-28
门市部 费用合计 5.60
1 马步村修漏用 5.60 2015-08-29


大神求教下 我想在空白日期哪里加一个【统计总金额 】如何实现呢