[mysql] 关联查询sql记录

//查询账单关联订单
select
o.id as id,
o.order_no as orderNo,
o.case_no as caseNo,
o.send_time as sendTime,
o.final_time as finalTime,
(select ca.car_no from fm_order_case ca where ca.case_no = o.case_no) as carNo,
(select co.service_money from fm_order_cost co where co.order_id = o.id ) as serviceMoney
from fm_order o 
where o.id IN (
select bo.order_id from ac_bill_order bo where bo.bill_id = 3
)
 
//发票 账单 机构信息联查
SELECT
g.org_name as orgName,
b.bill_seq as billSeq,
b.order_count as orderCount,
b.invoice_stat as invoiceStat,
a.invoice_title as invoiceTitle,
a.invoice_type as invoiceType,
a.invoice_company as invoiceCompany,
a.invoice_content as invoiceContent,
a.invoice_money as invoiceMoney,
a.apply_time as applyTime
FROM ac_invoice_apply a 
LEFT JOIN ac_bill b ON b.id = a.bill_id
LEFT JOIN ct_group g ON g.user_id = b.user_id
 
//查询基准id
SELECT 
    max(b.id)
FROM
    ac_bill b
WHERE
    b.user_id = 59
AND
    b.bad_debt_id is not null
 
 
//查询待插入的账单id
SELECT
    b.id
FROM
    ac_bill b
WHERE
    b.id>6 
and 
    b.user_id = 59
 
//查询催款单时间段
SELECT DISTINCT
(
select b.start_time from ac_bill b
where b.id = (
select min(b.id) from ac_bill b where b.id > 6 and b.user_id = 59
)
) as startTime,
(
select b.end_time from ac_bill b
where b.id = (
select max(b.id) from ac_bill b where b.id > 6 and b.user_id = 59
)
) as endTime
FROM ac_bill b
 
//查询催款时段累计金额
select  sum(b.bill_money-b.deposit_money)
FROM ac_bill b
WHERE b.id in(
SELECT
    b.id
FROM
    ac_bill b
WHERE
    b.id>6  and b.user_id = 59 
)
 
 
 
//查询催款时间短+累计金额
SELECT DISTINCT
(
select b.start_time from ac_bill b
where b.id = (
select min(b.id) from ac_bill b where b.id > 6 and b.user_id = 59
)
) as startTime,
(
select b.end_time from ac_bill b
where b.id = (
select max(b.id) from ac_bill b where b.id > 6 and b.user_id = 59
)
) as endTime,
(
select  sum(b.bill_money-b.deposit_money)
FROM ac_bill b
WHERE b.id in(
SELECT
b.id
FROM
ac_bill b
WHERE
b.id> 6 
and b.user_id = 59
)
) as totalMoney
FROM ac_bill b
left join ct_user u on u.id = b.user_id
WHERE b.user_id = 59
 
//查询与催款单关联的账单信息
select 
* 
from
ac_reminder_bill rb
LEFT JOIN
ac_bill b on rb.bill_id = b.id
where rb.reminder_id = 3



每月1号自动生成所有机构的上月账单
//查询完成订单ID
SELECT
o.id
FROM
fm_order o LEFT JOIN fm_order_cost c ON o.id = c.order_id
WHERE
o.buyer_user_id = 59
AND
o.deal_stat = '09'
AND
YEAR(o.final_time) = 2014
AND
MONTH(o.final_time) = 6
 
 
//查询完成订单个数
SELECT
count(*)
FROM
fm_order o LEFT JOIN fm_order_cost c ON o.id = c.order_id
WHERE
o.buyer_user_id = 59
AND
o.deal_stat = '09'
AND
YEAR(o.final_time) = 2014
AND
MONTH(o.final_time) = 6
 
 
//查询完成订单总金额
SELECT
sum(c.pay_money)
FROM
fm_order o LEFT JOIN fm_order_cost c ON o.id = c.order_id
WHERE
o.buyer_user_id = 59
AND
o.deal_stat = '09'
AND
YEAR(o.final_time) = 2014
AND
MONTH(o.final_time) = 6
 
 
//查询用户上月充值总金额
SELECT
sum(d.deposit_money)
FROM
ac_deposit d
WHERE
d.user_id = 59
AND
d.deposit_stat = 1
AND
d.audit_stat = 2
AND
YEAR(d.deposit_time) = 2014
AND
MONTH(d.deposit_time) = 6
 
//sql合体，一个语句查询某月某用户的完成订单数  账单金额 充值金额
SELECT
ifnull(
(
SELECT count(*) FROM fm_order o
WHERE o.buyer_user_id = 59
AND o.deal_stat = '09'
AND    YEAR(o.final_time) = 2014
AND MONTH(o.final_time) = 6
),
'0'
) as orderCount,
ifnull(
(
SELECT sum(c.pay_money) FROM fm_order o 
LEFT JOIN fm_order_cost c ON o.id = c.order_id
WHERE o.buyer_user_id = 59
AND o.deal_stat = '09'
AND YEAR(o.final_time) =  2014
AND MONTH(o.final_time) = 6
),
'0'
) as billMoney,
ifnull(
(
SELECT sum(d.deposit_money) FROM ac_deposit d
WHERE d.user_id = 59
AND d.deposit_stat = 1
AND d.audit_stat = 2
AND YEAR(d.deposit_time) = 2014
AND MONTH(d.deposit_time) = 6
),
'0'
) as depositMoney