三个表是按照以下方式关联的:
物料表:物料id,名称,型号...
入库表:入库id,物料id,...入库类别,入库数量,入库日期,sn编号,...
出库表:出库id,入库id...出库数量,出库日期...
SELECT t1.Mat_id, Mat_name, Mat_mode, MIS_re1, MIS_date, MO_date
FROM (SELECT m.Mat_id, MIS_id, Mat_name, Mat_mode, MIS_re1, MIS_date
FROM material m, MaterialInStorage mis
WHERE m.Mat_id = mis.Mat_id
AND Mat_category <>3
) AS t1
LEFT JOIN (SELECT m.Mat_id, mis.MIS_id, MO_date
FROM Material m, MaterialInStorage mis, MaterialOutstock mo
WHERE m.Mat_id = mis.Mat_id
AND mis.MIS_id = mo.MIS_id
) AS t2 ON t1.MIS_id = t2.MIS_id
ORDER BY MIS_re1, MIS_date
该语句得到的结果是
Mat_id Mat_name Mat_mode MIS_re1 MIS_date MO_date
000027 002 电脑 222 20110101 20110801
000027 002 电脑 22SD 20100820 20120301
000027 002 电脑 22SD 20130101 NULL
000005 台式电脑 DELL 990 GH1D63X 20110901 20110906
000005 台式电脑 DELL 990 GH1D63X 20110913 20110925
000005 台式电脑 DELL 990 GH1D63X 20111001 NULL
000013 笔记本电脑 DELL V1450 H9VCLP1 20110814 20110905 我想要得到每一个MIS_re1的最后一条
也就是说要得到的是如下数据:
000027 002 电脑 222 20110101 20110801
000027 002 电脑 22SD 20130101 NULL
000005 台式电脑 DELL 990 GH1D63X 20111001 NULL
000013 笔记本电脑 DELL V1450 H9VCLP1 20110814 20110905
请问我该怎么修改语句?
物料表:物料id,名称,型号...
入库表:入库id,物料id,...入库类别,入库数量,入库日期,sn编号,...
出库表:出库id,入库id...出库数量,出库日期...
SELECT t1.Mat_id, Mat_name, Mat_mode, MIS_re1, MIS_date, MO_date
FROM (SELECT m.Mat_id, MIS_id, Mat_name, Mat_mode, MIS_re1, MIS_date
FROM material m, MaterialInStorage mis
WHERE m.Mat_id = mis.Mat_id
AND Mat_category <>3
) AS t1
LEFT JOIN (SELECT m.Mat_id, mis.MIS_id, MO_date
FROM Material m, MaterialInStorage mis, MaterialOutstock mo
WHERE m.Mat_id = mis.Mat_id
AND mis.MIS_id = mo.MIS_id
) AS t2 ON t1.MIS_id = t2.MIS_id
ORDER BY MIS_re1, MIS_date
该语句得到的结果是
Mat_id Mat_name Mat_mode MIS_re1 MIS_date MO_date
000027 002 电脑 222 20110101 20110801
000027 002 电脑 22SD 20100820 20120301
000027 002 电脑 22SD 20130101 NULL
000005 台式电脑 DELL 990 GH1D63X 20110901 20110906
000005 台式电脑 DELL 990 GH1D63X 20110913 20110925
000005 台式电脑 DELL 990 GH1D63X 20111001 NULL
000013 笔记本电脑 DELL V1450 H9VCLP1 20110814 20110905 我想要得到每一个MIS_re1的最后一条
也就是说要得到的是如下数据:
000027 002 电脑 222 20110101 20110801
000027 002 电脑 22SD 20130101 NULL
000005 台式电脑 DELL 990 GH1D63X 20111001 NULL
000013 笔记本电脑 DELL V1450 H9VCLP1 20110814 20110905
请问我该怎么修改语句?
SELECT t1.Mat_id, Mat_name, Mat_mode, MIS_re1, MIS_date, MO_date
FROM (SELECT m.Mat_id, MIS_id, Mat_name, Mat_mode, MIS_re1, MIS_date
FROM material m, MaterialInStorage mis
WHERE m.Mat_id = mis.Mat_id
AND Mat_category <>3
) AS t1
LEFT JOIN (SELECT m.Mat_id, mis.MIS_id, MO_date
FROM Material m, MaterialInStorage mis, MaterialOutstock mo
WHERE m.Mat_id = mis.Mat_id
AND mis.MIS_id = mo.MIS_id
) AS t2 ON t1.MIS_id = t2.MIS_id
ORDER BY MIS_re1, MIS_date DESC) AS all GROUP BY all.MIS_re1 all 里如果有同名字段会报错。自己调试下。