Oracle: Đếm số hàng trên từng cột

Tracking tình hình hợp đồng là một phần trong hệ RBAC. Nhiệm vụ là tổng hợp được số hàng theo từng cột theo từng tiêu chí. Có một bảng theo dõi contract của các user và một bảng theo dõi từng số lần access vào các tài nguyên hợp đồng.

Cách 1: Thuần túy ASCII

$sql = "SELECT c.contract_id FROM

(SELECT

c.contract_id, c.contract_end_date, c.contract_priority,

ct.max_company_count, ct.max_report_count

FROM contracts c, contract_types ct

WHERE c.contract_type_id = ct.contract_type_id

AND c.contract_id IN ($validContracts)

AND c.user_id    = :user_id) c,

(SELECT

c.contract_id,

COUNT(DISTINCT tr.company_id) company_count

FROM contracts c, contract_trackers tr

WHERE c.contract_id = tr.contract_id

AND c.contract_id IN ($validContracts)

GROUP BY c.contract_id) cc1,

(SELECT

c.contract_id,

COUNT(DISTINCT tr.number) company_count

FROM contracts c, contract_trackers tr

WHERE c.contract_id = tr.contract_id

AND c.contract_id IN ($validContracts)

GROUP BY c.contract_id) cc2,

(SELECT

c.contract_id,

COUNT(DISTINCT tr.report_file) report_count

FROM contracts c, contract_trackers tr

WHERE c.contract_id = tr.contract_id

AND c.contract_id IN ($validContracts)

GROUP BY c.contract_id) cc3

WHERE c.contract_id     = cc1.contract_id

AND cc1.contract_id     = cc2.contract_id

AND cc2.contract_id     = c.contract_id

AND (c.max_company_count IS NOT NULL AND

c.max_company_count > (CASE

WHEN cc1.company_count > cc2.company_count THEN cc1.company_count

ELSE cc2.company_count

END))

AND (c.max_report_count IS NOT NULL AND

c.max_report_count > cc3.report_count)

ORDER BY c.contract_priority DESC";

Cách 2: Lợi dụng đặc điểm của COUNT DISTINCT của Oracle

SELECT c.contract_id

FROM contracts c,

contract_types ct,

(SELECT

c1.contract_id,

COUNT(DISTINCT tr.company_id) company_count,

COUNT(DISTINCT tr.number) company_count2,

COUNT(DISTINCT tr.report_file) report_count

FROM contracts c1,

contract_trackers tr

WHERE c1.contract_id = tr.contract_id

AND c1.contract_id IN (4)

GROUP BY c1.contract_id) tr

WHERE c.contract_type_id = ct.contract_type_id

AND tr.contract_id = c.contract_id

AND c.contract_id IN (4)

AND c.user_id    = 8

AND (ct.max_company_count IS NOT NULL AND

ct.max_company_count > (CASE

WHEN tr.company_count > tr.company_count2 THEN tr.company_count

ELSE tr.company_count2

END))

AND (ct.max_report_count IS NOT NULL AND

ct.max_report_count > tr.report_count)

ORDER BY c.contract_priority DESC