genderbee's questions

使用 PostgreSQL 9.3.24

问题

只是简单的操作，但我不知道如何做最简单的方法。

available_count只有当 sum of s for same product_idis 0and onwayis 0or时，我才能显示结果NULL？即130479在这种情况下。我想为每个显示所有值store_id。

谢谢你。

更新

我尝试在 WHERE 中不允许使用聚合函数。

select
*

from
j_product_store_availability psa 

where
sum(available_count) = '0'
and onway = '0'

group by
product_id

order by
product_id

输入。

product_id | store_id | available count | onway 
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0
3          | 1        | 0               | 0
3          | 2        | 0               | 0
3          | 3        | 1               | 0
4          | 1        | 0               | 0
4          | 2        | 0               | 0
4          | 3        | 0               | 1

预期输出。

product_id | store_id | available count | onway 
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0

系统

PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg19.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit

问题

max需要从表中返回一个值，product_id基于available_count.

Sql

select
    product_id, store_id, max(available_count)
from
    j_product_store_availability
group by
    product_id,
    store_id,
    available_count
order by
    product_id,
    available_count desc,
    store_id
--limit 1

结果

product_id store_id available_count

130475  4   7
130475  1   4
130475  8   4
130475  2   3
130475  5   3
130475  7   3
130475  10  3
130475  3   2
130475  6   2
130475  9   0
130479  1   0
130479  2   0
130479  3   0
...

它为每个返回行store_id。只需要返回一个基于 的最大值available_count。

预期结果

130475  4   7
130479  1   0
(next product_id)
...

谢谢。

解决方案（劳伦兹解决方案）

SELECT DISTINCT ON (product_id)
product_id,
store_id,
max(available_count)
from
j_product_store_availability
--where store_id = '7'
group by
product_id,
store_id,
available_count
ORDER by
product_id,
available_count desc;

系统

PostgreSQL 11.5 (Ubuntu 11.5-0ubuntu0.19.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit

案子

select在case条件下工作吗？我需要从 4 个州返回 1 个州case。我试图解释。

Shop 有 eshop ( store_id = 7) 和 stores ( store_id = 1 - 10, others, but not 7)。

Eshop 和每个商店都有available_count- 库存。

逻辑是：

当available_count> 1 where store_id= 7 (eshop) then in stock.

当available_count= 0 where store_id= 7 (eshop) and available_count> 1 where store_id!= 7 （对于所有商店，至少一个值大于 1，因为 eshop 为 0）然后in stock on store.

当available_count<= 1 where store_id= 7 (eshop) 或available_count<= 1 where store_id!= 7 （至少一个值为 1，其他值为 0，但如果全部为 0，则not in stock）则on request.

当count_availability= 0 where store_id= 7 (eshop) and count_availability= 0 where store_id!= 7 (all values are 0) then not in stock.

我试过了

case
when (select ja.available_count where ja.store_id = '7') > 1 then 'IN STOCK'
when (select ja.available_count where ja.store_id = '7') = 0 and (select ja.available_count where ja.store_id != '7') > 1 then 'IN STOCK ON STORE'
when (select ja.available_count where ja.store_id = '7') <= 1 or (select ja.available_count where ja.store_id != '7') <= 1 then 'ON REQUEST'
when (select ja.available_count where ja.store_id = '7') = 0 and (select ja.available_count where ja.store_id != '7') = 0 then 'NOT IN STOCK'
end as "availability",

它返回多个值availability，仅返回 'IN STOCK' 或 '[NULL]'，有时似乎正确值存在问题。

有更好的方法吗？

感谢您的任何帮助。

我在 Ubuntu 上使用 PostgreSQL 11.5

问题

我有带有连接表和where条件的 sql，看起来像这样。

...

where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431',
'318417',

...

当我运行 sql.

copy(
select distinct on ("HOBIS")
-- s_product.product_id,
product_shop_id as "HOBIS",
product_name as "NAME",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019') as "URL",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "IMG URL",
price_tax as "PRICE VAT",
case when price_rec > price_tax then concat(price_rec) end as "PRICE ORIGINAL"
from
s_product
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431'
)
and image_order = '0'
and s_pricelist_generated_lists.group_id = '1'

--order by "HOBIS"
) to stdout (format csv, quote '"');

结果按 排序s_product.product_shop_id。

...

230041,"Interiérové dveře Klasik plné dub archico, 3D povrch - 60 cm L",https://eshop.unihobby.cz/stavba-dvere-interierove-dvere-dvere-klasik-plne-dub-archico-3d-povrch/136280p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/klasik-plne-dub-archico.jpg,990.00,1390.00
237002,Vinylová podlaha Kronostep - True Grit,https://eshop.unihobby.cz/stavba-podlahy-vinylove-podlahy-podlaha-vinylova-kronostep-true-grit-5-0mm/151328p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/237002-krono-original-krono-xonic-r018-true-grit-3.jpg.big.jpg,1058.00,1323.00
318417,Primalex Malvena na fasády 1 l,https://eshop.unihobby.cz/bydleni-barvy-a-laky-venkovni-barvy-primalex-malvena-na-fasady-1l/153358p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/318417-malvena-5l.jpg,199.00,
464431,Benzínová řetězová pila CSH46 Scheppach,https://eshop.unihobby.cz/zahrada-zahradni-technika-retezove-pily-benzinove-retezove-pily-pila-retezova-benzinova-csh46-scheppach/150162p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/464431-csh46-scheppach-diy-garten-titel-neu-na.jpg,3290.00,

...

但我希望有结果，因为它们处于where状态。有可能有结果where吗？sort当我的 sql中没有条件时，为什么要对结果进行 PG 排序？

谢谢。

版本： PostgreSQL 11.4（Ubuntu 11.4-1.pgdg19.04+1）。

问题：可以导出到 json 文件吗？类似 csv 导出的东西。

COPY ( SQL )
TO stdout DELIMITER '"' csv;

谢谢。

更新：或者我可以为字段添加转义字符\吗？param_namevalue

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",

结果是：

{name:"připojení",value:"1/2""},

我需要这个（\之前"只用于字段param_name和value：

{name:"připojení",value:"1/2\""},

Update2：添加了整个 sql 命令。如何实现功能row_to_json？只需添加row_to_json到我拥有的选择中的每个项目？例如：

select row_to_json(string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params"),

...etc...

整个 sql。

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' > ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
s_product.product_id as "itemID",
s_product.product_id as "itemGroupID",
product_shop_id as "productCode",
product_ean as "ean",
product_name as "title",
product_short_label as "description",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/') as "link",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "image",
price_tax as "price",
price_rec as "priceOriginal",
available_count as "available",
case
when available_count >= 5 then 'SKLADEM > 5 KS'
when available_count < 5 then 'SKLADEM < 5 KS'
when available_couNt = 0 then 'NENÍ SKLADEM'
end as "availability",
producer_name as "brand",
concat('Úvod > ',cte.category_name) as "category",
s_category.category_id as "categoryID",
cte.category_ids as "hierarchy",
string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",
case when price_tax = price_rec then '' else 'Akce' end as "label"

from
s_product
left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join s_category on s_product.category_id = s_category.category_id
left join cte on s_product.category_id = cte.category_id
left join s_cf_j_product_value as a on s_product.product_id = a.product_id
left join s_cf_value as v on a.value_id = v.value_id
left join s_cf_param as p on v.param_id = p.param_id

where
image_order = '0'
and s_category.category_name is not null
and product_active = 'y'
and s_pricelist_generated_lists.group_id = '10'
--and s_product.product_id = '133471'

group by
s_product.product_id,
s_product_image.filename,
s_category.category_id,
s_pricelist_generated_lists.price_tax,
s_pricelist_generated_lists.price_rec,
s_producer.producer_name,
cte.category_name,
cte.category_ids
;