【高分悬赏】mysql两个查询结果根据相同的日期合并成一个表

求助大神，有两个查询语句，例如
查询一：select action_id,date_,count(dev_imei_hash) from test1.hql1 where action_id ='31025' group by date_
查询结果为
date_          action_id   count(dev_imei_hash)
20160221 31025 1500
20160222 31025 1500
20160223 31025 1441
20160224 31025 1359
20160225 31025 1350
20160226 31025 1359
20160227 31025 1332
第二个查询：select date_,count(dev_imei_hash) from test1.hql1 group by date_
查询结果为
date_        count(dev_imei_hash)
20160221 9980
20160222 9980
20160223 9591
20160224 8991
20160225 8991
20160226 8991
20160227 8946

现在想把两个查询结合起来，实现这样的查询结果，可以么？需要怎么弄呢？
date_          action_id   count(dev_imei_hash)  count(dev_imei_hash)
20160221 31025 1500               9980    
20160222 31025 1500               9980
20160223 31025 1441               9591
20160224 31025 1359               8991
20160225 31025 1350               8991
20160226 31025 1359               8991
20160227 31025 1332               8946

5 个解决方案

#1

select  a.* ,b.count_result
（select action_id,date_,count(dev_imei_hash) from test1.hql1 where action_id ='31025' group by date_）a ,
(select date_,count(dev_imei_hash) count_result from test1.hql1 group by date_ )  b
where a.date_=b.date_

#2

引用 1 楼 cc_pucca 的回复:

select  a.* ,b.count_result
（select action_id,date_,count(dev_imei_hash) from test1.hql1 where action_id ='31025' group by date_）a ,
(select date_,count(dev_imei_hash) count_result from test1.hql1 group by date_ )  b
where a.date_=b.date_

我也不知道为啥会有这样的错误，提示的就是unexpected  'count'  (count)

#3

试试这个：

SELECT  t1.* ,
        t2.count2
FROM    ( SELECT    action_id ,
                    date_ ,
                    COUNT(dev_imei_hash) AS count1
          FROM      test1.hql1
          WHERE     action_id = '31025'
          GROUP BY  date_
        ) AS t1
        JOIN ( SELECT   date_ ,
                        COUNT(dev_imei_hash) AS count2
               FROM     test1.hql1
               GROUP BY date_
             ) AS t2 ON t1.date_ = t2.date_

#4

fhmkh
结帖率： 0%
   

引用

当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html

8、如何给分和结贴？
http://bbs.csdn.net/help#post_dispose

#5

select t1.* ,t2.count(dev_imei_hash)  from
(select action_id,date_,count(dev_imei_hash) from test1.hql1 where action_id ='31025' group by date_)t1,
(select date_,count(dev_imei_hash) from test1.hql1 group by date_)t2
where t1.date_ = t2.date_;