mysql 根据条件计算子表行数
我有两个表:“用户”-> "order"
TABLE: user
user_id
-----------
u1
u2
TABLE: order
order_id | user_id | flag
-------------------------
o1 | u1 | fA
o2 | u2 | fB
Y需要获取所有用户统计有多少次带有'fA'标志的订单< /strong>
我需要的结果:
user_id | orders
----------------
u1 | 1
u2 | 0
我尝试:
SELECT
u.user_id,
COUNT(o.order_id) AS orders
FROM
`user` AS u LEFT JOIN
`order` AS o USING (user_id)
WHERE
o.flag IS NULL OR
o.flag IN ('fA')
GROUP BY
u.user_id;
但是,此查询不包括 user=u2,因为他没有带有标记 fA; 我需要 user=u2 与 orders=0 一起出现,
也许是这样的:
SELECT
u.user_id,
COUNT(o.order_id IF o.flag IN('fA')) OR 0 AS count ...
表格和数据:
CREATE TABLE `user` (user_id VARCHAR(2) NULL);
CREATE TABLE `order` (order_id VARCHAR(2) NULL,user_id VARCHAR(2) NULL,flag VARCHAR(2) NULL);
INSERT INTO `user` VALUES ('u1'), ('u2');
INSERT INTO `order` VALUES ('o1','u1','fA'),('o2','u2','fB');
I have two tables: "user" -> "order"
TABLE: user
user_id
-----------
u1
u2
TABLE: order
order_id | user_id | flag
-------------------------
o1 | u1 | fA
o2 | u2 | fB
Y need obtain all users counting how many times have orders with flag 'fA'
RESULTS WHAT I NEED:
user_id | orders
----------------
u1 | 1
u2 | 0
I TRY:
SELECT
u.user_id,
COUNT(o.order_id) AS orders
FROM
`user` AS u LEFT JOIN
`order` AS o USING (user_id)
WHERE
o.flag IS NULL OR
o.flag IN ('fA')
GROUP BY
u.user_id;
But, this query is excluding user=u2 because he don't have an order with flag fA;
I need the user=u2 appear with orders=0
Maybe something like that:
SELECT
u.user_id,
COUNT(o.order_id IF o.flag IN('fA')) OR 0 AS count ...
Tables and data:
CREATE TABLE `user` (user_id VARCHAR(2) NULL);
CREATE TABLE `order` (order_id VARCHAR(2) NULL,user_id VARCHAR(2) NULL,flag VARCHAR(2) NULL);
INSERT INTO `user` VALUES ('u1'), ('u2');
INSERT INTO `order` VALUES ('o1','u1','fA'),('o2','u2','fB');
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
您可以尝试在“LEFT JOIN”的“ON”语句中使用条件,如下所示:
运行SQLFiddle 中的示例
You can try using the condition in the 'ON' statement of the 'LEFT JOIN', like this:
Runing example in SQLFiddle
至少有三种方法可以做到这一点:
只需使用依赖子查询。
在子查询上使用
LEFT JOIN
:在组合条件上使用
LEFT JOIN
:我认为最快的是最后一个,但需要在足够大的数据上进行测试并查看执行计划。
SQL Fiddle
There are at least three ways to do it:
Just use dependent subquery.
Use
LEFT JOIN
on subquery:Use
LEFT JOIN
on combined condition:The fastest is last I think, but it need to be tested on large enough data and see execution plan.
SQL Fiddle
您需要使用左外连接而不是左连接
You need to use a left outer join instead of left join
您可以尝试使用 CASE 语句。 我今天没有 MYSQL,所以我可以验证语法,但它应该与此接近:
You can try using a CASE statement. I don't have MYSQL here today so I can validate the syntax, but it should be close to this:
该查询将起作用:
This query will work: