[GENERAL] Query Improvement??

Поиск
Список
Период
Сортировка
От Bret Stern
Тема [GENERAL] Query Improvement??
Дата
Msg-id 1509917974.3996.24.camel@bret.machinemanagement.com
обсуждение исходный текст
Список pgsql-general

Goal is to return all vendors which exist in all three companies

I think I got lucky figuring this out. Is there an obviously better way?

combined_item_master looks like this:
company_code character varying(10) NOT NULL,
primary_vendor_no character varying(7)
..more fields

data looks like this:

company_code | primary_vendor
AAA                                      003
BBB                                      004
CCC                                      001
CCC                                      004
AAA                                      123
BBB                                      123
CCC                                      123
BBB                                      003

Query returns all primary_vendor_no (as vendor_locations) which exist in all three companies
results:
vendor_locations
123


Here's the query

select primary_vendor_no, count(primary_vendor_no) as vendor_locations
from
(
SELECT distinct primary_vendor_no, company_code
  FROM combined_item_master
group by primary_vendor_no, company_code
) as a

group by primary_vendor_no
having count(primary_vendor_no)=3
order by vendor_locations DESC, primary_vendor_no


Thanks
Bret



В списке pgsql-general по дате отправления:

Предыдущее
От: Mark Fletcher
Дата:
Сообщение: Re: [GENERAL] pg_logical/snapshots directory
Следующее
От: tao tony
Дата:
Сообщение: Re: [GENERAL] checkpoint and recovering process use too much memory