MySQL筛选数据?

Question

这个怎么筛选出有值的数据，（，petroNum，cnoocNum，sinopecNum任意一个有值）的前七条？

```sql

SELECT * from 
(

SELECT
    np.id,
    ROUND( AVG( CASE WHEN nc.enterprise_nature = '172' THEN np.price END ), 2 ) AS petroNum,
    ROUND( AVG( CASE WHEN nc.enterprise_nature = '173' THEN np.price END ), 2 ) AS cnoocNum,
    ROUND( AVG( CASE WHEN nc.enterprise_nature = '175' THEN np.price END ), 2 ) AS sinopecNum,
    DATE_FORMAT( np.date, '%Y-%m-%d' ) AS created
FROM
news_price np -- 价格数据表
RIGHT JOIN news_company nc ON nc.id = np.company_id  -- 企业信息表
WHERE
np.del_flag = 0 
AND np.approval_progress = 2 -- 已通过
AND np.product_id = 2 -- 石油焦 
--   AND np.date >= CURDATE() - INTERVAL 8 DAY  -- 近九天
--    AND WEEKDAY( np.date ) NOT IN ( 5, 6 ) -- 不含星期六，星期天
GROUP BY
DATE_FORMAT(np.date,'%Y-%m-%d')  ORDER BY created desc

)m 

```

Answer 1

该回答引用自GPT-3.5,由博主GISer Liu编写：

根据你的需求，你想要筛选出具有至少一个有值的字段（petroNum、cnoocNum、sinopecNum）的前七条数据。
你可以在你的SQL查询中添加一个外层查询，在外层查询中使用条件语句来筛选出至少一个字段有值的数据，并且再使用LIMIT子句来限制结果为前七条。
下面是你的SQL查询进行修改后的解决方案：
sqlCopy code

SELECT * FROM (
SELECT 
np.id,
ROUND( AVG( CASE WHEN nc.enterprise_nature = '172' THEN np.price END ), 2 ) AS petroNum,
ROUND( AVG( CASE WHEN nc.enterprise_nature = '173' THEN np.price END ), 2 ) AS cnoocNum,
ROUND( AVG( CASE WHEN nc.enterprise_nature = '175' THEN np.price END ), 2 ) AS sinopecNum,
DATE_FORMAT( np.date, '%Y-%m-%d' ) AS created
FROM 
news_price np
RIGHT JOIN news_company nc ON nc.id = np.company_id
WHERE 
np.del_flag = 0
AND np.approval_progress = 2
AND np.product_id = 2
AND np.date >= CURDATE() - INTERVAL 8 DAY
AND WEEKDAY( np.date ) NOT IN ( 5, 6 )
GROUP BY 
DATE_FORMAT(np.date,'%Y-%m-%d')
ORDER BY 
created DESC
) m
WHERE 
petroNum IS NOT NULL OR cnoocNum IS NOT NULL OR sinopecNum IS NOT NULL
LIMIT 7;

在这个解决方案中，我添加了一个外层查询，并在外层查询中使用了条件语句来筛选出至少一个字段有值的数据（使用OR逻辑操作符），然后再使用LIMIT子句来限制结果为前七条数据。

如果该回答解决了您的问题，请采纳！如果没有，请参考以下方案进行修订