如何在mysql中通过比较和空检查指定order by？

h9vpoimq 于 2021-08-01 发布在 Java

关注(0)|答案(2)|浏览(390)

我有两个表feed_old（默认数据）和feed_new（新数据），cron作业将每天运行，并且只使用当前信息更新feed_new表
定时任务

$url = "localhost/test.xml";
$xml = simplexml_load_file($url);

$this->db->query("TRUNCATE TABLE feed_new");

$date = date('Y-m-d H:i:s');

foreach($xml->Product as $item)
{
  $data = array(
    'product_code'    => $item->ProductCode,
    'name'            => $item->Name,
    'price'           => $item->RetailCurrentPrice,
    'stock'           => (int)$item->Stock,
    'manufacture'           => '1',
    'date_updated'      => $date
  );

  $update_status = $this->model_price->check_price($data);
}

模型

public function check_price($data)
{
  if($data) {
    $insert = $this->db->insert('feed_new', $data);
    return ($insert == true) ? true : false;
  }
}

因为这里一切正常
当我比较feed\u new和feed\u old时，问题来了，得到了什么变化，并显示了所有记录
在比较feed\u new和feed\u old之后，我想从两个表中提取所有记录，并按级别对它们进行排序
第1级-如果产品有不同的价格feed\u new.price<>feed\u old.price
2级-如果feed\u old的产品在feed\u new中不存在（这意味着不再支持产品）
第3级-如果feed\u new的产品在feed\u old中不存在（这意味着产品是新的）
4级-剩余结果
比较查询

SELECT fn.name AS name_new, fo.date_updated, fo.id, fo.name,fo.price,fo.product_code, fn.product_code AS product_code_new, fo.stock, fn.price AS price_new, fn.stock AS stock_new, fn.date_updated AS date_updated_new 
FROM feed_old fo 
LEFT JOIN feed_new fn ON fo.product_code = fn.product_code 

UNION ALL 

SELECT fn.name AS name_new, fo.date_updated, fo.id, fo.name,fo.price,fo.product_code, fn.product_code AS product_code_new, fo.stock, fn.price AS price_new, fn.stock AS stock_new, fn.date_updated AS date_updated_new 
FROM feed_old fo 
RIGHT JOIN feed_new fn ON fn.product_code = fo.product_code 

WHERE fo.product_code IS NULL OR fn.name IS NULL ORDER BY COALESCE(price <> price_new, name_new is NULL, product_code IS NULL) DESC

问题是level-3总是显示在最后一条记录上，我的意思是在我下面的工作示例中level-4之后，您可以看到name\u new->test3位于表的底部，此时应该位于第3位
我怎样才能按上面的级别订购
工作示例

sql mysql coalesce select sql-order-by

来源：https://stackoverflow.com/questions/62672205/how-to-specify-an-order-by-from-comparisons-and-null-checks-in-mysql

2条答案

按热度按时间

ih99xse11#

COALESCE 似乎不是最适合这种排序的函数，因为它只返回第一个非- NULL 值，这在比较操作数可能是 NULL .
建议使用 CASE 而是使用一个虚拟整数值作为级别-类似于：

ORDER BY CASE WHEN price_new IS NOT NULL AND price_new <> price THEN 1
              WHEN price_new IS NULL THEN 2
              WHEN price IS NULL THEN 3
              ELSE 4
         END

请看这个sqlfiddle演示：http://sqlfiddle.com/#!9/57c8eb3/6。

赞(0）回复(0）举报 2021-08-01

rhfm7lfc2#

可以使用ifnull、case-when和union编写更好的解决方案。
解决方案-http://sqlfiddle.com/#!9/57c8eb3/21号

SELECT fn.name AS name_new, fo.date_updated, fo.id, fo.name,fo.price,
fo.product_code, fn.product_code AS product_code_new, fo.stock, fn.price AS price_new, 
fn.stock AS stock_new, fn.date_updated AS date_updated_new,
(case when (IFNULL(fn.product_code,'X') != 'X' and fo.price != fn.price) 
then 'Level1' 
when IFNULL(fn.product_code,'X') = 'X' then 'Level2'
else 'Level4' end) as Ordering_level
FROM feed_old fo LEFT JOIN feed_new fn 
ON fo.product_code = fn.product_code 
UNION
SELECT fn.name AS name_new, fo.date_updated, fo.id, fo.name,fo.price,
fo.product_code, fn.product_code AS product_code_new, fo.stock, 
fn.price AS price_new, fn.stock AS stock_new, fn.date_updated AS date_updated_new,
(case when (IFNULL(fo.product_code,'X') != 'X' and fo.price != fn.price) 
then 'Level1'  
when IFNULL(fo.product_code,'X') = 'X' then 'Level3'
else 'Level4' end) as Ordering_level
FROM feed_old fo RIGHT JOIN feed_new fn 
ON fn.product_code = fo.product_code 
order by ordering_level

赞(0）回复(0）举报 2021-08-01

我来回答

如何在mysql中通过比较和空检查指定order by？

2条答案

相关问题

热门标签

最新问答