Magento2常用SQL语句

### 根据sku获取spu
SELECT 
*
FROM catalog_product_entity
WHERE entity_id = (
SELECT parent_id
FROM `catalog_product_relation`
WHERE child_id = (
SELECT entity_id
FROM catalog_product_entity
WHERE sku = '70022169'
)
);


### 根据spu获取sku
SELECT sku FROM catalog_product_entity
WHERE entity_id IN (
SELECT child_id
FROM `catalog_product_relation`
WHERE parent_id = (
SELECT entity_id
FROM catalog_product_entity
WHERE sku = 'spu00168'
)
);


### 查看sku状态
select
entity_id as pid,
store_id,
case `value`
when 1 then '启用'
else '禁用'
end as `value`
from catalog_product_entity_int
where attribute_id = 97 and store_id = 0
group by 1,2;

### 已启用，但未设置分组价的SKU
SELECT *
FROM catalog_product_entity a LEFT JOIN catalog_product_entity_int b ON a.`entity_id` = b.`entity_id`
WHERE a.type_id = 'simple' 
AND b.attribute_id = 97 AND b.`store_id` = 0 AND b.`value` = 1
AND a.entity_id NOT IN (
SELECT DISTINCT(entity_id)
FROM `catalog_product_entity_tier_price`
)


### 查看spu对应的sku的状态
SELECT
*
FROM catalog_product_entity_int
WHERE attribute_id = 97 AND entity_id IN (
SELECT child_id
FROM `catalog_product_relation`
WHERE parent_id = (
SELECT entity_id
FROM catalog_product_entity
WHERE sku = 'spu00168'
)
);


### 获取库存信息
select 
a.entity_id,a.sku,b.qty,c.`value` as usa_qty
from catalog_product_entity a left join cataloginventory_stock_item b on a.entity_id = b.product_id
left join catalog_product_entity_varchar c on a.entity_id = c.entity_id
where a.entity_id in (
308,
309,
1089,
1159
)
and c.attribute_id = 409 and c.store_id = 1;


### 查订单中库存不足的sku
### 225654是未完成订单的ID
SELECT t1.*,t2.`qty` 
FROM 
(
SELECT product_id,sku,qty
FROM `cataloginventory_stock_item` a LEFT JOIN catalog_product_entity b ON a.`product_id` = b.`entity_id`
WHERE product_id IN (
SELECT entity_id
FROM catalog_product_entity
WHERE sku IN (
SELECT
sku
FROM `cancle_order_item`
WHERE order_id = 225654
)
)
)AS t1 LEFT JOIN `cancle_order_item` t2 ON t1.sku = t2.sku
WHERE t2.order_id = 225654;


### gallery
SELECT *
FROM `catalog_product_entity_media_gallery_value` a LEFT JOIN `catalog_product_entity_media_gallery` b ON a.`value_id` = b.`value_id`
WHERE b.`value` = '/q/0/q0003497_20_.jpg'

导出产品相册中图片和视频的链接：

select 
entity_id,
sku,
src 'Image Src',
position 'Image Postion',
media_type
from 
(

SELECT 
a.entity_id,
sku,
CONCAT('图片链接前缀', d.`value`) as 'src',
c.position as 'position',
'image' as 'media_type'
FROM catalog_product_entity a LEFT JOIN catalog_product_entity_int b ON a.`entity_id` = b.`entity_id`
LEFT JOIN catalog_product_entity_media_gallery_value c ON a.`entity_id` = c.`entity_id`
LEFT JOIN catalog_product_entity_media_gallery d ON c.`value_id` = d.`value_id`
WHERE b.`attribute_id` = 97 AND b.`store_id` = 0 
# AND b.`value` = 1
AND c.`store_id` = 0 and c.disabled = 0 
and d.media_type = 'image'

union

SELECT 
a.entity_id,
sku,
d.`url` as 'src',
c.position as 'position',
'video' as 'media_type'

FROM catalog_product_entity a LEFT JOIN catalog_product_entity_int b ON a.`entity_id` = b.`entity_id`
LEFT JOIN catalog_product_entity_media_gallery_value c ON a.`entity_id` = c.`entity_id`
LEFT JOIN catalog_product_entity_media_gallery_value_video d ON c.`value_id` = d.`value_id`
WHERE b.`attribute_id` = 97 AND b.`store_id` = 0 
# AND b.`value` = 1
AND c.`store_id` = 0 and c.disabled = 0 
and d.store_id = 0

)t
order by entity_id asc, `position` asc