mysql语句汇总 - 数据库编程

GROUP BY xfp.subjectId,xsb.Id ORDER BY xsb.Id) AS t LEFT JOIN xmgl_subject_budget xsb2 ON t.Pid = xsb2.Id WHERE t.Pid <> 0 GROUP BY t.Pid;

3.-- 查询各个部门在特定科目下的控制额度及支出信息(优化)

SELECT DISTINCT(xcs1.dept),xsc.subjectName,xcs2.amount,COALESCE(SUM(xfa.amount),0) payedMoney
,xcs2.amount-SUM(xfa.amount) leftAmount
FROM xmgl_control_subject xcs1
LEFT JOIN xmgl_control_subject xcs2 ON xcs1.dept = xcs2.dept AND xcs2.detailId = 3
LEFT JOIN xmgl_subject_control xsc ON xcs2.detailId = xsc.Id
LEFT JOIN xmgl_finance_activity xfa ON xcs2.dept = xfa.department AND xfa.detailId IN('22','23')
GROUP BY xcs1.dept,xcs1.detailId;

4.类似于QQ空间的消息列表

-- 赞了我
SELECT tf.id,1 AS typeId,c.avatar AS avatar, c.name,ct.catalog AS catalog, ct.id AS messageId, ct.content AS trendsContent,NULL AS replyContent, tf.create_time AS createTime,tf.read AS isRead
FROM trends_fork tf LEFT JOIN cust_trends ct ON tf.trends_id = ct.id LEFT JOIN customer c ON c.id = tf.customer_id
WHERE tf.master_id = 1
UNION ALL
-- 给我留言
SELECT cb.id,2 AS typeId,c.avatar AS avatar,c.name, 3 AS catalog,cb.id AS messageId,NULL AS trendsContent,cb.content AS replyContent,cb.create_time AS createTime,cb.read AS isRead
FROM cust_board cb LEFT JOIN customer c ON cb.customer_id = c.id WHERE cb.master_id = 1
UNION ALL
-- 评论我的动态
SELECT  tr.id,3 AS typeId, c.avatar AS avatar, c.name, ct.catalog AS catalog, ct.id AS messageId, ct.content AS trendsContent, tr.content AS replyContent,tr.create_time AS createTime,tr.read AS isRead
FROM trends_reply tr LEFT JOIN cust_trends ct ON tr.trends_id = ct.id LEFT JOIN customer c ON c.id = tr.customer_id
WHERE tr.master_id = 1
UNION ALL
-- 回复了我评论的消息（包括直接回复我的评论、在我的动态下评论别人的回复）
SELECT tra.id,4 AS typeId,c.avatar AS avatar, c.name, ct.catalog AS catalog, tra.trends_id AS messageId,ct.content AS trendsContent, tra.content AS replyContent, tra.create_time AS createTime,tra.read AS isRead
FROM trends_replyat tra LEFT JOIN  trends_reply tr ON tra.reply_id = tr.id LEFT JOIN customer c ON c.id = tra.customer_id LEFT JOIN cust_trends ct ON ct.id = tr.trends_id
WHERE tra.at = 1 OR (tra.master_id = 1 AND tra.customer_id != 1)
UNION ALL
-- 留言板中回复我的消息
SELECT cbr.id,5 AS typeId,c.avatar AS avatar, c.name, 3 AS catalog, cb.id AS messageId,cb.content AS trendsContent,cbr.content AS replyContent, cbr.create_time AS createTime,cbr.read AS isRead
FROM cust_board_reply cbr LEFT JOIN cust_board cb ON cbr.board_id = cb.id LEFT JOIN customer c ON cbr.customer_id = c.id
WHERE (cbr.board_customer_id = 1 AND cbr.customer_id != 1 ) OR (cbr.master_id = 1 AND cbr.customer_id != 1) ORDER BY  createTime DESC
LIMIT 2,10

mysql语句汇总(二)