php - 结合2个选择mysql基础

我需要做类似的事情。该系统必须在输入内，并且必须在表specification_group_description上通过关键字搜索。
如果我写了P，我就会得到所有P的列表。
如果我写O，我有处理器和主板的元素

处理器（specifcation_group_description）

-----英特尔（specification_description）

----- AMD

祖母

----- XXXX

----- YYYY

我的目标是在上面创建一个sql，但是不知道我的方法是否很好。

我想我应该合并到数据库

第一

SELECT agd.name
FROM clic_specification_group_description agd,
 clic_specification_group sg
WHERE agd.specification_group_id = sg.specification_group_id
AND agd.language_id = 2

第二

select *
FROM clic_specification a,
     clic_specification_description ad
WHERE ad.language_id = 2
and a.specification_id =ad.specification_id
ORDER BY ad.name
limit 10

结果必须是（不起作用）

 select *,
(
    SELECT agd.name
    FROM clic_specification_group_description agd,
     clic_specification_group sg
    WHERE agd.specification_group_id = sg.specification_group_id
    AND agd.language_id = 2
)
 FROM clic_specification a,
      clic_specification_description ad
    WHERE ad.language_id = 2
    and a.specification_id =ad.specification_id
    AND ad.name like"%$keywords%"
    ORDER BY ad.name
    limit 10

桌子下面

--
-- Structure de la table `clic_specification`
--

CREATE TABLE `clic_specification` (
  `specification_id` int(11) NOT NULL,
  `specification_group_id` int(11) NOT NULL,
  `sort_order` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Contenu de la table `clic_specification`
--

INSERT INTO `clic_specification` (`specification_id`, `specification_group_id`, `sort_order`) VALUES
(1, 6, 1),
(2, 6, 1),
(3, 6, 3),
(5, 3, 2),
(11, 3, 8),
(12, 5, 0);

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

--
-- Structure de la table `clic_specification_description`
--

CREATE TABLE `clic_specification_description` (
  `specification_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Contenu de la table `clic_specification_description`
--

INSERT INTO `clic_specification_description` (`specification_id`, `language_id`, `name`) VALUES
(5, 1, 'test 2'),
(3, 1, 'Clockspeed'),
(2, 1, 'No. of Cores'),
(1, 1, 'Description'),
(11, 1, 'test 8'),
(12, 2, 'Description'),
(2, 2, 'Nbr. de Cores'),
(3, 2, 'fréquence'),
(5, 2, 'testfr 2'),
(11, 2, 'testfr 8');

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

--
-- Structure de la table `clic_specification_group`
--

CREATE TABLE `clic_specification_group` (
  `specification_group_id` int(11) NOT NULL,
  `sort_order` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Contenu de la table `clic_specification_group`
--

INSERT INTO `clic_specification_group` (`specification_group_id`, `sort_order`) VALUES
(3, 2),
(4, 1),
(5, 3),
(6, 4);

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

--
-- Structure de la table `clic_specification_group_description`
--

CREATE TABLE `clic_specification_group_description` (
  `specification_group_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Contenu de la table `clic_specification_group_description`
--

INSERT INTO `clic_specification_group_description` (`specification_group_id`, `language_id`, `name`) VALUES
(6, 1, 'Processor'),
(5, 1, 'Motherboard'),
(4, 1, 'Technical'),
(3, 1, 'Video'),
(3, 2, 'Vidéo'),
(4, 2, 'Technique'),
(5, 2, 'Carte mère'),
(6, 2, 'Processeur');

--
-- Index pour les tables exportées
--

--
-- Index pour la table `clic_specification`
--
ALTER TABLE `clic_specification`
  ADD PRIMARY KEY (`specification_id`);

--
-- Index pour la table `clic_specification_description`
--
ALTER TABLE `clic_specification_description`
  ADD PRIMARY KEY (`specification_id`,`language_id`);

--
-- Index pour la table `clic_specification_group`
--
ALTER TABLE `clic_specification_group`
  ADD PRIMARY KEY (`specification_group_id`);

--
-- Index pour la table `clic_specification_group_description`
--
ALTER TABLE `clic_specification_group_description`
  ADD PRIMARY KEY (`specification_group_id`,`language_id`);

--
-- AUTO_INCREMENT pour les tables exportées
--

--
-- AUTO_INCREMENT pour la table `clic_specification`
--
ALTER TABLE `clic_specification`
  MODIFY `specification_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
--
-- AUTO_INCREMENT pour la table `clic_specification_group`
--
ALTER TABLE `clic_specification_group`
  MODIFY `specification_group_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

第一个结果，但不仅显示所有语言

SELECT DISTINCT
    s.specification_id AS id,
    sd.name AS name,
    sgd.name AS group_name
FROM  clic_specification s
LEFT JOIN clic_specification_group sg ON ( s.specification_group_id = sg.specification_group_id )

left join clic_specification_group_description sgd on (sg.specification_group_id = sgd.specification_group_id) ,
    clic_specification_description sd
WHERE
    sd.name like '%f%'
    AND s.specification_id = sd.specification_id
    AND sg.specification_group_id = sgd.specification_group_id
    AND sd.language_id = 2
LIMIT 10

最佳答案

找到了

SELECT DISTINCT
    s.specification_id AS id,
    sd.name AS name,
    sgd.name AS group_name
FROM  clic_specification s
LEFT JOIN clic_specification_group sg ON ( s.specification_group_id = sg.specification_group_id )

left join clic_specification_group_description sgd on (sg.specification_group_id = sgd.specification_group_id) ,
    clic_specification_description sd
WHERE
    sd.name like '%f%'
    AND s.specification_id = sd.specification_id
    AND sg.specification_group_id = sgd.specification_group_id
    AND sd.language_id = 2
    AND sgd.language_id = 2
LIMIT 10

关于php - 结合2个选择mysql基础，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49518482/