解决方案 »
- mysql如何按条件提取每小时最后一条数据?
- 【高分】PowerDesigner15如何设置MySql的自增列的起始值和步长?
- windows mysql 疑問
- 如何用vb6.0实现与mysql数据库的连接和添加操作?
- mysql 联合查询
- JDBC + eclipse,mysql中文字符乱码
- mysql中表a和表b合并为表c,再将表c恢复至表a,请问如何处理较好
- 麻烦大家帮忙看看,oracle过程转mysql
- 安装MySQL卡在starting server
- python或者sql信息补全问题
- 如何用mysql语言实现动态的行转列
- java项目部署在tomcat里,mysql数据库部署在另一个服务器里,项目和数据库没部署在一个服务器里,我知道mysql服务器的IP,该怎样把数据传到数据库
SU13061301011|3207*195
假设后面对应的是SU13061301012,对应结果:
SU13061301012|3207*160(/*已知入库总量540-之前用掉的390(195+195),且SU13061301012对应的总量大于160时*/)各表插入数据的SQL呢?是说原表插入的原数据吗?
yes
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
[code=sql]
--
-- 表的结构 `kw_manage`
--
CREATE TABLE IF NOT EXISTS `kw_manage` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`rkno` varchar(25) NOT NULL,
`packageno` varchar(20) NOT NULL,
`type` varchar(20) NOT NULL,
`cust_no` varchar(20) NOT NULL,
`snp` int(4) NOT NULL,
`wh_info` varchar(20) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`ID`),
KEY `packageno` (`packageno`),
KEY `date` (`date`)
);INSERT INTO `kw_manage` (`ID`, `username`, `rkno`, `packageno`, `type`, `cust_no`, `snp`, `wh_info`, `date`) VALUES
(1, 'admin', '201302190001', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', 15, 'YE-00-01', '2013-06-27 10:56:10'),
(2, 'admin', '201302190001', 'VP3.0 130219002', 'BEM330-500', '23703 3AW0A', 15, 'YE-00-01', '2013-06-27 10:56:12'),
(3, 'admin', '201302190001', 'VP3.0 130219003', 'BEM330-500', '23703 3AW0A', 15, 'YE-00-01', '2013-06-27 10:56:14');
-- --------------------------------------------------------
--
-- 表的结构 `stock_detail`
--
CREATE TABLE IF NOT EXISTS `stock_detail` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`stock_no` varchar(20) NOT NULL,
`adress` varchar(10) NOT NULL,
`arr_time` datetime NOT NULL,
`c_type` varchar(20) NOT NULL,
`pu_no` varchar(20) NOT NULL,
`cust_no` varchar(20) NOT NULL,
`mount` int(10) NOT NULL,
`snp` int(10) NOT NULL,
`c1_time` datetime NOT NULL,
`c2_time` datetime NOT NULL,
PRIMARY KEY (`ID`)
);INSERT INTO `stock_detail` (`ID`, `stock_no`, `adress`, `arr_time`, `c_type`, `pu_no`, `cust_no`, `mount`, `snp`, `c1_time`, `c2_time`) VALUES
(3, 'SU13061301011', 'A1', '2013-06-19 09:00:00', 'B12D(2)', 'K25B423', '237033AW0A', 13, 15, '2013-06-15 15:00:00', '2013-06-15 15:30:00'),
(4, 'SU13061301011', 'A1', '2013-06-19 09:00:00', 'B12D(2)', 'K25B424', '237033AW0A', 13, 15, '2013-06-15 15:00:00', '2013-06-15 15:30:00'),
(11, 'SU13061301015', 'B', '2013-06-19 19:00:00', 'D511', 'K25I101', '23703EX80A', 12, 15, '2013-06-15 15:00:00', '2013-06-15 15:30:00');
--
-- 表的结构 `vp_barcode_2d_y`
--
CREATE TABLE IF NOT EXISTS `vp_barcode_2d_y` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(25) NOT NULL,
`packageno` varchar(20) NOT NULL,
`hi_no` varchar(20) NOT NULL,
`cust_no` varchar(20) NOT NULL,
`lotno` varchar(20) NOT NULL,
`description` varchar(20) NOT NULL,
`type` varchar(20) NOT NULL,
`barcode` varchar(50) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`ID`),
KEY `packageno` (`packageno`),
KEY `lotno` (`lotno`)
);
INSERT INTO `vp_barcode_2d_y` (`ID`, `username`, `packageno`, `hi_no`, `cust_no`, `lotno`, `description`, `type`, `barcode`, `date`) VALUES
(241, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0066', '2013-02-19 08:49:15'),
(242, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0008', '2013-02-19 08:49:18'),
(243, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0042', '2013-02-19 08:49:21'),
(244, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0053', '2013-02-19 08:49:24'),
(245, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0030', '2013-02-19 08:49:30'),
(246, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0021', '2013-02-19 08:49:33'),
(247, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A3524', '2013-02-19 08:49:35'),
(248, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A3532', '2013-02-19 08:49:39'),
(249, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0033', '2013-02-19 08:49:41'),
(250, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0044', '2013-02-19 08:49:44'),
(251, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0058', '2013-02-19 08:49:47'),
(252, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A3513', '2013-02-19 08:49:50'),
(253, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0055', '2013-02-19 08:49:53'),
(254, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0029', '2013-02-19 08:49:56'),
(255, '00219', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', '3207', 'VP3.0', 'BEM330-500', 'ZMBEM330-500A10000ZM320723703 3AW0A0061', '2013-02-19 08:49:58');
-- 表的结构 `kw_manage`
--
CREATE TABLE IF NOT EXISTS `kw_manage` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`rkno` varchar(25) NOT NULL,
`packageno` varchar(20) NOT NULL,
`type` varchar(20) NOT NULL,
`cust_no` varchar(20) NOT NULL,
`snp` int(4) NOT NULL,
`wh_info` varchar(20) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`ID`),
KEY `packageno` (`packageno`),
KEY `date` (`date`)
);INSERT INTO `kw_manage` (`ID`, `username`, `rkno`, `packageno`, `type`, `cust_no`, `snp`, `wh_info`, `date`) VALUES
(1, 'admin', '201302190001', 'VP3.0 130219001', 'BEM330-500', '23703 3AW0A', 15, 'YE-00-01', '2013-06-27 10:56:10'),
(2, 'admin', '201302190001', 'VP3.0 130219002', 'BEM330-500', '23703 3AW0A', 15, 'YE-00-01', '2013-06-27 10:56:12'),
(3, 'admin', '201302190001', 'VP3.0 130219003', 'BEM330-500', '23703 3AW0A', 15, 'YE-00-01', '2013-06-27 10:56:14');
-- --------------------------------------------------------
--
-- 表的结构 `stock_detail`
--
CREATE TABLE IF NOT EXISTS `stock_detail` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`stock_no` varchar(20) NOT NULL,
`adress` varchar(10) NOT NULL,
`arr_time` datetime NOT NULL,
`c_type` varchar(20) NOT NULL,
`pu_no` varchar(20) NOT NULL,
`cust_no` varchar(20) NOT NULL,
`mount` int(10) NOT NULL,
`snp` int(10) NOT NULL,
`c1_time` datetime NOT NULL,
`c2_time` datetime NOT NULL,
PRIMARY KEY (`ID`)
);INSERT INTO `stock_detail` (`ID`, `stock_no`, `adress`, `arr_time`, `c_type`, `pu_no`, `cust_no`, `mount`, `snp`, `c1_time`, `c2_time`) VALUES
(3, 'SU13061301011', 'A1', '2013-06-19 09:00:00', 'B12D(2)', 'K25B423', '237033AW0A', 13, 15, '2013-06-15 15:00:00', '2013-06-15 15:30:00'),
(4, 'SU13061301011', 'A1', '2013-06-19 09:00:00', 'B12D(2)', 'K25B424', '237033AW0A', 13, 15, '2013-06-15 15:00:00', '2013-06-15 15:30:00'),
(11, 'SU13061301015', 'B', '2013-06-19 19:00:00', 'D511', 'K25I101', '23703EX80A', 12, 15, '2013-06-15 15:00:00', '2013-06-15 15:30:00');
--
-- 表的结构 `vp_barcode_2d_y`
--
CREATE TABLE IF NOT EXISTS `vp_barcode_2d_y` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(25) NOT NULL,
`packageno` varchar(20) NOT NULL,
`hi_no` varchar(20) NOT NULL,
`cust_no` varchar(20) NOT NULL,
`lotno` varchar(20) NOT NULL,
`description` varchar(20) NOT NULL,
`type` varchar(20) NOT NULL,
`barcode` varchar(50) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`ID`),
KEY `packageno` (`packageno`),
KEY `lotno` (`lotno`)
);
//由于太大,只给出了packageno为1,2,3时的数据
INSERT INTO `vp_barcode_2d_y` (`packageno`, `cust_no`, `lotno`) VALUES
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219001', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219002', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207'),
('VP3.0 130219003', '23703 3AW0A', '3207');
SU13061301011|3207*195
SU13061301011|3207*195
假设后面对应的是SU13061301012,对应结果:
SU13061301012|3207*160(/*已知入库总量540-之前用掉的390(195+195),且SU13061301012对应的总量大于160时*/)注:其中st表中的stock_no字段对应的cust_no和数量和(字段mount*字段snp),再通过st表的cust_no和kw_manage、vp表联合查询的cust_no的结果来匹配数据。