筛选后插入另外一张表

MySql 码拜 4年前 (2016-02-20) 199次浏览
筛选出表中TYPE和NAME相同的记录行把数量值做一个总和,同时TIME需要最新的时间,而OPERATOR也是最新时间对应的人。
表如下:
TYPE                 NAME                    NUMBER                          OPERATOR                             TIME
上衣                     HM                                10                                  小明                              2015-09-08
裤子                    JEANS                            20                                 张三                              2015-01-03
上衣                      HM                                 5                                   赵四                              2015-07-08
筛选出的结果应该是:
TYPE                   NAME                      SUM(NUMBER)               OPERATOR                       TIME
上衣                       HM                              15                                  小明                            2015-09-08
裤子                    JEANS                             20                                 张三                            2015-01-03

谢谢

解决方案

20

select   t.TYPE,t.NAME,SUM(NUMBER),t.OPERATOR,max(TIME)  from table t group by t.TYPE,t.NAME

30

SELECT   a.type,a.name,a.operator,sum(number),max(a.time)
FROM tb
GROUP BY  a.type,a.name,a.operator

30

SELECT a.*,b.OPERATOR FROM
(SELECT TYPE,NAME,SUM(NUMBER) AS NUMBER,MAX(TIME) AS TIME FROM testtb GROUP BY TYPE,NAME) a,
testtb b
WHERE a.TYPE=b.TYPE AND a.NAME=b.NAME AND a.TIME=b.TIME

CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明筛选后插入另外一张表
喜欢 (0)
[1034331897@qq.com]
分享 (0)