create table WareHouseProperty
(
ID int(11) not null auto_increment comment 'ID',
WIID int(4) comment '仓库信息ID',
RackID varchar(2) comment '货架号:用A,B,C表示,一律用大写',
Layer int(1) comment '第几层',
Number int(2) comment '该层的第几个格子:01~99',
BarCode varchar(20) comment '条形码',
primary key (ID)
);
-- 测试数据
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',1,'1','14-A1A01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',1,'2','14-A1A02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',1,'3','14-A1A03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',2,'1','14-A2A01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',2,'2','14-A2A02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',2,'3','14-A2A03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',3,'1','14-A3A01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',3,'2','14-A3A02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'A',3,'3','14-A3A03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',1,'1','14-B1B01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',1,'2','14-B1B02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',1,'3','14-B1B03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',1,'4','14-B1B04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',2,'1','14-B2B01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',2,'2','14-B2B02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',2,'3','14-B2B03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',2,'4','14-B2B04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',3,'1','14-B3B01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',3,'2','14-B3B02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',3,'3','14-B3B03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',3,'4','14-B3B04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',4,'1','14-B4B01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',4,'2','14-B4B02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',4,'3','14-B4B03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'B',4,'4','14-B4B04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',1,'1','14-C1C01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',1,'3','14-C1C03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',1,'4','14-C1C04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',1,'5','14-C1C05');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',2,'1','14-C2C01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',2,'2','14-C2C02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',2,'3','14-C2C03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',2,'4','14-C2C04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',2,'5','14-C2C05');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',3,'1','14-C3C01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',3,'2','14-C3C02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',3,'3','14-C3C03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',3,'4','14-C3C04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',3,'5','14-C3C05');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',4,'1','14-C4C01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',4,'2','14-C4C02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',4,'3','14-C4C03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',4,'4','14-C4C04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',4,'5','14-C4C05');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',5,'1','14-C5C01');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',5,'2','14-C5C02');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',5,'3','14-C5C03');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',5,'4','14-C5C04');
insert into WareHouseProperty (WIID,RackID,Layer,Number,BarCode) values (14,'C',5,'5','14-C5C05');
-- 希望查询的结果这样
-- RackID Layer Number
-- A 3 3
-- B 4 4
-- C 5 5
-- 因为数据可能会有不连续,不能取他的最大值。
(
ID int(11) not null auto_increment comment 'ID',
WIID int(4) comment '仓库信息ID',
RackID varchar(2) comment '货架号:用A,B,C表示,一律用大写',
Layer int(1) comment '第几层',
Number int(2) comment '该层的第几个格子:01~99',
BarCode varchar(20) comment '条形码',
primary key (ID)
);
-- 查询条件是 根据wiid来的
-- 查询结果是 某仓库 的 货架 有多少层 这层有少个格子(其中某层格子数最多的)
-> count(distinct Layer) as Layer,
-> count(distinct Number) as Number
-> from WareHouseProperty
-> group by RackID;
+--------+-------+--------+
| RackID | Layer | Number |
+--------+-------+--------+
| A | 3 | 3 |
| B | 4 | 4 |
| C | 5 | 5 |
+--------+-------+--------+
3 rows in set (0.00 sec)mysql>