select * from a where (price >160 and unit = '年') or (price <110 and unit = '月')

我觉得这个问题，文心一言绰绰有余，都不用通义灵码就能搞定~楼上就是答案

select a.m_id from ((select m_id from table_name where price > 160 and unit = "年") as a  INNER JOIN (select m_id from table_name where price < 110 and unit = "月") b on a.m_id = b.m_id)

如果在表里面unit相同且m_id相同的超过两条，最后需要加上group by

select a.m_id from ((select m_id from table_name where price > 160 and unit = "年") as a  INNER JOIN (select m_id from table_name where price < 110 and unit = "月") b on a.m_id = b.m_id) group by a.m_id

其实大家可能会有疑问,为什么我不把 price 分为 month_price 和 year_price,这样就简单了,但有原因的,这个单位是自定义的,它可以有10天,10周等任意单位,图上示例只写了月和年

SELECT a.* FROM table1 a INNER JOIN table2 b ON a.id = b.id WHERE a.price >160 and a.unit = '年' and b.price <110 and b.unit = '月';

一个查询，但是join的缺点都知道吧

就我觉得你的做法才是正确的，简单的查询，结果集取交集即可。能不join就不join。

DROP TABLE `test`;
CREATE TABLE `test` (
  `id` bigint(5) NOT NULL AUTO_INCREMENT,
  `m_id` bigint(20) DEFAULT NULL,
  `price` tinyint(3) unsigned DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

INSERT INTO test (`m_id`, `price`, `unit`) VALUES (28, 100, '月');
INSERT INTO test (`m_id`, `price`, `unit`) VALUES (28, 200, '年');
INSERT INTO test (`m_id`, `price`, `unit`) VALUES (29, 120, '月');
INSERT INTO test (`m_id`, `price`, `unit`) VALUES (29, 150, '年');
INSERT INTO test (`m_id`, `price`, `unit`) VALUES (30, 180, '年');
INSERT INTO test (`m_id`, `price`, `unit`) VALUES (30, 130, '月');

SELECT
    testA.m_id 
FROM
    test testA 
WHERE
    EXISTS ( SELECT testB.m_id FROM test testB WHERE testB.price > 160 AND testB.unit = '年' AND testA.m_id = testB.m_id ) 
    AND testA.price < 110 
    AND testA.unit = '月';

select distinct mid from table where mid in (select mid from table where 条件1) and 条件2;

这样可以不

SELECT m_id
FROM table_name
WHERE (price > 160 AND unit = '年')
   OR (price < 110 AND unit = '月')
GROUP BY m_id
HAVING count(*) > 1;

也可以试试这种方式，看看是不是更容易理解。

with a as (SELECT m_id FROM test WHERE price > 160 AND unit = '年' group by m_id),
     b as (SELECT m_id FROM test WHERE price < 110 AND unit = '月' group by m_id)
select a.m_id
from a
         left join b on a.m_id = b.m_id
where b.m_id is not null

with a as (SELECT m_id FROM test WHERE price > 160 AND unit = '年' group by m_id),
     b as (SELECT m_id FROM test WHERE price < 110 AND unit = '月' group by m_id)
select a.m_id
from a
    inner join b on a.m_id = b.m_id;

-- 假设表名是 table_name

select
    unit_year.mid
from
    table_name unit_year
    inner join table_name unit_month on unit_year.mid = unit_month.mid
where
    unit_year.unit = "year"
    and unit_year.price > 160
    and unit_month.unit = "month"
    and unit_month.price < 110
group by
    unit_year.mid