$sql = "SELECT m.company,sum(o.money) FROM member AS m LEFT JOIN `order` AS o ON m.id=o.user_id WHERE o.status= 2 or o.status IS NULL GROUP BY m.id ORDER BY m.id DESC ";order表中没有订单,那么这个用户对应的order表中status就是NULL
to Meteorlet(Meteorlet):这么写我也想过,只不过我这个其实是三表联结,还需要连接另外一张表invoiceinvoice表是发票表,用来保存用户开发票的记录 订单是否需要开发票使用一个invoice的字段来表示,而status=2则表示订单已经处理完成。我的要求是列出每个用户需要开发票的订单金额总数及已经该用户已经开过的发票总数, 但用下面的SQL语句得不到正确结果, $sql = "SELECT m.id AS user_id,m.company, sum(o.money) AS total_bill, sum(i.bill_money) AS drawn_bill FROM `member` AS m LEFT JOIN `order` AS o ON m.id=o.user_id LEFT JOIN invoice AS i ON m.id=i.user_id WHERE o.invoice = 1 AND o.status=2 /* 这里是条件 GROUP BY m.id ORDER BY m.id DESC ";不知道三表联结可以这样写吗? 如果不可以想得到我想要的结果,能用一条SQL语句实现否?不知道我说明白了没有,
$sql = "SELECT m.company,sum(o.money) FROM member AS m LEFT JOIN `order` AS o ON m.id=o.user_id and o.status= 2 GROUP BY m.id ORDER BY m.id DESC ";
$sql = "SELECT m.id AS user_id,m.company, sum(o.money) AS total_bill, sum(i.bill_money) AS drawn_bill FROM `member` AS m LEFT JOIN `order` AS o ON m.id=o.user_id and o.invoice = 1 AND o.status=2 LEFT JOIN invoice AS i ON m.id=i.user_id GROUP BY m.id ORDER BY m.id DESC ";
FROM member AS m
LEFT JOIN `order` AS o ON m.id=o.user_id
WHERE o.status= 2 or o.status IS NULL
GROUP BY m.id
ORDER BY m.id DESC ";order表中没有订单,那么这个用户对应的order表中status就是NULL
订单是否需要开发票使用一个invoice的字段来表示,而status=2则表示订单已经处理完成。我的要求是列出每个用户需要开发票的订单金额总数及已经该用户已经开过的发票总数,
但用下面的SQL语句得不到正确结果,
$sql = "SELECT m.id AS user_id,m.company,
sum(o.money) AS total_bill,
sum(i.bill_money) AS drawn_bill
FROM `member` AS m
LEFT JOIN `order` AS o ON m.id=o.user_id
LEFT JOIN invoice AS i ON m.id=i.user_id
WHERE o.invoice = 1 AND o.status=2 /* 这里是条件
GROUP BY m.id
ORDER BY m.id DESC ";不知道三表联结可以这样写吗? 如果不可以想得到我想要的结果,能用一条SQL语句实现否?不知道我说明白了没有,
FROM member AS m
LEFT JOIN `order` AS o ON m.id=o.user_id and o.status= 2
GROUP BY m.id
ORDER BY m.id DESC ";
sum(o.money) AS total_bill,
sum(i.bill_money) AS drawn_bill
FROM `member` AS m
LEFT JOIN `order` AS o ON m.id=o.user_id and o.invoice = 1 AND o.status=2
LEFT JOIN invoice AS i ON m.id=i.user_id
GROUP BY m.id
ORDER BY m.id DESC ";
并不是每个用户的所有发票记录,
目前有二种方法:
1.创建临时表,将其中的一个sum和user_id保存,然后再使用join就可以了.
2.二次分别查询二表的sum,保存结果集,用PHP处理我目前使用的是第一种方法,不知道哪种效率会更高一些。