问题

你的要求是将以下英文文本翻译成中文：

"i have product table as

for each product, i have separate product type table, for Product_A_Type, i have type table as below

like wise i have Product_B_Type table

and having stock table as

i am doing filter based on product and type combination for eg:-

in my select query, i will search with product as "A" and type as %ab%, in this case i need to get results based on product type A only, if i search with product "B" and type as %12% then the query should return results for product "B" and it should ignore product A. i tried with below query, but it returns empty row..

select
*
from
daily_stock
where
(
product_id in (
select
id
from
products p
where
lower(p.name) like lower('%lubr%')
)
and product_type_id in (
select
id
from
lubricant_type l
where
lower(l.type) like lower('%%')
)
and product_type_id in (
select
id
from
lubricant_type l
where
lower(l.name) like lower('%%')
)
)
and (
product_id in (
select
id
from
products p
where
lower(p.name) like lower('%lubr%')
)
and product_type_id in (
select
id
from
fuel_type f
where
lower(f.type) like lower('%%')
)
and product_type_id in (
select
id
from
fuel_type f
where
lower(f.name) like lower('%%')
)
)"

以下是翻译后的文本：

我有一个产品表如下：

对于每个产品，我都有单独的产品类型表，例如Product_A_Type，我有以下类型表：

同样，我也有Product_B_Type表：

还有一个库存表如下：

我正在根据产品和类型的组合进行筛选，例如：

在我的选择查询中，我将搜索产品为"A"，类型为%ab%，在这种情况下，我只需要基于产品类型A获取结果，如果我搜索产品为"B"，类型为%12%，则查询应返回产品为"B"的结果，并忽略产品A。我尝试了以下查询，但它返回了空行。

select 
* 
from 
daily_stock 
where 
(
product_id in (
select 
id 
from 
products p 
where 
lower(p.name) like lower('％lubr％')
) 
and product_type_id in (
select 
id 
from 
lubricant_type l 
where 
lower(l.type) like lower('%%')
) 
and product_type_id in (
select 
id 
from 
lubricant_type l 
where 
lower(l.name) like lower('%%')
)
) 
and (
product_id in (
select 
id 
from 
products p 
where 
lower(p.name) like lower('％lubr％')
) 
and product_type_id in (
select 
id 
from 
fuel_type f 
where 
lower(f.type) like lower('%%')
) 
and product_type_id in (
select 
id 
from 
fuel_type f 
where 
lower(f.name) like lower('%%')
)
)

希望这有助于您理解。如果您有任何其他问题，请随时提出。

    select 
  * 
from 
  daily_stock 
where 
  (
    product_id in (
      select 
        id 
      from 
        products p 
      where 
        lower(p.name) like lower('%lubr%')
    ) 
    and product_type_id in (
      select 
        id 
      from 
        lubricant_type l 
      where 
        lower(l.type) like lower('%%')
    ) 
    and product_type_id in (
      select 
        id 
      from 
        lubricant_type l 
      where 
        lower(l.name) like lower('%%')
    )
  ) 
  and (
    product_id in (
      select 
        id 
      from 
        products p 
      where 
        lower(p.name) like lower('%lubr%')
    ) 
    and product_type_id in (
      select 
        id 
      from 
        fuel_type f 
      where 
        lower(f.type) like lower('%%')
    ) 
    and product_type_id in (
      select 
        id 
      from 
        fuel_type f 
      where 
        lower(f.name) like lower('%%')
    )
  )

答案1

得分: 1

以下是您要翻译的内容：

请尝试此查询：

select ds.id, ds.product_id, ds.product_type_id
from daily_stock ds
inner join product p on p.id = ds.product_id
inner join Product_A_Type pa on pa.id = ds.product_type_id
inner join Product_B_Type pb on pb.id = ds.product_type_id
where p.name = 'A' and ( pa.type like '%ab%' or pb.type like '%ab%' )

示例在此处

select ds.id, ds.product_id, ds.product_type_id
from daily_stock ds
inner join product p on p.id = ds.product_id
inner join Product_A_Type pa on pa.id = ds.product_type_id
inner join Product_B_Type pb on pb.id = ds.product_type_id
where p.name = 'A' and ( pa.type like '%ab%' or pb.type like '%ab%' )