http://topic.csdn.net/u/20110711/15/0df8dc7a-a25e-4c8f-8136-b7ea5ad0dcf4.html
这个是我上次提出的问题 :最后chuanzhang5687 兄弟的帮忙 实现了。。比如表A
id shop date
1 ss 2007-11-1
2 bb 2007-12-1
3 bb 2007-1-1
4 ss 2007-9-1
5 aa 2007-9-1
6 ss 2007-9-1
7 aa 2007-9-1表 B
id shop message
1 aa sssss
2 bb sssss
3 ss sadas
但是 现在又有一个问题 当在B中添加一个条目4 cc dsadas我现在想用sql产生这样的一个表
id shop message count
1 ss sadas 3
2 aa sssss 2
3 bb sssss 2
4 cc dsadas 0
count 计数 shop在表A中出现了几次 然后按照count 降序排序
这个是我上次提出的问题 :最后chuanzhang5687 兄弟的帮忙 实现了。。比如表A
id shop date
1 ss 2007-11-1
2 bb 2007-12-1
3 bb 2007-1-1
4 ss 2007-9-1
5 aa 2007-9-1
6 ss 2007-9-1
7 aa 2007-9-1表 B
id shop message
1 aa sssss
2 bb sssss
3 ss sadas
但是 现在又有一个问题 当在B中添加一个条目4 cc dsadas我现在想用sql产生这样的一个表
id shop message count
1 ss sadas 3
2 aa sssss 2
3 bb sssss 2
4 cc dsadas 0
count 计数 shop在表A中出现了几次 然后按照count 降序排序
解决方案 »
- 使用mysql索引遇到奇怪问题
- 这个问题一般怎么解决呢?
- 咨询一下 mysql的myisam和innodb引擎如何处理多用户对某一数据的同时操作?
- mysql查询语句怎么用上正则表达式?
- 怎么让mysql同时支持繁体和简体字???????
- c语言中用C API连接mysql数据库的问题 急切期盼高手帮忙
- mysql的A字段设为int类型,可条件为A='adfs'时可得出所有为0的结果,为何?
- 这是mysql的bug吗?〉
- linux下mysql不启动
- MySQL表里有学年和学期两列,根据当前时间填写当前两列的值,用函数能实现吗?
- mysql登入时出错ERROR 2003:Can't connect to MySQL server on 'localhost'<10061>
- 调用MySql带有返回参数的存储过程,获取返回参数值?
from 表A a right join 表B b on a.id=b.id
group by b.id,b.shop,b.message
order by b.id,b.shop,js desc
Query OK, 0 rows affected (0.00 sec)[mysql root@test 02:19:09]>select @a:=@a+1 as id, b.shop,b.message,count(a.shop) `count` from b left join a on a.shop=b.shop group by b.shop,b.message order by count(a.shop) desc;
+------+------+---------+-------+
| id | shop | message | count |
+------+------+---------+-------+
| 1 | aa | sssss | 2 |
| 2 | bb | sssss | 2 |
| 3 | ss | sadas | 3 |
| 4 | cc | dsadas | 0 |
+------+------+---------+-------+
4 rows in set (0.00 sec)
from b left join a on b.shop=a.shop
group by b.id
from 表A a right join 表B b on a.shop=b.shop
group by b.id,b.shop,b.message
order by b.id,b.shop,js desc
from b left join a on b.shop=a.shop
group by b.id
order by 4 desc
DROP TABLE IF EXISTS test.tb_shop ;
CREATE TABLE IF NOT EXISTS test.tb_shop (
id INT UNSIGNED NOT NULL ,
shop VARCHAR(10) NOT NULL ,
PRIMARY KEY (id) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;INSERT INTO test.tb_shop (id , shop)
VALUES(1, 'ss'),(2, 'bb'),(3, 'bb'),(4, 'ss'),(5, 'aa'),(6, 'ss'),(7, 'aa');DROP TABLE IF EXISTS test.tb_message ;
CREATE TABLE IF NOT EXISTS test.tb_message (
id INT UNSIGNED NOT NULL ,
shop VARCHAR(10) NOT NULL ,
message VARCHAR(10) NOT NULL ,
PRIMARY KEY (id) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;INSERT INTO test.tb_message (id , shop, message)
VALUES(1, 'aa', 'sssss'),(2, 'bb', 'sssss'),(3, 'ss', 'sadas'),(4, 'cc', 'dsadas');SELECT m.id, m.shop, m.message, IFNULL(s.cnt, 0) AS cnt FROM tb_message m
LEFT JOIN (SELECT shop , COUNT(*) AS cnt FROM tb_shop GROUP BY shop) s ON m.shop = s.shop
ORDER BY cnt DESC
我刚刚试了大家的建议 but 我发现 带 where 就会错误 不晓得为啥。
就应该这样
id shop message count
1 ss sadas 2
2 aa sssss 2
3 bb sssss 1
4 cc dsadas 0
from b left join (select shop,count(*) as cnt from a where date<='2007-9-1' group by shop) c on b.shop=c.shop
order by c.cnt desc如果你在顶楼就说明你需要加WHERE, 那别人会给你的语句是完全不同写法的。提问时最好花点时间把问题讲清楚。
from b a left join (select shop,count(*) as js from a where `date`<='2007-9-1' group by shop) b
on a.shop=b.shop
order by b.id,b.shop,js desc