＃＃＃＃＃＃＃＃＃＃ DML.SQL ＃＃＃＃＃＃＃＃＃＃＃＃
＃＃＃＃＃＃＃＃＃＃ DML.SQL ＃＃＃＃＃＃＃＃＃＃＃＃
＃＃＃＃＃＃＃＃＃＃ DML.SQL ＃＃＃＃＃＃＃＃＃＃＃＃
＃＃＃＃＃＃＃＃＃＃ DML.SQL ＃＃＃＃＃＃＃＃＃＃＃＃
CREATE TABLE N_CATEGORY_INFO (
CATECD INT PRIMARY KEY AUTO_INCREMENT
, CATENAME VARCHAR(255) NOT NULL
, ISACTIVE CHAR(1) DEFAULT 'N'
, REGDATE DATETIME
);
CREATE TABLE N_DATA_INFO (
DATACD INT PRIMARY KEY AUTO_INCREMENT
, CATECD INT NOT NULL
, SORTCODE INT DEFAULT '0'
, LABEL VARCHAR(255) NOT NULL
, SUBJECT VARCHAR(255) NOT NULL
, CONTENTS TEXT NOT NULL
, READNUM INT DEFAULT '0'
, ISACTIVE CHAR(1) DEFAULT 'N'
, REGDATE DATETIME
);
CREATE TABLE N_DATA_COMMENT (
DCCD INT PRIMARY KEY AUTO_INCREMENT
, DATACD INT NOT NULL
, CATECD INT NOT NULL
, COMMENT TINYTEXT NOT NULL
, USERNAME VARCHAR(40) NOT NULL
, PWD VARCHAR(40) NOT NULL
, ISACTIVE CHAR(1) DEFAULT '0'
, REGDATE DATETIME
);
CREATE TABLE N_RANK_INFO (
RICD INT PRIMARY KEY AUTO_INCREMENT
, RANKTYPE VARCHAR(255) DEFAULT 'NEWS'
, RANKNAME VARCHAR(255) UNIQUE
, ISACTIVE CHAR(1) DEFAULT 'N'
, REGDATE DATETIME
);
CREATE TABLE N_RANK_NEWSDATA (
RANK INT NOT NULL
, RICD INT NOT NULL
, DATACD INT NOT NULL
, CATECD INT NOT NULL
, RANKDATE DATE NOT NULL
, REGDATE DATETIME
);
CREATE TABLE N_RANK_COMMENTDATA (
RANK INT NOT NULL
, RICD INT NOT NULL
, OCCD INT NOT NULL
, DATACD INT NOT NULL
, CATECD INT NOT NULL
, RANKDATE DATE NOT NULL
, REGDATE DATETIME
);
/*
########## BASE CODE #############################
INSERT INTO N_RANK_INFO (RANKTYPE, RANKNAME, ISACTIVE, REGDATE)
VALUES('GAME', 'MMORPGランキング', 'Y', SYSDATE());
INSERT INTO N_RANK_INFO (RANKTYPE, RANKNAME, ISACTIVE, REGDATE)
VALUES('NEWS', 'MMORPGニュースランキング', 'Y', SYSDATE());
INSERT INTO N_RANK_INFO (RANKTYPE, RANKNAME, ISACTIVE, REGDATE)
VALUES('GAME', 'MMORPG新ゲーム', 'Y', SYSDATE());
INSERT INTO N_RANK_INFO (RANKTYPE, RANKNAME, ISACTIVE, REGDATE)
VALUES('GAME', 'おすすめゲーム', 'Y', SYSDATE());
########## BASE CODE #############################
*/
/*
########## SAMPLE TABLE ##########################
CREATE TABLE N_TEST(
CATECD INT PRIMARY KEY AUTO_INCREMENT
, RANKNAME VARCHAR(255) NOT NULL
, RANKDATE DATE
, REGDATE DATETIME
);
INSERT INTO N_TEST(RANKNAME, RANKDATE, REGDATE)
VALUES('TEST', SYSDATE(), SYSDATE());
CREATE TABLE N_RANK_TEST(
RTCD INT AUTO_INCREMENT,
CATECD INT,
DATACD INT,
READNUM INT
);
INSERT INTO N_RANK_TEST(CATECD, DATACD, READNUM) VALUES(1,1,2345);
INSERT INTO N_RANK_TEST(CATECD, DATACD, READNUM) VALUES(1,2,4345);
INSERT INTO N_RANK_TEST(CATECD, DATACD, READNUM) VALUES(1,3,2845);
INSERT INTO N_RANK_TEST(CATECD, DATACD, READNUM) VALUES(1,4,1232);
INSERT INTO N_RANK_TEST(CATECD, DATACD, READNUM) VALUES(2,1,5345);
INSERT INTO N_RANK_TEST(CATECD, DATACD, READNUM) VALUES(2,2,102);
INSERT INTO N_RANK_TEST(CATECD, DATACD, READNUM) VALUES(3,1,1);
INSERT INTO N_RANK_TEST(CATECD, DATACD, READNUM) VALUES(3,2,10000);
RANKING DATA SAMPLE
SELECT DISTINCT COUNT( T1.RTCD ) AS RANK, T1.RTCD, T1.READNUM,
T1.CATECD, T1.DATACD, SYSDATE()
FROM N_RANK_TEST T1, N_RANK_TEST T2
WHERE T1.READNUM < T2.READNUM
OR T1.RTCD = T2.RTCD
GROUP BY T1.RTCD
ORDER BY RANK
LIMIT 0 , 1000000
########## SAMPLE TABLE ##########################
*/
＃＃＃＃＃＃＃＃＃＃ QUERY.SQL ＃＃＃＃＃＃＃＃＃＃＃＃
＃＃＃＃＃＃＃＃＃＃ QUERY.SQL ＃＃＃＃＃＃＃＃＃＃＃＃
＃＃＃＃＃＃＃＃＃＃ QUERY.SQL ＃＃＃＃＃＃＃＃＃＃＃＃
＃＃＃＃＃＃＃＃＃＃ QUERY.SQL ＃＃＃＃＃＃＃＃＃＃＃＃
INSERT INTO N_CATEGORY_INFO (CATENAME, ISACTIVE, REGDATE)
VALUES ('$CATENAME', '$ISACTIVE', now());
UPDATE N_CATEGORY SET CATENAME = '$CATENAME', ISACTIVE = '$ISACTIVE'
WHERE CATECD = $CATECD;
SELECT CATECD, CATENAME, ISACTIVE, REGDATE FROM N_CATEGORY_INFO
WHERE ISACTIVE = '$ISACTIVE';
ORDER BY REGDATE ASC;
SELECT CATECD, CATENAME, ISACTIVE, REGDATE FROM N_CATEGORY_INFO
WHERE CATECD = $CATECD AND ISACTIVE = '$ISACTIVE';
INSERT INFO N_DATA_INFO (CATECD, SUBJECT, CONTENTS, ISACTIVE, REGDATE)
VALUES ($CATECD, '$SUBJECT', '$CONTENTS', '$ISACTIVE', now());
UPDATE N_DATA_INFO SET SORTCODE = $SORTCODE, SUBJECT = '$SUBJECT', CONTENTS = '$CONTENTS', ISACTIVE = '$ISACTIVE'
WHERE DATACD = $DATACD;
SELECT DATACD, CATECD, SORTCODE, SUBJECT, READNUM, REGDATE
FROM N_DATA_INFO WHERE CATECD = $CATECD AND ISACTIVE = '$ISACTIVE' ORDER BY SORTCODE DESC;
SELECT DATACD, CATECD, SORTCODE, SUBJECT, CONTENTS, READNUM, ISACTIVE, REGDATE
FROM N_DATA_INFO WHERE CATECD = $CATECD AND DATACD = $DATACD AND ISACTIVE = '$ISACTIVE';
INSERT INFO N_DATA_COMMENT (DATACD, CATECD, COMMENT, USERNAME, PWD, ISACTIVE, REGDATE)
VALUES ($DATACD, $CATECD, '$COMMENT', '$USERNAME', '$PWD', '$ISACTIVE', now());
UPDATE N_DATA_COMMENT SET SORTCODE = $SORTCODE, SUBJECT = '$SUBJECT', CONTENTS = '$CONTENTS', ISACTIVE = '$ISACTIVE'
WHERE DCCD = $DCCD;
SELECT DATACD, CATECD, SORTCODE, SUBJECT, READNUM, REGDATE
FROM N_DATA_INFO WHERE CATECD = $CATECD AND ISACTIVE = '$ISACTIVE' ORDER BY SORTCODE DESC;
SELECT DATACD, CATECD, SORTCODE, SUBJECT, CONTENTS, READNUM, ISACTIVE, REGDATE
FROM N_DATA_INFO WHERE CATECD = $CATECD AND DATACD = $DATACD AND ISACTIVE = '$ISACTIVE';
INSERT INTO N_RANK_INFO (RANKNAME, ISACTIVE, REGDATE)
VALUES ('$RANKNAME', '@ISACTIVE', now());
UPDATE N_RANK_INFO RANKNAME = '$RANKNAME', ISACTIVE = '$ISACTIVE'
WHERE RICD = '$RICD';
SELECT RICD, RANKNAME, ISACTIVE, REGDATE
FROM N_RANK_INFO;
SELECT RICD, RANKNAME, ISACTIVE, REGDATE
FROM N_RANK_INFO
WHRER RICD = $RICD;
/*
HOT RANKING DATA QUERY
*/
INSERT INTO N_RANK_NEWSDATA (RICD, RANK, DATACD, CATECD, RANKDATE, REGDATE)
SELECT DISTINCT 1, COUNT(T1.DATACD) AS RANK, DATACD, CATECD, SYSDATE(), SYSDATE()
FROM N_DATA_INFO T1, N_DATA_INFO T2
WHERE T1.READNUM < T2.READNUM OR T1.DATACD = T2.DATACD
GROUP BY T1.DATACD
ORDER BY RANK
LIMIT 0, 100
/*
NEW RANKING DATA QUERY
*/
INSERT INTO N_RANK_NEWSDATA (RICD, RANK, DATACD, CATECD, RANKDATE, REGDATE)
SELECT DISTINCT 1, COUNT(T1.DATACD) AS RANK, DATACD, CATECD, SYSDATE(), SYSDATE()
FROM N_DATA_INFO T1, N_DATA_INFO T2
WHERE T1.DATACD < T2.DATACD OR T1.DATACD = T2.DATACD
GROUP BY T1.DATACD
ORDER BY RANK
LIMIT 0, 100
/*
NEWS Ranked Query
*/
SELECT CI.CATECD, DI.DATACD, RN.RICD, RN.RANK, CI.CATENAME, DI.SUBJECT, RN.RANKDATE, RN.REGDATE
FROM N_RANK_NEWSDATA AS RN
INNER JOIN N_DATA_INFO AS DI ON RN.DATACD = DI.DATACD
INNER JOIN N_CATEGORY_INFO AS CI ON RN.CATECD = CI.CATECD
WHERE RN.RANKDATE = '$RANKDATA';
/*
News Ranking Query
1. NEW NEWS RANKING
SELECT DATACD, CATECD FROM N_DATA_INFO ORDER BY DATACD DESC LIMIT 1, 100
2. HOT NEWS RANKING
SELECT DATACD, CATECD FROM N_DATA_INFO ORDER BY READNUM DESC LIMIT 1, 100
3. RELATIONAL NEWS (NEW)
SELECT DATACD, CATECD FROM N_DATA_INFO
WHERE LABEL = '$LABEL'
ORDER BY DATACD DESC LIMIT 1, 100
4. RELATIONAL NEWS (HOT)
SELECT DATACD, CATECD FROM N_DATA_INFO
WHERE LABEL = '$LABEL'
ORDER BY READNUM DESC LIMIT 1, 100
*/
/*
HOT RANKING DATA SAMPLE
*/
SELECT DISTINCT COUNT( T1.RTCD ) AS RANK, T1.RTCD, T1.READNUM,
T1.CATECD, T1.DATACD, SYSDATE()
FROM N_RANK_TEST T1, N_RANK_TEST T2
WHERE T1.READNUM < T2.READNUM
OR T1.RTCD = T2.RTCD
GROUP BY T1.RTCD
ORDER BY RANK
LIMIT 0 , 30
/*
NEW RANKING DATA SAMPLE
*/
SELECT DISTINCT COUNT( T1.RTCD ) AS RANK, T1.RTCD, T1.READNUM,
T1.CATECD, T1.DATACD, SYSDATE()
FROM N_RANK_TEST T1, N_RANK_TEST T2
WHERE T1.RTCD < T2.RTCD
OR T1.RTCD = T2.RTCD
GROUP BY T1.RTCD
ORDER BY RANK
LIMIT 0 , 30
