27,579
社区成员
发帖
与我相关
我的任务
分享
from auto_payment_due_t a inner join workload_t on a.policy_no = workload_t.policy_no
and a.deal_date between '01-01-2011' and '01-31-2011'
and a.payment_due_type in ('2','3','4','7')
inner join auto_agreement_t b on a.policy_no = b.policy_no
inner join motorised_vehicl01 c on a.policy_no = c.policy_no and b.policy_no = c.policy_no
select a.branch_company_c01,a.policy_no,
a.endorsement_no,
a.product_code,a.department_code, date(a.deal_date),
a.amount,b.selling_channel_01,
c.vehicle_type,c.usage,workload_t.percent
from
(
select * from auto_payment_due_t
where date(deal_date) between '01-01-2011' and '01-31-2011'
and payment_due_type in ('2','3','4','7')
) a
inner join workload_t on a.policy_no = workload_t.policy_no,
inner join auto_agreement_t b on a.policy_no = b.policy_no
inner join motorised_vehicl01 c on a.policy_no = c.policy_no
--SQL SERVER 2005
SELECT
a.branch_company_c01,
a.policy_no,
a.endorsement_no,
a.product_code,
a.department_code,
deal_date = CONVERT(VARCHAR(10), a.deal_date, 120),
a.amount,
b.selling_channel_01,
c.vehicle_type,
c.usage,
w.[percent]
FROM auto_payment_due_t a
INNER JOIN workload_t w
ON a.policy_no = w.policy_no
INNER JOIN auto_agreement_t b
ON a.policy_no = b.policy_no
INNER JOIN motorised_vehicl01 c
ON a.policy_no = c.policy_no
WHERE a.deal_date BETWEEN '20110101' AND '20110131'
AND a.payment_due_type in ('2','3','4','7')
--a.deal_date 加索引
--a.payment_due_type 加索引
--a.policy_no, b.policy_no, c.policy_no 加索引(如果唯一可考虑聚集索引)
select a.branch_company_c01,a.policy_no,
a.endorsement_no,
a.product_code,a.department_code, date(a.deal_date),
a.amount,b.selling_channel_01,
c.vehicle_type,c.usage,workload_t.percent
from auto_payment_due_t a
left join auto_agreement_t b on a.policy_no = b.policy_no
left join motorised_vehicl01 c on a.policy_no = c.policy_no
where date(deal_date) between '01-01-2011' and '01-31-2011'
and a.payment_due_type in ('2','3','4','7')
select a.branch_company_c01,a.policy_no,
a.endorsement_no,
a.product_code,a.department_code, date(a.deal_date),
a.amount,b.selling_channel_01,
c.vehicle_type,c.usage,workload_t.percent
from auto_payment_due_t a
inner join workload_t on a.policy_no = workload_t.policy_no,
inner join auto_agreement_t b on a.policy_no = b.policy_no
inner join motorised_vehicl01 c on a.policy_no = c.policy_no
where date(deal_date) between '01-01-2011' and '01-31-2011'
and a.payment_due_type in ('2','3','4','7')