多选同一张表

马努

我有这张桌子:

CREATE TABLE `test1` (
  `id` int(4) NOT NULL,
  `company_Id_Company` varchar(10) NOT NULL,
  `head_Id_Head` int(4) NOT NULL,
  `detail_Id_Detail` int(4) NOT NULL,
  PRIMARY KEY (`id`,`company_Id_Company`,`head_Id_Head`),
  KEY `fk_test1_detail` (`id`,`head_Id_Head`,`detail_Id_Detail`),
  CONSTRAINT `fk_test1_detail` FOREIGN KEY (`id`, `head_Id_Head`, `detail_Id_Detail`) REFERENCES `detail` (`id`, `head_Id_Head`, `id_Detail`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_test1_company` FOREIGN KEY (`id`, `company_Id_Company`) REFERENCES `company` (`id`, `id_Company`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB

我在表中有此数据:

 id | company_Id_Company | head_Id_Head | detail_Id_Detail
-----------------------------------------------------------
 1            01                1                7
 1            02                1                7
 1            01                3                2
 1            03                2                9
 1            05                4                1
 1            01                5                4
 1            02                5                4
 1            03                5                4

我需要同时找到满足两个强制条件的所有数据:

select * from test1 where head_Id_Head = 1 and detail_Id_Detail = 7
                 AND
select * from test1 where head_Id_Head = 5 and detail_Id_Detail = 4

如果两个查询都在运行,我应该是compañoa:01和02

我该如何查询?

杰米D77

这将假设您没有重复。

SELECT  *
FROM    test1 t1
WHERE   [company_Id_Company] IN (SELECT [company_Id_Company]
                                 FROM   test1 t2
                                 WHERE  ((t2.head_Id_Head = 1
                                              AND t2.detail_Id_Detail = 7)
                                             OR (t2.head_Id_Head = 5
                                                 AND t2.detail_Id_Detail = 4))
                                 GROUP BY t2.[company_Id_Company]
                                 HAVING COUNT(*) = 2)

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章