我需要修改下面的sql，以便它检索在名为planogram的表中设置了“beer”类别的项的ttff数据。
select p.product_name, count(t.product_id), avg(t.TTFF), std(t.TTFF)
from product p left join
TTFFdata t
on p.product_id = t.product_id
where planogram_id = 1 group by p.product_name
但是产品没有类别数据，所以我需要从平面图表中的类别中获取。类似这样，但通过适当的连接使其工作：
select p.product_name, count(t.product_id), avg(t.TTFF), std(t.TTFF)
from product p left join
TTFFdata t
on p.product_id = t.product_id
where planogram.category = "beer" group by p.product_name
要考虑两件事：
我对sql不是很有经验。
我认为我得到的数据库结构不好（产品上没有类别标识？！），但我可能得按原样处理。
非常感谢您的帮助。
我使用的完整SQL如下：
-- phpMyAdmin SQL Dump
-- version 4.6.5.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Mar 08, 2017 at 07:50 PM
-- Server version: 10.1.21-MariaDB
-- PHP Version: 5.6.30
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 */;
--
-- Database: `chart`
--
-- --------------------------------------------------------
--
-- Table structure for table `planogram`
--
CREATE TABLE `planogram` (
`planogram_id` int(11) NOT NULL,
`planogram_name` varchar(200) NOT NULL,
`project_name` varchar(200) NOT NULL,
`category` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `planogram`
--
INSERT INTO `planogram` (`planogram_id`, `planogram_name`, `project_name`, `category`) VALUES
(1, 'planogramA', '', 'beer'),
(2, 'planogramB', '', 'coffee'),
(3, 'planogramC', '', 'coffee'),
(4, 'planogramD', '', 'fruit'),
(5, 'planogramE', '', 'beer');
-- --------------------------------------------------------
--
-- Table structure for table `product`
--
CREATE TABLE `product` (
`product_id` int(11) NOT NULL,
`product_name` varchar(200) NOT NULL,
`detail` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`product_id`, `product_name`, `detail`) VALUES
(1, 'beer1', ''),
(2, 'beer2', ''),
(3, 'coffee1', ''),
(4, 'coffee2', ''),
(5, 'coffee3', ''),
(6, 'coffee4', ''),
(7, 'coffee5', ''),
(8, 'coffee6', ''),
(9, 'beer3', ''),
(10, 'beer4', ''),
(13, 'fruit1', ''),
(14, 'fruit2', '');
-- --------------------------------------------------------
--
-- Table structure for table `TTFFdata`
--
CREATE TABLE `TTFFdata` (
`record_id` int(11) NOT NULL,
`participant_id` varchar(45) NOT NULL,
`TTFF` decimal(10,2) NOT NULL,
`product_id` int(11) NOT NULL,
`planogram_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `TTFFdata`
--
INSERT INTO `TTFFdata` (`record_id`, `participant_id`, `TTFF`, `product_id`, `planogram_id`) VALUES
(1, 'a1', '6.21', 1, 1),
(2, 'a2', '5.70', 1, 1),
(3, 'a3', '6.00', 1, 1),
(4, 'b1', '3.40', 2, 1),
(5, 'b2', '4.30', 2, 1),
(6, 'b3', '6.00', 2, 1),
(7, 'c1', '7.00', 3, 2),
(8, 'c2', '8.00', 3, 2),
(9, 'c3', '5.00', 3, 2),
(10, 'd1', '8.90', 4, 2),
(11, 'd2', '3.00', 4, 2),
(12, 'd3', '4.50', 4, 2),
(15, 'e1', '8.00', 5, 2),
(16, 'e2', '9.00', 5, 2),
(17, 'f1', '5.50', 6, 3),
(18, 'f2', '5.00', 6, 3),
(19, 'g1', '5.20', 7, 3),
(20, 'g2', '3.60', 7, 3),
(21, 'h1', '5.00', 8, 3),
(22, 'h2', '5.10', 8, 3),
(23, 'i1', '6.00', 13, 4),
(24, 'i2', '7.00', 13, 4),
(25, 'j1', '4.00', 14, 4),
(26, 'j2', '6.00', 14, 4),
(27, 'k1', '6.70', 9, 5),
(28, 'k2', '6.10', 9, 5),
(29, 'l1', '3.00', 10, 5),
(30, 'l2', '5.00', 10, 5);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `planogram`
--
ALTER TABLE `planogram`
ADD PRIMARY KEY (`planogram_id`);
--
-- Indexes for table `product`
--
ALTER TABLE `product`
ADD PRIMARY KEY (`product_id`);
--
-- Indexes for table `TTFFdata`
--
ALTER TABLE `TTFFdata`
ADD PRIMARY KEY (`record_id`),
ADD KEY `product_id` (`product_id`),
ADD KEY `planogram_id` (`planogram_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `planogram`
--
ALTER TABLE `planogram`
MODIFY `planogram_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;
--
-- AUTO_INCREMENT for table `TTFFdata`
--
ALTER TABLE `TTFFdata`
MODIFY `record_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;
/*!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 */;
最佳答案
您需要在联接中包含所有3个表，并确保对所有相关字段进行分组以获得正确的输出。根据样本数据，每个产品只与一个平面图相关联，因此我的答案是基于这样一个假设，即对于您的整个数据，这是正确的：
select p.product_name, count(t.product_id), avg(t.TTFF), std(t.TTFF)
from product p
left join TTFFdata t on p.product_id = t.product_id
left join planogram pl on t.planogram_id=pl.planogram_id
where planogram.category = "beer"
group by p.product_name
关于mysql - MySQL三路加盟？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42761336/