GP15SET

GP15SET

关注
发信
关注(28)粉丝(399)

mysql - “选择总和”在一张表中不起作用

我有两个表：

*-- Struttura della tabella `confmiele`
--
CREATE TABLE IF NOT EXISTS `confmiele` (
  `idconf` int(11) NOT NULL,
  `dataconfmiele` date NOT NULL,
  `codconfmiele` varchar(10) NOT NULL,
  `nomeconfmiele` varchar(20) NOT NULL,
  `boxconfmiele` varchar(20) NOT NULL,
  `pesoconfmiele` int(7) NOT NULL,
  `numconfmiele` int(7) NOT NULL,
  `totconfmiele` int(7) NOT NULL,
  `noteconfmiele` varchar(300) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
--
-- Dump dei dati per la tabella `confmiele`
--
INSERT INTO `confmiele` (`idconf`, `dataconfmiele`, `codconfmiele`, `nomeconfmiele`, `boxconfmiele`, `pesoconfmiele`, `numconfmiele`, `totconfmiele`, `noteconfmiele`) VALUES
(2, '2015-09-02', 'GPO15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, ''),
(4, '2015-09-02', 'GPO15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, ''),
(5, '2015-09-02', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 10, 10000, ''),
(6, '2015-09-03', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, ''),
(7, '2015-10-07', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 10, 10000, ''),
(8, '2015-10-07', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 10, 10000, ''),
(9, '2015-10-08', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, ''),
(10, '2015-10-08', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, ''),
(11, '2015-10-08', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, ''),
(12, '2015-10-08', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, ''),
(13, '2015-10-08', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, ''),
(14, '2015-10-08', 'GP15SET', 'GIPPOMIELE', 'VASO VETRO 1000', 1000, 1, 1000, '');
--
-- Indici per le tabelle scaricate
--
--
-- Indici per le tabelle `confmiele`
--
ALTER TABLE `confmiele`
  ADD PRIMARY KEY (`idconf`);
--
-- AUTO_INCREMENT per le tabelle scaricate
--
--
-- AUTO_INCREMENT per la tabella `confmiele`
--
ALTER TABLE `confmiele`
  MODIFY `idconf` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=15;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;*


select sum在此表中起作用：

*SELECT SUM(numconfmiele) FROM confmiele WHERE codconfmiele = 'GP15SET' AND boxconfmiele = 'VASO VETRO 1000'*


但是，在第二个表上：

--
-- Struttura della tabella `dettconffattmiele`
--

CREATE TABLE IF NOT EXISTS `dettconffattmiele` (
  `iddettconffatt` int(11) NOT NULL,
  `dataconffattmiele` date NOT NULL,
  `idfatt` int(11) NOT NULL,
  `codconffattmiele` varchar(10) NOT NULL,
  `nomeconffattmiele` varchar(20) NOT NULL,
  `boxfattmiele` varchar(20) NOT NULL,
  `numconffattmiele` int(7) NOT NULL,
  `totpesoconffattmiele` int(7) NOT NULL,
  `uniteurofattmiele` decimal(7,2) NOT NULL,
  `eurofattmiele` decimal(7,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

--
-- Dump dei dati per la tabella `dettconffattmiele`
--

INSERT INTO `dettconffattmiele` (`iddettconffatt`, `dataconffattmiele`, `idfatt`, `codconffattmiele`, `nomeconffattmiele`, `boxfattmiele`, `numconffattmiele`, `totpesoconffattmiele`, `uniteurofattmiele`, `eurofattmiele`) VALUES
(1, '2015-09-02', 10, ' GP15SET', ' GIPPOMIELE', 'VASO VETRO 1000', 5, 5000, '10.00', '50.00'),
(2, '2015-09-02', 11, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 5, 5000, '10.00', '50.00'),
(3, '2015-09-02', 12, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 5, 5000, '10.00', '50.00'),
(6, '2015-09-03', 19, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(7, '2015-09-03', 20, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(8, '2015-09-03', 21, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(9, '2015-09-03', 22, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(10, '2015-09-03', 23, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(11, '2015-09-03', 24, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(12, '2015-09-03', 25, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(13, '2015-09-03', 26, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(14, '2015-09-03', 27, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(15, '2015-09-03', 28, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(16, '2015-09-03', 29, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(17, '2015-09-03', 30, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(18, '2015-09-03', 31, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(19, '2015-09-03', 32, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(20, '2015-09-03', 33, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(21, '2015-09-03', 34, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(22, '2015-09-03', 35, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00'),
(23, '2015-09-03', 36, ' GP15SET', ' GIPPOMIELE', ' VASO VETRO 1000', 1, 1000, '1.00', '1.00');

--
-- Indici per le tabelle scaricate
--

--
-- Indici per le tabelle `dettconffattmiele`
--
ALTER TABLE `dettconffattmiele`
  ADD PRIMARY KEY (`iddettconffatt`);

--
-- AUTO_INCREMENT per le tabelle scaricate
--

--
-- AUTO_INCREMENT per la tabella `dettconffattmiele`
--
ALTER TABLE `dettconffattmiele`
  MODIFY `iddettconffatt` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=24;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


相同的select sum不起作用：

SELECT SUM(numconffattmiele) FROM dettconffattmiele WHERE codconffattmiele = 'GP15SET' AND  boxfattmiele =  'VASO VETRO 1000'


我不太了解为什么？
代码相同，并且我在一台服务器上工作。

服务器：通过UNIX套接字的Localhost
Tipo di服务器：MySQL
版本服务器：5.5.31-log-源分发
版本协议：10
Utente：root @ localhost
Codifica caratteri del服务器：UTF-8 Unicode（utf8）

Apache / 2.2.12（Linux / SUSE）
Versione del Client del数据库：libmysql-mysqlnd 5.0.8-dev-20102224-$ Id：65fe78e70ce53d27a6cd578597722950e490b0d0 $
Estensione PHP：mysqli Documentazione
PHP版本：5.3.17
phpMyAdmin

信息版本：4.4.4

最佳答案

您的数据中有, ' GP15SET',空格。

但是在您的查询WHERE codconffattmiele = 'GP15SET' ...

但是'GP15SET'不等于'GP15SET'

http://sqlfiddle.com/#!9/7bd3a5/3

我想您对'VASO VETRO 1000'和' VASO VETRO 1000'的期望相同

08-25 01:15
Powered by LMLPHP ©2024 GP15SET 0.030400