Raport z bazy MsSQL z danymi ułożonymi wg id

0

Witajcie,
Mam bazę gdzie dla każego ID przypisane są pewne parametry


id | parametr | wartość
1 | nazwa | lalala
1 | waga | 10
1 | cena | 15
1 | kolor | niebieski
1 |rok | 2018
2 | nazwa | tytyty
2 | wysokość | 15
2 | cena | 8
2 | kolor | niebieski
2 | data przeglądu | 2015-01-12

itd. itp. parametrów w bród i dla jednego id występują, ale dla innego już nie muszą.
Jak z tak ułożonych danych wyraportować - pokaż mi wszystkie produkty (numery id, zakładamy, że id to oddzielny produkt o możliwej nieskończonej ilości parametrów), gdzie cena = 10 AND kolor = 'niebieski'

0
select id from tabela where (parametr = 'cena' and wartosc = 10) or (parametr = 'cena' and kolor = 'niebieski') group by id having count(id) = 2
0

Osobiście preferuję taki zapis:

SELECT id FROM tabela WHERE (parametr, wartosc ) IN (('cena', '10'), ('kolor', 'niebieski')) GROUP BY id HAVING Count(DISTINCT parametr)=1
0

I jeszcze jedna propozycja:

select a.id from tabela a where a.parametr='kolor' and a.value='niebieski' and exists (select 1 from tabela b where b.id=a.id and b.parametr='cena' and b.wartosc='10');

albo (doczytałem, że to MSSQL a nie MySQL, więc intersect powinno być):

select id from tabela where parametr='kolor' and wartosc='niebieski'
intersect 
select id from tabela where parametr='cena' and wartosc='10'
0

taka uwaga na koniec odnośnie mojego i @Marcin.Miga kodu - jeśli będziesz miał zdublowane pary parametr-wartość dla danego id po których szukasz to dostaniesz błędne wyniki

0

Nie wiem czy Cie zrozumialem, ale chcesz transponowac wiersze w kolumny? Jesli tak poczytaj o operacji GROUP_CONCAT, ihmo wybieranie kazdej kolumny w podzapytaniu to nie jest najwydajniesze:

https://dba.stackexchange.com/questions/47902/how-to-transpose-convert-rows-as-columns-in-mysql

SELECT * FROM tabela WHERE id IN (SELECT t2.id FROM tabela AS t2 where t2.parametr='cena' and t2.wartosc > 10)
0

Nienawidzę modelu EAV ;)
Jak muszę to posiłkuje się w MSSQL typem xml, wtedy jest łatwiej:

--dane
select 1 id,
convert(xml,N'<root>
    <nazwa>lalala</nazwa>
    <waga>10</waga>
    <cena>15</cena>
    <kolor>niebieski</kolor>
    <rok>2018</rok>
</root>') atr
into a

insert into a
select 1 id,
convert(xml,N'<root>
    <nazwa>lalala</nazwa>
    <waga>10</waga>
    <cena>1</cena>
    <kolor>niebieski</kolor>
    <rok>2018</rok>
</root>') atr

I proste zapytanie:


select * 
from a
where 
    atr.value('(//root/waga)[1]','integer')=10
    and atr.value('(//root/cena)[1]','integer')=15

wtedy nawet jak pytasz o nieistniejące atrybuty to nie musisz sprawdzać czy są, bo takie coś nie zwróci błędu:


select * 
from a
where 
    atr.value('(//root/nieistniejącyatrybut)[1]','integer')=10
0

To jeszcze dodam, że xmla można wykorzystać w twoim przypadku:

with dane as (
select
   id
   ,(select 
		cast('<' + lower(replace(parametr,' ','_')) + '>' + wartosc + '</'+lower(replace(parametr,' ','_')) + '>' as xml)
	from a x 
	where x.id=b.id
	for xml path(''), root('root'), type) atr
from 
	(select distinct id from a) as b
)

SELECT * 
FROM dane
WHERE 
    atr.value('(//root/waga)[1]','integer')=10
    AND atr.value('(//root/kolor)[1]','varchar(10)')='niebieski'

Wtedy też jest łatwiej w przypadku np. takim: znajdż te z kolorem niebieskim i zdefiniowaną datą przeglądu:

SELECT * 
FROM dane
WHERE 
    atr.exist('(//root/data_przeglądu)')=1
    AND atr.value('(//root/kolor)[1]','varchar(10)')='niebieski'

2

@abrakadaber: Nie badałem ;) Ale też nie odczułem zwolnień z tego powodu. Możliwe, że budowanie zapytań byłoby szybsze, ale ja cenię sobie wygodę...

W każdym razie piszę o sytuacji gdzie jest to zrobione zgodnie ze sztuką, a to wymaga troszkę wiedzy jak działa typ XML w MS SQL,

Więc od początku, nie wystarczy stworzyć tabeli z kolumną XML i zapomnieć o sprawie, bo to proszenie się o kłopoty ;)

Tworzymy xsd i dodajemy do bazy (CREATE XML SCHEMA COLLECTION), następnie kolumna w bazie jest typed, to w głownej mierze sprawia że xml nie jest traktowany jako ciąg znaków, a (w uproszczeniu) przyspiesza wyszukiwanie, ponieważ znane są typy danych elementów / atrybutów itd. To co prawda wpływa na insert ale wspiera select.

Dodatkowo tworzymy index na kolumnie xml, sam MS SQL trzyma xml-a jako BLOB co wpływa na to że podczas wykonywania zapytania czytana jest cała zawartość., Założenie indexu powoduje, że do tego bloba tworzone są wiersze w indexie zawierające informacje o ścieżce, typie, nazwie wartości itd. co znacząco przyspiesza zapytanie i widzimy oczekiwany index seek

To oczywiście w dużym skrócie i uproszczeniu.

1 użytkowników online, w tym zalogowanych: 0, gości: 1