php - 将 2 个 select mysql base 合二为一

Question

我需要做类似的东西。系统必须在输入内并通过关键字搜索表 specification_group_description。如果我写 P，我有关于所有 P 的列表。那里有处理器如果我写O，我有处理器和主板的元素

处理器(specifcation_group_description)

----- 英特尔(规范说明)

----- AMD

主板

----- XXXX

----- 年年年

我的目标是在上面创建一个 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

table 下面

--
-- 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

Answer 1

找到

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