mysql从一个表中选择

pkwftd7m 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(483)

我的表结构是

id;product_id;sell_type;sell_state

sell_type: BUY, SELL

sell_state: OPEN, FILLED, CANCELED

如何只选择 product_id 每两次操作， BUY & SELL 在 sell_type 以及 FILLED 在 sell_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');

mysql

来源：https://stackoverflow.com/questions/49947415/mysql-select-from-one-table

3条答案

按热度按时间

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

赞(0）回复(0）举报 2021-06-21

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日

赞(0）回复(0）举报 2021-06-21

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号

赞(0）回复(0）举报 2021-06-21

我来回答

mysql从一个表中选择

3条答案

相关问题

热门标签

最新问答