谁能帮我精简一下sql语句？？

Question

select m4.ins_indate,m4.m_name,m4.m_unit,m4.ins_cun,m4.mid2,m4.m_add,m4.ins_reduce,m3.ad,m3.reduce from (select m2.m_id as mid1,sum(m2.m_add) as ad,sum(m2.ins_reduce) as reduce,count(distinct m2.m_id,m2.ins_indate)from mis_instoremat m2 where m2.ins_status!=1 group
by m2.m_id,m2.ins_indate order by m2.m_id) as m3
join (select m1.ins_indate,m1.m_name,m1.m_unit,m1.ins_cun,m1.m_id as mid2,m1.m_add,m1.ins_reduce from mis_instoremat as m1 inner join(select m_id,max(id) as ma from mis_instoremat group by m_id) b
on m1.m_id=b.m_id and m1.id=b.ma) as m4 on m4.mid2=m3.mid1 

Answer 1

贴你的记录出来看看，可能要重新设计SQL语句

Answer 2

不要贴图，并不方便别人调试。建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://forum.csdn.net/BList/OtherDatabase
   
   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试。   

Answer 3

CREATE TABLE `mis_instoremat` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ins_id` varchar(45) NOT NULL,
  `ins_indate` datetime NOT NULL,
  `ins_transcompany` varchar(45) default NULL,
  `ins_carnumber` varchar(45) default NULL,
  `ins_supplycompany` varchar(45) NOT NULL,
  `ins_supplyname` varchar(45) NOT NULL,
  `ins_pihao` varchar(45) NOT NULL,
  `m_id` varchar(45) NOT NULL,
  `m_name` varchar(45) NOT NULL,
  `ins_materialtype` varchar(45) NOT NULL,
  `ins_spec` varchar(45) NOT NULL,
  `m_add` double NOT NULL,
  `m_unit` varchar(45) NOT NULL,
  `m_store` varchar(45) NOT NULL,
  `ins_re` varchar(200) default NULL,
  `ins_eid` varchar(45) NOT NULL,
  `ins_storager` varchar(45) NOT NULL,
  `ins_status` int(11) NOT NULL,
  `ins_cun` double default NULL,
  `ins_reduce` double default NULL,
  `ins_status1` int(11) default NULL,
  `ins_status2` int(11) default NULL,
  PRIMARY KEY  (`id`)
) 
INSERT INTO `mis_instoremat` (`id`,`ins_id`,`ins_indate`,`ins_transcompany`,`ins_carnumber`,`ins_supplycompany`,`ins_supplyname`,`ins_pihao`,`m_id`,`m_name`,`ins_materialtype`,`ins_spec`,`m_add`,`m_unit`,`m_store`,`ins_re`,`ins_eid`,`ins_storager`,`ins_status`,`ins_cun`,`ins_reduce`,`ins_status1`,`ins_status2`) VALUES 
 (1,'SZYL_RK09120210001','2009-12-02 00:00:00','T','鲁b1578','莒南水泥厂','lzg','AS__100010','SZGD_CK09112110035','中联水泥','水泥','#LL',500,'袋','原材料库','','SZSY_JC09120210005','仓库',0,0,0,0,0),
 (2,'SZYL_RK09120210002','2009-12-02 00:00:00','T','鲁b78545','莒南水泥厂','lzg','AS__10008','SZGD_CK09111810033','矿粉','原材料','无',500,'KG','原材料库','','SZSY_JC09120210008','仓库',0,0,0,0,0),
 (3,'SZYL_RK09120210003','2009-12-02 00:00:00','方','鲁b8754','青岛沙集团','lzg','AS__10003','SZGD_CK09111810027','水','原材料','无',1000000,'方','原材料库','','SZSY_JC09120210006','仓库',0,0,0,0,0),
 (4,'SZYL_RK09120210004','2009-12-02 00:00:00','T','鲁c7845','青岛沙集团','lzg','AS__100010','SZGD_CK09112110034','p32.5水泥散','水泥','p32.5',500,'吨','原材料库','','SZSY_JC09120210009','仓库',0,0,0,0,0),
 (5,'SZYL_RK09120210005','2009-12-02 00:00:00','KG','鲁t5465','莒南水泥厂','lzg','AS__10002','SZGD_CK09111810032','膨胀剂','原材料','无',500,'KG','原材料库','','SZSY_JC09120210007','仓库',0,0,0,0,0),
 (6,'SZYL_RK09120210006','2009-12-02 00:00:00','KG','鲁d81111','莒南水泥厂','lzg','AS__10007','SZGD_CK09111810031','粉煤灰','原材料','无',500,'KG','原材料库','','SZSY_JC09120210004','仓库',0,0,0,0,0),
 (7,'SZYL_RK09120210007','2009-12-02 00:00:00','KG','鲁e5442','青岛沙集团','lzg','AS__10006','SZGD_CK09111810030','外加剂','原材料','无',500,'KG','原材料库','','SZSY_JC09120210003','仓库',0,0,0,0,0),
 (8,'SZYL_RK09120210008','2009-12-02 00:00:00','方','鲁78445','青岛沙集团','lzg','AS__10002','SZGD_CK09111810029','石子','原材料','无',500,'KG','原材料库','','SZSY_JC09120210002','仓库',0,0,0,0,0),
 (9,'SZYL_RK09120210009','2009-12-02 00:00:00','方','鲁14564','莒南水泥厂','lzg','AS__10001','SZGD_CK09111810028','砂','原材料','砂',500,'kg','原材料库','','SZSY_JC09120210001','仓库',0,0,0,0,0),
 (10,'SZYL_RK09120210010','2009-12-02 00:00:00','方','鲁b4865','青岛沙集团','lzg','AS__10001','SZGD_CK09111810028','砂','原材料','砂',333,'方','原材料库','','SZSY_JC09120210015','仓库',0,500,0,0,0),
 (11,'SZYL_RK09120210011','2009-12-02 00:00:00','kg','鲁c75596','圣泽集团','lzg','2','SZGD_CK09111810032','膨胀剂','原材料','无',333,'KG','原材料库','','SZSY_JC09120210023','仓库',0,500,0,0,0),
 (12,'SZYL_RK09120210012','2009-12-02 00:00:00','T','鲁d43212','莒南水泥厂','lzg','2','SZGD_CK09112110034','p32.5水泥散','水泥','p32.5',333,'T','原材料库','','SZSY_JC09120210029','仓库',0,500,0,0,0),
 (13,'SZYL_RK09120210013','2009-12-02 00:00:00','T','鲁d5552','莒南水泥厂','lzg','AS__10002','SZGD_CK09111810033','矿粉','原材料','无',333,'T','原材料库','','SZSY_JC09120210028','仓库',0,500,0,0,0),
 (14,'SZYL_RK09120210014','2009-12-02 00:00:00','T','鲁d8925','莒南水泥厂','lzg','AS__10001','SZGD_CK09112110035','中联水泥','水泥','#LL',333,'T','原材料库','','SZSY_JC09120210027','仓库',0,500,0,0,0),
 (15,'SZYL_RK09120210015','2009-12-02 00:00:00','方','鲁d5641','青岛沙集团','lzg','2','SZGD_CK09111810029','石子','原材料','无',333,'方','原材料库','','SZSY_JC09120210026','仓库',0,500,0,0,0),
 (16,'SZYL_RK09120210016','2009-12-02 00:00:00','方','鲁5876','青岛沙集团','lzg','2','SZGD_CK09111810028','砂','原材料','砂',200,'方','原材料库','','SZSY_JC09120210025','仓库',0,833,0,0,0),
 (17,'SZYL_RK09120210017','2009-12-02 00:00:00','kg','鲁d44485','圣泽集团','lzg','2','SZGD_CK09111810032','膨胀剂','原材料','无',200,'KG','原材料库','','SZSY_JC09120210024','仓库',0,833,0,0,0),
 (18,'SZYL_RK09120210018','2009-12-02 00:00:00','方','鲁t11656','青岛盛夏网络科技有限公司','lzg','2','SZGD_CK09111810027','水','原材料','无',100,'方','原材料库','','SZSY_JC09120210022','仓库',0,1000000,0,0,0),
 (19,'SZYL_RK09120210019','2009-12-02 00:00:00','kg','鲁t11656','圣泽集团','lzg','2','SZGD_CK09111810031','粉煤灰','原材料','无',200,'KG','原材料库','','SZSY_JC09120210021','仓库',0,500,0,0,0),
 (20,'SZYL_RK09120210020','2009-12-02 00:00:00','kg','鲁t11656','莒南水泥厂','lzg','2','SZGD_CK09111810030','外加剂','原材料','无',200,'KG','原材料库','','SZSY_JC09120210020','仓库',0,500,0,0,0),
 (21,'SZYL_RK09120210021','2009-12-02 00:00:00','t','鲁t11656','莒南水泥厂','lzg','AS__10002','SZGD_CK09112110034','p32.5水泥散','水泥','p32.5',200,'T','原材料库','','SZSY_JC09120210019','仓库',0,833,0,0,0),
 (22,'SZYL_RK09120210022','2009-12-02 00:00:00','t','鲁t11656','圣泽集团','lzg','AS__100010','SZGD_CK09111810033','矿粉','原材料','无',200,'T','原材料库','','SZSY_JC09120210018','仓库',0,833,0,0,0),
 (23,'SZYL_RK09120210023','2009-12-02 00:00:00','t','鲁t11656','莒南水泥厂','lzg','AS__10002','SZGD_CK09112110035','中联水泥','水泥','#LL',200,'T','原材料库','','SZSY_JC09120210017','仓库',0,833,0,0,0),
 (24,'SZYL_RK09120210024','2009-12-02 00:00:00','方','鲁t11656','莒南水泥厂','lzg','AS__10001','SZGD_CK09111810029','石子','原材料','无',200,'方','原材料库','','SZSY_JC09120210016','仓库',0,833,0,0,0),
 (25,'SZYL_RK09120210025','2009-12-02 00:00:00','','鲁t11656','青岛沙集团','l','3','SZGD_CK09112110034','p32.5水泥散','水泥','p32.5',100,'T','原材料库','','SZSY_JC09120210038','仓库',0,1033,0,0,0),
 (26,'SZYL_RK09120210026','2009-12-02 00:00:00','','鲁t11656','青岛沙集团','f','3','SZGD_CK09111810033','矿粉','原材料','无',100,'T','原材料库','','SZSY_JC09120210037','仓库',0,1033,0,0,0),
 (27,'SZYL_RK09120210027','2009-12-02 00:00:00','','鲁t11656','青岛沙集团','fg','3','SZGD_CK09112110035','中联水泥','水泥','#LL',100,'T','原材料库','','SZSY_JC09120210036','仓库',0,1033,0,0,0),
 (28,'SZYL_RK09120210028','2009-12-02 00:00:00','','鲁t11656','圣泽集团','sf','3','SZGD_CK09111810029','石子','原材料','无',100,'方','原材料库','','SZSY_JC09120210035','仓库',0,1033,0,0,0),
 (29,'SZYL_RK09120210029','2009-12-02 00:00:00','','鲁t11656','青岛市水泥厂','w','3','SZGD_CK09111810028','砂','原材料','砂',100,'方','原材料库','','SZSY_JC09120210034','仓库',0,1033,0,0,0),
 (30,'SZYL_RK09120210030','2009-12-02 00:00:00','','鲁t11656','青岛市质量保障局','fs','3','SZGD_CK09111810032','膨胀剂','原材料','无',100,'KG','原材料库','','SZSY_JC09120210033','仓库',0,1033,0,0,0),
 (31,'SZYL_RK09120210031','2009-12-02 00:00:00','','鲁t11656','圣泽集团','sdfd','3','SZGD_CK09111810027','水','原材料','无',233,'方','原材料库','','SZSY_JC09120210032','仓库',0,1000100,0,0,0),
 (32,'SZYL_RK09120210032','2009-12-02 00:00:00','','鲁t11656','青岛盛夏网络科技邮箱公司','sds','3','SZGD_CK09111810031','粉煤灰','原材料','无',333,'KG','原材料库','','SZSY_JC09120210031','仓库',0,700,0,0,0),
 (33,'SZYL_RK09120210033','2009-12-02 00:00:00','','鲁t11656','莒南水泥厂','dfsdf','3','SZGD_CK09111810030','外加剂','原材料','无',333,'KG','原材料库','','SZSY_JC09120210030','仓库',0,700,0,0,0),
 (34,'SZYL_RK09120210034','2009-12-02 00:00:00','','鲁t11656','青岛沙集团','dfsf','AS__10001','SZGD_CK09111810032','膨胀剂','原材料','无',150,'KG','原材料库','','SZSY_JC09120210014','仓库',0,1133,0,0,0),
 (35,'SZYL_RK09120210035','2009-12-02 00:00:00','','鲁t11656','莒南水泥厂','ds','AS__10001','SZGD_CK09111810030','外加剂','原材料','无',100,'KG','原材料库','','SZSY_JC09120210013','仓库',0,1033,0,0,0),
 (36,'SZYL_RK09120210036','2009-12-02 00:00:00','','鲁t11656','青岛沙集团','dfd','AS__10002','SZGD_CK09111810027','水','原材料','无',444,'方','原材料库','','SZSY_JC09120210012','仓库',0,1000333,0,0,0),
 (37,'SZYL_RK09120210037','2009-12-02 00:00:00','','鲁t11656','青岛沙集团','dfd','AS__10001','SZGD_CK09111810031','粉煤灰','原材料','无',250,'KG','原材料库','','SZSY_JC09120210011','仓库',0,1033,0,0,0),
 (38,'SZYL_RK09120210038','2009-12-02 00:00:00','','鲁t11656','莒南水泥厂','fsd','AS__10002','SZGD_CK09111810030','外加剂','原材料','无',130,'KG','原材料库','','SZSY_JC09120210010','仓库',0,1133,0,0,0),
 (39,'SZYL_RK09120210039','2009-12-02 00:00:00','','','df','sdf','lzg','SZGD_CK09111810032','膨胀剂','原材料','无',100,'KG','原材料库','','SZSY_JC09120210039','仓库',0,1283,0,0,0),
 (40,'SZYL_RK09120210040','2009-12-02 00:00:00','','鲁t11656','fdfd','lzg','lzg3','SZGD_CK09112110035','中联水泥','水泥','#LL',100,'T','原材料库','','SZSY_JC09120210040','仓库',0,1133,0,0,0);

Answer 4

要得到的是 ins_indate   m_id  m_add(最后一条的)  m_reduce（最后一条的) ins_cun(最后一条的)  sum(m_add) sum(ins_reduce) m_unit最后一条是某一条编号的最后一条 编号是（m_id）

Answer 5

按照你 #8楼 的说法如下。mysql> select ins_indate,m_id,m_add,ins_reduce,
    ->  (select sum(m_add) from mis_instoremat where ins_indate=a.ins_indate and
 m_id=a.m_id) as m_add,
    ->  (select sum(ins_reduce) from mis_instoremat where ins_indate=a.ins_indat
e and m_id=a.m_id) as ins_reduce
    -> from mis_instoremat a
    -> where not exists (select 1 from mis_instoremat where ins_indate=a.ins_ind
ate and m_id=a.m_id and id>a.id);
+---------------------+--------------------+-------+------------+---------+-----
-------+
| ins_indate          | m_id               | m_add | ins_reduce | m_add   | ins_
reduce |
+---------------------+--------------------+-------+------------+---------+-----
-------+
| 2009-12-02 00:00:00 | SZGD_CK09112110034 |   100 |          0 |    1133 |
     0 |
| 2009-12-02 00:00:00 | SZGD_CK09111810033 |   100 |          0 |    1133 |
     0 |
| 2009-12-02 00:00:00 | SZGD_CK09111810029 |   100 |          0 |    1133 |
     0 |
| 2009-12-02 00:00:00 | SZGD_CK09111810028 |   100 |          0 |    1133 |
     0 |
| 2009-12-02 00:00:00 | SZGD_CK09111810027 |   444 |          0 | 1000777 |
     0 |
| 2009-12-02 00:00:00 | SZGD_CK09111810031 |   250 |          0 |    1283 |
     0 |
| 2009-12-02 00:00:00 | SZGD_CK09111810030 |   130 |          0 |    1263 |
     0 |
| 2009-12-02 00:00:00 | SZGD_CK09111810032 |   100 |          0 |    1383 |
     0 |
| 2009-12-02 00:00:00 | SZGD_CK09112110035 |   100 |          0 |    1233 |
     0 |
+---------------------+--------------------+-------+------------+---------+-----
-------+
9 rows in set (0.00 sec)mysql>

Answer 6

select ins_indate,m_id,m_name,m_add,ins_reduce,(select sum(m_add) from mis_instoremat where ins_indate=a.ins_indate and
 m_id=a.m_id) as madd,(select sum(ins_reduce) from mis_instoremat where ins_indate=a.ins_indate and m_id=a.m_id) as insreduce
from mis_instoremat a
where not exists (select 1 from mis_instoremat where ins_indate=a.ins_indate and m_id=a.m_id and id>a.id) and ins_indate='2009-12-02';呵呵谢谢