Wie Reihe der Summe zurückzukehren () s

stimmen
0

Ich jetzt meine ursprüngliche Tabellenstruktur finden war nicht gut, so will es ändern. Aber ich habe eine harte Zeit Abfragen entwerfen Summen mit der neuen Struktur in den Zeilen zu erhalten.

current structure:
+----------+-------+-------+-------+-------+
|   state  | shop  | item0 | item1 | item2 | 
+----------+-------+-------+-------+-------+
|    5     |  0    |   1   |   2   |   3   |
|    5     |  1    |   1   |   2   |   3   |
|    5     |  2    |   1   |   2   |   3   |
|    4     |  3    |   1   |   2   |   3   |
+----------+-------+-------+-------+-------+
(quantities of items at shop)

Ich möchte diese zwei Tabellen ändern:

shops table
+---------+--------+
| shop_id | state  |
+---------+--------+
|    0    |    5   |
|    1    |    5   |
|    2    |    5   |
|    3    |    4   |
+---------+--------+

items table
+------------+--------------+
|   shop  | item | quantity | 
+------------+--------------+
|    0    |  0   |    1     |
|    0    |  1   |    2     |
|    0    |  2   |    3     |
|    1    |  0   |    1     |
|    1    |  1   |    2     |
|    1    |  2   |    3     |
|    2    |  0   |    1     |
|    2    |  1   |    2     |
|    2    |  2   |    3     |
|    3    |  0   |    1     |
|    3    |  1   |    2     |
|    3    |  2   |    3     |
+------------+--------------+

Das alte Layout erlaubt einfache Abfragen für Summen für Zeile bekommen:

SELECT state,SUM(item0) t0,SUM(item1) t1,SUM(item2) t2
FROM shops
WHERE state=5 

    +--------+---------+---------+----------+
    | state  |    t0   |    t1   |    t2    |
    +--------+---------+---------+----------+
    |    5   |    3    |     6   |    9     |
    +--------+---------+---------+----------+

With the new structure,
I can get the totals in column as follows:

SELECT item,SUM(quantity) total
FROM shops
LEFT JOIN items ON shop=shopid
WHERE state=5
GROUP by item
+--------+---------+
|  item  |  total  |
+--------+---------+
|    0   |    3    |
+--------+---------+
|    1   |    6    |
+--------+---------+
|    2   |    9    |
+--------+---------+

but how do I get the totals in rows:
+--------+---------+---------+----------+
| state  |    t0   |    t1   |     t2   |
+--------+---------+---------+----------+
|    4   |     1   |     2   |      3   |
|    5   |     3   |     6   |      9   |
+--------+---------+---------+----------+
Veröffentlicht am 10/02/2010 um 01:41
quelle vom benutzer
In anderen Sprachen...                            


1 antworten

stimmen
1

Sie könnten versuchen , ein paar mehr mit JOINs:

SELECT S.state, 
    SUM(T0.quantity) AS "T0",
    SUM(T1.quantity) AS "T1",
    SUM(T2.quantity) AS "T2"
FROM shops AS S
LEFT JOIN items AS T0 ON S.shop_id = T0.shop_id AND T0.item=0
LEFT JOIN items AS T1 ON S.shop_id = T1.shop_id AND T1.item=1
LEFT JOIN items AS T2 ON S.shop_id = T2.shop_id AND T2.item=2
GROUP BY S.state

Es könnte ein einfacherer Weg geben.

Beantwortet am 10/02/2010 um 07:59
quelle vom benutzer

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more