Как сделать 4 выборки из 2-х таблиц с сортировкой по 1 ключу?

Рейтинг: 0Ответов: 1Опубликовано: 07.03.2015

Есть вот такой запрос: 

SELECT * FROM 
(SELECT `date`, COUNT(`hard`) AS `count` FROM `statistic` GROUP BY `date`) T1,
(SELECT `date`, COUNT(DISTINCT`hard`) AS `uniq` FROM `statistic` GROUP BY `date`) T2
WHERE T2.date = T1.date

А нужно сделать еще вот такой запрос во вторую таблицу: 

SELECT * FROM 
(SELECT `date`, COUNT(`ip`) AS `countip` FROM `download` GROUP BY `date`) T1,
(SELECT `date`, COUNT(DISTINCT`ip`) AS `uniqip` FROM `download` GROUP BY `date`) T2
WHERE T2.date = T1.date

чтобы на выходе я получил, например:

Array ( [0] => stdClass Object ( [date] => 2015-03-05 [count] => 5 [uniq] => 4 [countip] => 6 [uniqip] => 5) )
sql mysql
Источник: Stack Overflow на русском

Ответы

▲ 1

Не это?

SELECT * FROM 
(SELECT `date`, COUNT(`hard`) AS `count` FROM `statistic` GROUP BY `date`) T1,
(SELECT `date`, COUNT(DISTINCT`hard`) AS `uniq` FROM `statistic` GROUP BY `date`) T2,
(SELECT `date`, COUNT(`ip`) AS `countip` FROM `download` GROUP BY `date`) T3,
(SELECT `date`, COUNT(DISTINCT`ip`) AS `uniqip` FROM `download` GROUP BY `date`) T4
WHERE T2.date = T1.date and T3.date = T1.date and T4.date = T1.date;

Если я правильно понял замысел, то проще написать так:

SELECT * FROM 
(SELECT `date`, COUNT(`hard`) AS `count`, COUNT(DISTINCT`hard`) AS `uniq` FROM `statistic` GROUP BY `date`) T1,
(SELECT `date`, COUNT(`ip`) AS `countip`, COUNT(DISTINCT`ip`) AS `uniqip` FROM `download` GROUP BY `date`) T2,
WHERE T2.date = T1.date;