phpmyadmin Mysql查询多个表和内部连接

wlwcrazw  于 4个月前  发布在  PHP
关注(0)|答案(2)|浏览(51)

我有3个表，我想有一个查询，给我的销售额，存款和它们之间的差异的总和，尝试这个，但记录重复。
这是我的疑问：

-- phpMyAdmin SQL Dump
-- version 4.9.11
-- https://www.phpmyadmin.net/
--
-- Servidor: localhost:3306
-- Tiempo de generación: 28-11-2023 a las 16:25:38
-- Versión del servidor: 5.6.41-84.1
-- Versión de PHP: 7.4.33

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Base de datos: `geasacpe_consolidado`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `articulo`
--

CREATE TABLE `articulo` (
  `id_articulo` int(11) NOT NULL,
  `nombre_articulo` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `precio` decimal(10,2) NOT NULL,
  `id_local` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `articulo`
--

INSERT INTO `articulo` (`id_articulo`, `nombre_articulo`, `precio`, `id_local`) VALUES
(1, 'PREMIUN AVENTURERO', '10.00', 1),
(2, 'REGULAR AVENTURERO', '8.00', 1),
(3, 'GLP AVENTURERO', '5.00', 1),
(4, 'PREMIUN EL FIEL', '12.00', 2),
(5, 'REGULAR EL FIEL', '9.00', 2),
(6, 'GLP EL FIEL', '4.00', 2);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `contometro`
--

CREATE TABLE `contometro` (
  `id_contometro` int(11) NOT NULL,
  `fecha_contometro` date NOT NULL,
  `turno_contometro` int(11) NOT NULL,
  `inicial_contometro` int(11) NOT NULL,
  `final_contometro` int(11) NOT NULL,
  `precio_articulo` decimal(10,3) NOT NULL,
  `id_local` int(11) NOT NULL,
  `id_manguera` int(11) NOT NULL,
  `id_punto` int(11) NOT NULL,
  `id_lado` int(11) NOT NULL,
  `id_articulo` int(11) NOT NULL,
  `id_vendedor` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `contometro`
--

INSERT INTO `contometro` (`id_contometro`, `fecha_contometro`, `turno_contometro`, `inicial_contometro`, `final_contometro`, `precio_articulo`, `id_local`, `id_manguera`, `id_punto`, `id_lado`, `id_articulo`, `id_vendedor`) VALUES
(3, '2023-11-28', 1, 0, 10, '10.000', 1, 1, 1, 1, 1, 1),
(4, '2023-11-28', 1, 0, 8, '8.000', 1, 2, 1, 1, 2, 1),
(5, '2023-11-28', 1, 0, 12, '5.000', 1, 3, 1, 1, 3, 1),
(6, '2023-11-28', 1, 0, 13, '10.000', 1, 4, 1, 2, 1, 1);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `depositoxvendedor`
--

CREATE TABLE `depositoxvendedor` (
  `id_depositoxvendedor` int(11) NOT NULL,
  `fecha_depositoxvendedor` date NOT NULL,
  `turno_depositoxvendedor` int(11) NOT NULL,
  `numero_depositoxvendedor` int(11) NOT NULL,
  `monto_depositoxvendedor` decimal(10,2) NOT NULL,
  `id_tipopago` int(11) NOT NULL,
  `id_vendedor` int(11) NOT NULL,
  `id_local` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `depositoxvendedor`
--

INSERT INTO `depositoxvendedor` (`id_depositoxvendedor`, `fecha_depositoxvendedor`, `turno_depositoxvendedor`, `numero_depositoxvendedor`, `monto_depositoxvendedor`, `id_tipopago`, `id_vendedor`, `id_local`) VALUES
(1, '2023-11-28', 1, 1, '100.00', 1, 1, 1),
(2, '2023-11-28', 1, 2, '150.00', 2, 1, 1),
(3, '2023-11-28', 1, 3, '50.00', 1, 2, 1),
(4, '2023-11-28', 1, 4, '30.00', 2, 2, 1);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `lado`
--

CREATE TABLE `lado` (
  `id_lado` int(11) NOT NULL,
  `numero_lado` int(11) NOT NULL,
  `id_punto` int(11) NOT NULL,
  `id_local` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `lado`
--

INSERT INTO `lado` (`id_lado`, `numero_lado`, `id_punto`, `id_local`) VALUES
(1, 1, 1, 1),
(2, 2, 1, 1),
(3, 3, 3, 1),
(4, 4, 3, 1),
(5, 1, 4, 2),
(6, 2, 4, 2),
(7, 3, 2, 2),
(8, 4, 2, 2);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `local`
--

CREATE TABLE `local` (
  `id_local` int(11) NOT NULL,
  `nombre_local` varchar(50) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `local`
--

INSERT INTO `local` (`id_local`, `nombre_local`) VALUES
(1, 'AVENTURERO'),
(2, 'EL FIEL INCOMPRENDIDO');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `manguera`
--

CREATE TABLE `manguera` (
  `id_manguera` int(11) NOT NULL,
  `numero_manguera` int(11) NOT NULL,
  `id_lado` int(11) NOT NULL,
  `id_articulo` int(11) NOT NULL,
  `id_local` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `manguera`
--

INSERT INTO `manguera` (`id_manguera`, `numero_manguera`, `id_lado`, `id_articulo`, `id_local`) VALUES
(1, 1, 1, 1, 1),
(2, 2, 1, 2, 1),
(3, 3, 1, 3, 1),
(4, 1, 2, 1, 1),
(5, 2, 2, 2, 1),
(6, 3, 2, 3, 1);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `punto`
--

CREATE TABLE `punto` (
  `id_punto` int(11) NOT NULL,
  `marca_punto` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `modelo_punto` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `id_local` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `punto`
--

INSERT INTO `punto` (`id_punto`, `marca_punto`, `modelo_punto`, `id_local`) VALUES
(1, 'GILBARCO AVENTURERO', '3000', 1),
(2, 'TOKEIN EL FIEL', '2000', 2),
(3, 'TOKEIN AVENTURERO', '2000', 1),
(4, 'GILBARCO EL FIEL', '3000', 2);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `puntoxvendedor`
--

CREATE TABLE `puntoxvendedor` (
  `id_puntoxvendedor` int(11) NOT NULL,
  `fecha_puntoxvendedor` date NOT NULL,
  `turno_puntoxvendedor` int(11) NOT NULL,
  `id_punto` int(11) NOT NULL,
  `id_vendedor` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `puntoxvendedor`
--

INSERT INTO `puntoxvendedor` (`id_puntoxvendedor`, `fecha_puntoxvendedor`, `turno_puntoxvendedor`, `id_punto`, `id_vendedor`) VALUES
(1, '2023-11-28', 1, 1, 1),
(2, '2023-11-28', 1, 3, 2);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `vendedor`
--

CREATE TABLE `vendedor` (
  `id_vendedor` int(11) NOT NULL,
  `nombre_vendedor` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `id_local` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Volcado de datos para la tabla `vendedor`
--

INSERT INTO `vendedor` (`id_vendedor`, `nombre_vendedor`, `id_local`) VALUES
(1, 'VENDEDOR 1 AVENTURERO', 1),
(2, 'VENDEDOR 2 AVENTURERO', 1),
(3, 'VENDEDOR 1 EL FIEL', 2),
(4, 'VENDEDOR 2 EL FIEL', 2);

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `articulo`
--
ALTER TABLE `articulo`
  ADD PRIMARY KEY (`id_articulo`),
  ADD KEY `id_local` (`id_local`);

--
-- Indices de la tabla `contometro`
--
ALTER TABLE `contometro`
  ADD PRIMARY KEY (`id_contometro`),
  ADD KEY `id_local` (`id_local`),
  ADD KEY `id_manguera` (`id_manguera`),
  ADD KEY `id_punto` (`id_punto`),
  ADD KEY `id_lado` (`id_lado`),
  ADD KEY `id_articulo` (`id_articulo`),
  ADD KEY `id_vendedor` (`id_vendedor`);

--
-- Indices de la tabla `depositoxvendedor`
--
ALTER TABLE `depositoxvendedor`
  ADD PRIMARY KEY (`id_depositoxvendedor`),
  ADD KEY `id_vendedor` (`id_vendedor`),
  ADD KEY `id_local` (`id_local`);

--
-- Indices de la tabla `lado`
--
ALTER TABLE `lado`
  ADD PRIMARY KEY (`id_lado`),
  ADD KEY `id_punto` (`id_punto`),
  ADD KEY `id_local` (`id_local`);

--
-- Indices de la tabla `local`
--
ALTER TABLE `local`
  ADD PRIMARY KEY (`id_local`);

--
-- Indices de la tabla `manguera`
--
ALTER TABLE `manguera`
  ADD PRIMARY KEY (`id_manguera`),
  ADD KEY `id_lado` (`id_lado`),
  ADD KEY `id_articulo` (`id_articulo`),
  ADD KEY `id_local` (`id_local`);

--
-- Indices de la tabla `punto`
--
ALTER TABLE `punto`
  ADD PRIMARY KEY (`id_punto`),
  ADD KEY `id_local` (`id_local`);

--
-- Indices de la tabla `puntoxvendedor`
--
ALTER TABLE `puntoxvendedor`
  ADD PRIMARY KEY (`id_puntoxvendedor`),
  ADD KEY `id_punto` (`id_punto`),
  ADD KEY `id_vendedor` (`id_vendedor`);

--
-- Indices de la tabla `vendedor`
--
ALTER TABLE `vendedor`
  ADD PRIMARY KEY (`id_vendedor`),
  ADD KEY `id_local` (`id_local`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `articulo`
--
ALTER TABLE `articulo`
  MODIFY `id_articulo` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT de la tabla `contometro`
--
ALTER TABLE `contometro`
  MODIFY `id_contometro` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT de la tabla `depositoxvendedor`
--
ALTER TABLE `depositoxvendedor`
  MODIFY `id_depositoxvendedor` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT de la tabla `lado`
--
ALTER TABLE `lado`
  MODIFY `id_lado` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT de la tabla `local`
--
ALTER TABLE `local`
  MODIFY `id_local` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT de la tabla `manguera`
--
ALTER TABLE `manguera`
  MODIFY `id_manguera` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT de la tabla `punto`
--
ALTER TABLE `punto`
  MODIFY `id_punto` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT de la tabla `puntoxvendedor`
--
ALTER TABLE `puntoxvendedor`
  MODIFY `id_puntoxvendedor` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT de la tabla `vendedor`
--
ALTER TABLE `vendedor`
  MODIFY `id_vendedor` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `articulo`
--
ALTER TABLE `articulo`
  ADD CONSTRAINT `articulo_ibfk_1` FOREIGN KEY (`id_local`) REFERENCES `local` (`id_local`);

--
-- Filtros para la tabla `contometro`
--
ALTER TABLE `contometro`
  ADD CONSTRAINT `contometro_ibfk_1` FOREIGN KEY (`id_local`) REFERENCES `local` (`id_local`),
  ADD CONSTRAINT `contometro_ibfk_2` FOREIGN KEY (`id_manguera`) REFERENCES `manguera` (`id_manguera`),
  ADD CONSTRAINT `contometro_ibfk_3` FOREIGN KEY (`id_punto`) REFERENCES `punto` (`id_punto`),
  ADD CONSTRAINT `contometro_ibfk_4` FOREIGN KEY (`id_lado`) REFERENCES `lado` (`id_lado`),
  ADD CONSTRAINT `contometro_ibfk_5` FOREIGN KEY (`id_articulo`) REFERENCES `articulo` (`id_articulo`),
  ADD CONSTRAINT `contometro_ibfk_6` FOREIGN KEY (`id_vendedor`) REFERENCES `vendedor` (`id_vendedor`);

--
-- Filtros para la tabla `depositoxvendedor`
--
ALTER TABLE `depositoxvendedor`
  ADD CONSTRAINT `depositoxvendedor_ibfk_1` FOREIGN KEY (`id_vendedor`) REFERENCES `vendedor` (`id_vendedor`),
  ADD CONSTRAINT `depositoxvendedor_ibfk_2` FOREIGN KEY (`id_local`) REFERENCES `local` (`id_local`);

--
-- Filtros para la tabla `lado`
--
ALTER TABLE `lado`
  ADD CONSTRAINT `lado_ibfk_1` FOREIGN KEY (`id_punto`) REFERENCES `punto` (`id_punto`),
  ADD CONSTRAINT `lado_ibfk_2` FOREIGN KEY (`id_local`) REFERENCES `local` (`id_local`);

--
-- Filtros para la tabla `manguera`
--
ALTER TABLE `manguera`
  ADD CONSTRAINT `manguera_ibfk_1` FOREIGN KEY (`id_lado`) REFERENCES `lado` (`id_lado`),
  ADD CONSTRAINT `manguera_ibfk_2` FOREIGN KEY (`id_articulo`) REFERENCES `articulo` (`id_articulo`),
  ADD CONSTRAINT `manguera_ibfk_3` FOREIGN KEY (`id_local`) REFERENCES `local` (`id_local`);

--
-- Filtros para la tabla `punto`
--
ALTER TABLE `punto`
  ADD CONSTRAINT `punto_ibfk_1` FOREIGN KEY (`id_local`) REFERENCES `local` (`id_local`);

--
-- Filtros para la tabla `puntoxvendedor`
--
ALTER TABLE `puntoxvendedor`
  ADD CONSTRAINT `puntoxvendedor_ibfk_1` FOREIGN KEY (`id_punto`) REFERENCES `punto` (`id_punto`),
  ADD CONSTRAINT `puntoxvendedor_ibfk_2` FOREIGN KEY (`id_vendedor`) REFERENCES `vendedor` (`id_vendedor`);

--
-- Filtros para la tabla `vendedor`
--
ALTER TABLE `vendedor`
  ADD CONSTRAINT `vendedor_ibfk_1` FOREIGN KEY (`id_local`) REFERENCES `local` (`id_local`);
COMMIT;

/*!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 */;

个字符
The query gives me this result
结果应该是：
| 日期|图尔诺|文代多尔|Deposito|轮廓测量|
| --|--|--|--|--|
| 2023年11月28日| 1 |供应商1 AVENTURERO|二百五十|354.00|

phpmyadmin

2条答案

按热度按时间
rmbxnbpk

rmbxnbpk1#

试试这个：

select  DV.fecha_depositoxvendedor as date,  turno, V.nombre_vendedor as vendedor,
sum(DV.monto_depositoxvendedor) as deposito,
any_value(C.contometria) as contometria -- The aggregation function any_value makes the syntax right but logically uncenessary, as the aggregation is already done in the derived table C. Use min or max if function any_value is not available.
from  
    (select id_vendedor , turno_contometro as turno, sum((final_contometro-inicial_contometro)*precio_articulo) as contometria
    from contometro 
    group by id_vendedor, turno) C
inner join vendedor V on C.id_vendedor=V.id_vendedor
inner join depositoxvendedor DV on DV.id_vendedor=V.id_vendedor
group by vendedor, date, turno  
;

-- result
+------------+-------+-----------------------+----------+-------------+
| date       | turno | vendedor              | deposito | contometria |
+------------+-------+-----------------------+----------+-------------+
| 2023-11-28 |     1 | VENDEDOR 1 AVENTURERO |   250.00 |     354.000 |
+------------+-------+-----------------------+----------+-------------+

字符串
请注意，答案是使用指定的SQL_MODE值ONLY_FULL_GROUP_BY编写的，该值不允许选择列表具有 * 非聚合列，这些列既不在GROUP BY子句中命名，也在功能上不依赖于（唯一确定的）GROUP BY列 *。当我们在SQL_MODE中有值时，如果我们在主查询结束时从group by列表中删除turno，则发生错误。

select  DV.fecha_depositoxvendedor as date,  turno, V.nombre_vendedor as vendedor,
sum(DV.monto_depositoxvendedor) as deposito,
any_value(C.contometria) as contometria -- The aggregation function any_value makes the syntax right but logically uncenessary, as the aggregation is already done in the derived table C. 
from  
    (select id_vendedor , turno_contometro as turno, sum((final_contometro-inicial_contometro)*precio_articulo) as contometria
    from contometro 
    group by id_vendedor, turno) C
inner join vendedor V on C.id_vendedor=V.id_vendedor
inner join depositoxvendedor DV on DV.id_vendedor=V.id_vendedor
group by vendedor, date
;

-- result:
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'C.turno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

型
如果我们删除ONLY_FULL_GROUP_BY，它将不会失败。
最后，这可能超出了我对业务逻辑的关注，但我认为仅使用id_vendedor的连接条件不太令人满意。我们是否应该考虑进一步的连接条件，例如在表contometro和depositoxvendedor之间匹配日期和turno？

赞(0）分享  回复(0）举报  4个月前
b1payxdu

b1payxdu2#

问题是所有的JOINing都发生在任何聚合之前）sic为SUM()）。这可能会得到正确的总和。（注意，它消除了对GROUP BY的需要。）

SELECT  V.nombre_vendedor as vendedor,
        ( SELECT sum(DV.monto_depositoxvendedor)
            FROM depositoxvendedor DV
            WHERE DV.id_vendedor=V.id_vendedor ) as deposito,
        ( SELECT  sum((C.final_contometro - C.inicial_contometro) * C.precio_articulo)
              ) as contometria
    FROM vendedor V

字符串
我不知道如何处理C.fecha_contometro和C.turno_contometro，因为每个供应商都有多个fecha，等等。

赞(0）分享  回复(0）举报  4个月前
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com