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

tyhdxhl 2015-08-31 10:32:20



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语句如何实现和优化？

...全文

991 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

tyhdxhl 2015-11-23

打赏
举报

引用 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

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

道玄希言 2015-09-01

打赏
举报

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



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

tyhdxhl 2015-09-01

打赏
举报

引用 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放上来吧我不吝分数的

tyhdxhl 2015-09-01

打赏
举报

引用 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 是一样的額。[/quote] 大神说得对！支持学习学习！太感谢你啦！！！让咱把复杂东西简单化求教一个unknown column in 'field list' 错误。[/quote] 找不到这个列，你自己看下有没有这个列在表里[/quote] 这个列是存在的自增ID

道玄希言 2015-09-01

打赏
举报



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 添加进去。

tyhdxhl 2015-08-31

打赏
举报

引用 3 楼 ACMAIN_CHM 的回复:

可以尝试 WITH ROLLUP

好的我试下

ACMAIN_CHM 2015-08-31

打赏
举报

可以尝试 WITH ROLLUP

tyhdxhl 2015-08-31

打赏
举报

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

tyhdxhl 2015-08-31

打赏
举报

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

rick-he 2015-08-31

打赏
举报

引用 11 楼 tyhdxhl 的回复:

[quote=引用 9 楼 yangb0803 的回复:] [quote=引用 7 楼 tyhdxhl 的回复:] 厉害膜拜

tyhdxhl 2015-08-31

打赏
举报

引用 9 楼 yangb0803 的回复:

[quote=引用 7 楼 tyhdxhl 的回复:] 厉害膜拜

tyhdxhl 2015-08-31

打赏
举报

引用 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

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

道玄希言 2015-08-31

打赏
举报

引用 7 楼 tyhdxhl 的回复:

厉害膜拜

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

tyhdxhl 2015-08-31

打赏
举报

引用 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

厉害膜拜

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

tyhdxhl 2015-08-31

打赏
举报

引用 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

厉害膜拜

道玄希言 2015-08-31

打赏
举报

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



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

道玄希言 2015-08-31

打赏
举报

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



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