mysql从一个表中选择

pkwftd7m  于 2021-06-21  发布在  Mysql
关注(0)|答案(3)|浏览(478)

我的表结构是

id;product_id;sell_type;sell_state

sell_type: BUY, SELL

sell_state: OPEN, FILLED, CANCELED

如何只选择 product_id 每两次操作, BUY & SELLsell_type 以及 FILLEDsell_state ```
CREATE TABLE orderlist (
id int(10) UNSIGNED NOT NULL,
product_id int(11) NOT NULL,
sell_type enum('BUY','SELL') DEFAULT NULL,
sell_state enum('OPEN','FILLED','CANCELED') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO orderlist (id, product_id, sell_type, sell_state) VALUES
(7, 1, 'BUY', 'FILLED'),
(8, 1, 'SELL', 'FILLED'),
(9, 2, 'BUY', 'FILLED'),
(10, 3, 'SELL', 'FILLED');

dy1byipe

dy1byipe1#

试试这个

SELECT product_id
FROM orderlist
WHERE sell_state = 'FILLED' AND sell_type IN ('BUY','SELL')
GROUP BY product_id
HAVING COUNT(product_id) > 2

演示:http://sqlfiddle.com/#!9/464959/2

8ulbf1ek

8ulbf1ek2#

尝试此选择查询

SELECT t1.* 
FROM temp t1
INNER JOIN temp t2
ON t1.product_id = t2.product_id AND FIND_IN_SET('FILLED',t1.sell_state) > 0
WHERE FIND_IN_SET('BUY',t1.sell_type) > 0 AND
CONCAT(",", t2.sell_type, ",") REGEXP ",(BUY|SELL),"
GROUP BY t1.product_id

演示:http://sqlfiddle.com/#!9月7日FCD9月64日

xcitsw88

xcitsw883#

您可以使用groupby和count(distinct sell\u state)=2

select product_id 
from orderlist  
where sell_state ='FILLED'
and sell_type in ('BUY', 'SELL')
group by product_id 
having count(distinct sell_type) = 2

http://sqlfiddle.com/#!9/c41301/2号

相关问题