物料表
-----
物料编号 颜色组编号 尺码组编号
A Color Size
=====颜色组表
-----
颜色组编号 颜色编号
Color C1
Color C2
Color C3
Color C4
=====尺码组表
-----
尺码组编号 尺码编号
Size S1
Size S2
Size S3
Size S4库存表
物料编号 颜色编号 尺码编号 数量
A C1 S2 1
A C1 S3 1
A C2 S1 1
A C2 S2 1
A C3 S2 1
A C3 S3 1
A C3 S4 1现在想查出:
物料编号,颜色编号,S1数量,S2数量,S3数量,S4数量
A C1 0 1 1 0
A C2 1 1 0 0
A C3 0 1 1 1
A C4 0 0 0 0
-----
物料编号 颜色组编号 尺码组编号
A Color Size
=====颜色组表
-----
颜色组编号 颜色编号
Color C1
Color C2
Color C3
Color C4
=====尺码组表
-----
尺码组编号 尺码编号
Size S1
Size S2
Size S3
Size S4库存表
物料编号 颜色编号 尺码编号 数量
A C1 S2 1
A C1 S3 1
A C2 S1 1
A C2 S2 1
A C3 S2 1
A C3 S3 1
A C3 S4 1现在想查出:
物料编号,颜色编号,S1数量,S2数量,S3数量,S4数量
A C1 0 1 1 0
A C2 1 1 0 0
A C3 0 1 1 1
A C4 0 0 0 0
SUM(IF(尺码编号='S2',数量,0)),
SUM(IF(尺码编号='S3',数量,0)),
SUM(IF(尺码编号='S4',数量,0))
FROM 库存表 A
GROUP BY A.物料编号,A.颜色编号
SUM(IF(D.尺码编号='S1',A.数量,0)),
SUM(IF(D.尺码编号='S2',A.数量,0)),
SUM(IF(D.尺码编号='S3',A.数量,0)),
SUM(IF(D.尺码编号='S4',A.数量,0))
FROM (
SELECT * FROM
(SELECT 颜色编号 FROM 颜色组表) A,
(SELECT DISTINCT 物料编号 FROM 库存表) B) C LEFT JOIN 库存表 D
ON C.物料编号=D.物料编号 AND C.颜色编号=D.颜色编号
GROUP BY C.物料编号,C.颜色编号
CREATE TABLE `t_color_group` (
`fColorGroupCode` varchar(15) NOT NULL,
`fColorGroupName` varchar(20) NOT NULL,
PRIMARY KEY (`fColorGroupCode`),
UNIQUE KEY `fColorGroupName_UNIQUE` (`fColorGroupName`),
UNIQUE KEY `fColorGroupCode_UNIQUE` (`fColorGroupCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='颜色组表';DROP TABLE IF EXISTS `t_color_detail`;
CREATE TABLE `t_color_detail` (
`fColorGroupCode` varchar(15) NOT NULL,
`fColorCode` varchar(15) NOT NULL,
`fColorName` varchar(80) NOT NULL,
PRIMARY KEY (`fColorGroupCode`,`fColorCode`),
KEY `fkColorGroupCode_idx` (`fColorGroupCode`),
CONSTRAINT `fkColorGroupCode` FOREIGN KEY (`fColorGroupCode`) REFERENCES `t_color_group` (`fColorGroupCode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='颜色明细表';CREATE TABLE `t_size_group` (
`fSizeGroupCode` VARCHAR(15) NOT NULL ,
`fSizeGroupName` VARCHAR(20) NOT NULL ,
PRIMARY KEY (`fSizeGroupCode`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = '尺码组表';DROP TABLE IF EXISTS `t_size_detail`;
CREATE TABLE `t_size_detail` (
`fSizeGroupCode` varchar(15) NOT NULL,
`fSizeCode` varchar(15) NOT NULL,
`fSizeName` varchar(80) NOT NULL,
PRIMARY KEY (`fSizeGroupCode`,`fSizeCode`),
KEY `fkSizeGroupCode_idx` (`fSizeGroupCode`),
CONSTRAINT `fkSizeGroupCode` FOREIGN KEY (`fSizeGroupCode`) REFERENCES `t_size_group` (`fSizeGroupCode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='尺码明细表';DROP TABLE IF EXISTS `t_material_category`;
CREATE TABLE `t_material_category` (
`fMCCode` varchar(15) NOT NULL,
`fMCName` varchar(20) NOT NULL,
`fMCParentsCode` varchar(15) DEFAULT NULL,
`fMCColorGroupCode` varchar(15) DEFAULT NULL,
`fMCSizeGroupCode` varchar(15) DEFAULT NULL,
PRIMARY KEY (`fMCCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物料类别表';DROP TABLE IF EXISTS `t_material`;
CREATE TABLE `t_material` (
`fMaterialCode` varchar(30) NOT NULL,
`fMaterialName` varchar(40) NOT NULL,
PRIMARY KEY (`fMaterialCode`,`fColorDetailCode`,`fSizeDetailCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物料表';
DROP TABLE IF EXISTS `t_depot`;
CREATE TABLE `t_depot` (
`fDepotCode` varchar(15) NOT NULL,
`fDepotName` varchar(40) NOT NULL,
PRIMARY KEY (`fDepotCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='仓库表';
DROP TABLE IF EXISTS `t_inventory`;
CREATE TABLE `t_inventory` (
`fMaterialCode` varchar(30) NOT NULL,
`fSizeCode` varchar(15) NOT NULL DEFAULT '',
`fColorCode` varchar(15) NOT NULL DEFAULT '',
`fDepotCode` varchar(15) NOT NULL,
`fAmount` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`fMaterialCode`,`fSizeCode`,`fColorCode`,`fDepotCode`),
KEY `fkDepotCode_idx` (`fDepotCode`),
CONSTRAINT `fkDepotCode` FOREIGN KEY (`fDepotCode`) REFERENCES `t_depot` (`fDepotCode`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='库存表';
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...