我有两个表:
表1
id | field1 |id_order |
----------------------------------
1 | value1 | null |
2 | value2 | null |
3 | value3 | 999 |
4 | value4 | 888 |
5 | value5 | null |
6 | value6 | 777 |
和表2
id |id_order |name | total
-------------------------------------------
1 | 999 |name1 |100 |
2 | 999 |name2 |300 |
3 | 777 |name3 |700 |
4 | 888 |name4 |400 |
5 | 888 |name5 |500 |
6 | 888 |name6 |200 |
结果必须是
id | field1 |total_count | last_value
---------------------------------------------------------
1 | value1 |null | null |
2 | value2 |null | null |
3 | value3 |2 | 300 |
4 | value4 |3 | 200 |
5 | value5 |null | null |
6 | value6 |1 | 700 |
我想得到计数和最后一个值。这是我的代码,但运行缓慢:
SELECT *,
(select count(*) from table2 where table2.id_order=table1.id_order and table1.id_order > 0 group by id_order ) as total_count,
(select total from table2 where table2.id_order=table1.id_order and table1.id_order>0 order by id DESC limit 1) as last_value
FROM table1
考虑以下。。。
DROP TABLE IF EXISTS table1;
CREATE TABLE table1
(id SERIAL PRIMARY KEY
,field1 VARCHAR(12) NOT NULL
,id_order INT NULL
);
INSERT INTO table1 VALUES
(1,'value1',null),
(2,'value2',null),
(3,'value3',999),
(4,'value4',888),
(5,'value5',null),
(6,'value6',777);
DROP TABLE IF EXISTS table2;
CREATE TABLE table2
(id SERIAL PRIMARY KEY
,id_order INT NOT NULL
,name VARCHAR(12) NOT NULL
,total INT NOT NULL
);
INSERT INTO table2 VALUES
(1,999,'name1',100),
(2,999,'name2',300),
(3,777,'name3',700),
(4,888,'name4',400),
(5,888,'name5',500),
(6,888,'name6',200);
SELECT x.id
, x.field1
, y.total_count
, y.total
FROM table1 x
LEFT
JOIN
( SELECT a.*
, b.total_count
FROM table2 a
JOIN
( SELECT MAX(id) id
, COUNT(*) total_count
FROM table2
GROUP
BY id_order
) b
ON b.id = a.id
) y
ON y.id_order = x.id_order;
+----+--------+-------------+-------+
| id | field1 | total_count | total |
+----+--------+-------------+-------+
| 1 | value1 | NULL | NULL |
| 2 | value2 | NULL | NULL |
| 3 | value3 | 2 | 300 |
| 4 | value4 | 3 | 200 |
| 5 | value5 | NULL | NULL |
| 6 | value6 | 1 | 700 |
+----+--------+-------------+-------+
6 rows in set (0.00 sec)