Chọn các bản ghi từ 2 tháng trở lại đây

Oracle:

Mailing có một vấn đề: các record cũ quá 2 tháng thì không nên dùng lại theo đề nghị của nhóm.

SELECT created_dtime FROM requests
WHERE status = 0 AND created_dtime > LAST_DAY(ADD_MONTHS(SYSDATE, -2))

Trước hết là xóa các record trong tháng cách đây 2 tháng:

DELETE FROM requests
WHERE status = 0
AND created_dtime < LAST_DAY(ADD_MONTHS(SYSDATE, -2))

Backup MySQL

Step 1:

mysqldump --single-transaction -uroot -p --all-databases --master-data=1 > all_databases.sql

Step 2:

mysql -uroot < all_databases.sql

MySQL and JSON

SELECT
CONCAT("[",
GROUP_CONCAT(
CONCAT("{username:'",username,"'"),
CONCAT(",email:'",email),"'}")
)
,"]")
AS json FROM users;

To

[
{username:'mike',email:'mike@mikesplace.com'},
{username:'jane',email:'jane@bigcompany.com'},
{username:'stan',email:'stan@stanford.com'}
]

Thanks to http://www.thomasfrank.se/mysql_to_json.html

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

How to decrease length of fixed width column in Oracle

Lọ mọ cả ngày tìm ra lý do tại sao module reset password lại không hoạt động. Hóa ra cột chứa password có kiểu CHAR(42) trong khi password chỉ dài có 40 kí tự. Công việc giảm độ dài của field này cũng lắm nhiêu khê vì không thể làm đơn giản kiểu

ALTER TABLE users MODIFY password CHAR(40);

vì sẽ có lỗi 01441 ngay lập tức khi mà bảng đã có dữ liệu.

Vậy phải làm thế nào:

+ Thêm cột mới vào ngay sau cột cũ

+ Trao đổi dữ liệu 2 cột

+ Drop cột cũ

Tuy nhiên MySQL is more advanced than Oracle trong cái vụ ADD BEFORE/AFTER này nhiều. Oracle thì phải làm như sau:

/** Add a new column with desired width */
ALTER TABLE users ADD (password2 char(40));
/** Trimming trailing whitespace and swap */
UPDATE users SET password2 = TRIM(TRAILING ' ' FROM 'password');

COMMIT;
/** Drop old column */
ALTER TABLE users DROP COLUMN user_password;
/** Change to old name */
ALTER TABLE users RENAME COLUMN password2 TO password;
/** Prepare for the temporary table */
ALTER TABLE users RENAME TO users2;

/** Reserve the order of the field so not insert/update query will be affected */
CREATE TABLE users NOLOGGING /* unrecoverable */
AS
SELECT user_id, username, password,
email, lastname, firstname, company, reg_dtime,
user_act_code, status, news_subscriber, duration_type_id,
salutation_id, referrer_id, another_referrer, language_id, gender_id
FROM users2;
/** GC */
DROP TABLE users2;

Oracle table case-sensitive

Table in-case-sensitive

SELECT * FROM myusers

Table case-sensitive

select table_name from user_tables where table_name='MYUSERS';