primer:
data:
Code:
CREATE TABLE `a` (
`a_id` int(11) DEFAULT NULL,
`a_name` varchar(100) DEFAULT NULL
);
CREATE TABLE `p` (
`a_id` int(11) DEFAULT NULL,
`d` date DEFAULT NULL,
`k` int(11) DEFAULT NULL
) ;
INSERT INTO `a` VALUES (1,'kukuruz'),(2,'psenica'),(3,'govedo'),(4,'monitor'),(5,'procesor'),(6,'krompir');
INSERT INTO `p` VALUES (1,'2010-01-01',1),(1,'2010-01-01',1),(1,'2010-01-01',1),(1,'2010-01-01',1),
(1,'2010-01-01',2),(1,'2010-01-03',2),(1,'2010-01-03',2),(1,'2010-01-03',2),(1,'2010-01-04',2),(1,'2010-01-04',2),
(1,'2010-01-04',2),(1,'2010-01-04',2),(1,'2010-01-05',2),(1,'2010-01-05',2),(1,'2010-01-05',2),(1,'2010-01-05',2),
(1,'2010-01-10',2),(1,'2010-01-10',2),(1,'2010-01-10',2),(1,'2010-01-10',2),(2,'2010-01-10',2),(2,'2010-01-10',2),
(2,'2010-01-10',2),(2,'2010-01-10',2),(2,'2010-01-12',2),(2,'2010-01-12',2),(2,'2010-01-12',2),(2,'2010-01-12',2),
(2,'2010-01-01',2),(2,'2010-01-01',2),(2,'2010-01-01',2),(2,'2010-01-01',2),(3,'2010-01-15',2),(3,'2010-01-15',2),
(3,'2010-01-15',2),(3,'2010-01-15',2),(4,'2010-01-15',2),(4,'2010-01-15',2),(4,'2010-01-15',2),(4,'2010-01-15',2),
(4,'2010-01-16',2),(4,'2010-01-16',2),(4,'2010-01-16',2),(4,'2010-01-16',2),(5,'2010-01-16',2),(5,'2010-01-16',2),
(5,'2010-01-16',2),(5,'2010-01-16',2),(5,'2010-01-26',2),(5,'2010-01-26',2),(5,'2010-01-26',2),(6,'2010-01-26',2),
(6,'2010-01-26',2),(6,'2010-01-26',2),(6,'2010-01-26',2),(6,'2010-01-26',2),(6,'2010-01-22',2),(6,'2010-01-26',2),
(6,'2010-01-26',2),(6,'2010-01-26',2),(6,'2010-01-26',2),(6,'2010-01-16',2),(6,'2010-01-16',2),(6,'2010-01-16',2),
(6,'2010-01-16',2),(6,'2010-01-16',2),(6,'2010-01-06',2),(6,'2010-01-06',2),(6,'2010-01-06',2),(6,'2010-01-06',2),
(6,'2010-01-06',2);
upit:
Code:
mysql> select
-> x.a_name,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=1 and p.a_id=x.a_id) as d1,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=2 and p.a_id=x.a_id) as d2,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=3 and p.a_id=x.a_id) as d3,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=4 and p.a_id=x.a_id) as d4,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=5 and p.a_id=x.a_id) as d5,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=6 and p.a_id=x.a_id) as d6,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=7 and p.a_id=x.a_id) as d7,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=8 and p.a_id=x.a_id) as d8,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=9 and p.a_id=x.a_id) as d9,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=10 and p.a_id=x.a_id) as d10,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=11 and p.a_id=x.a_id) as d11,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=12 and p.a_id=x.a_id) as d12,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=13 and p.a_id=x.a_id) as d13,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=14 and p.a_id=x.a_id) as d14,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=15 and p.a_id=x.a_id) as d15,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=16 and p.a_id=x.a_id) as d16,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=17 and p.a_id=x.a_id) as d17,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=18 and p.a_id=x.a_id) as d18,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=19 and p.a_id=x.a_id) as d19,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=20 and p.a_id=x.a_id) as d20,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=21 and p.a_id=x.a_id) as d21,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=22 and p.a_id=x.a_id) as d22,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=23 and p.a_id=x.a_id) as d23,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=24 and p.a_id=x.a_id) as d24,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=25 and p.a_id=x.a_id) as d25,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=26 and p.a_id=x.a_id) as d26,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=27 and p.a_id=x.a_id) as d27,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=28 and p.a_id=x.a_id) as d28,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=29 and p.a_id=x.a_id) as d29,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=30 and p.a_id=x.a_id) as d30,
-> (select sum(p.k) from p where DAYOFMONTH(p.d)=31 and p.a_id=x.a_id) as d31
->
-> from a x group by x.a_id;
+----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| a_name | d1 | d2 | d3 | d4 | d5 | d6 | d7 | d8 | d9 | d10 | d11 | d12 | d13 | d14 | d15 | d16 | d17 | d18 | d19 | d20 | d21 | d22 | d23 | d24 | d25 | d26 | d27 | d28 | d29 | d30 | d31 |
+----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| kukuruz | 6 | NULL | 6 | 8 | 8 | NULL | NULL | NULL | NULL | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| psenica | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 8 | NULL | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| govedo | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| monitor | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 8 | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| procesor | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 6 | NULL | NULL | NULL | NULL | NULL |
| krompir | NULL | NULL | NULL | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | 2 | NULL | NULL | NULL | 18 | NULL | NULL | NULL | NULL | NULL |
+----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
6 rows in set (0.01 sec)
mysql>
kao sto rekoh, uzasno
ono sto jos mozes je da imas neku tabelu u kojoj imas 1-31 pa da je pomnozis .. ili ... sve zavisi sta tacno hoces ..