SELECT * FROM `m_user` WHERE username LIKE ‘%a%’
で余分に引っかかる場合はVARCHAR 型にBINARY 属性をつければOK
INSERT INTO d_point (usercode,contnmbr,pintdate,pointnum) VALUES (“101″,11,”2008-05-30”,10)
DELETE FROM d_point WHERE usercode = “101” AND contnmbr = 11 AND pintdate = “2008-05-30”
SELECT * FROM `d_point` WHERE usercode = “101” AND contnmbr = 11
SELECT sum(pointnum) as pointnum FROM d_point WHERE usercode = “101” AND contnmbr = 11
ﾗﾝｷﾝｸﾞ
SELECT usercode,SUM(pointnum) as pointnum FROM d_point WHERE contnmbr = 11 GROUP BY usercode ORDER BY pointnum DESC;
SELECT a.usercode,SUM(a.pointnum) as pointnum FROM d_point as a m_user as b WHERE a.contnmbr = 11 GROUP BY a.usercode ORDER BY pointnum DESC;
SELECT a.usercode,SUM(a.pointnum) as pointnum FROM d_point as a,m_user as b WHERE a.contnmbr = 11 GROUP BY a.usercode ORDER BY pointnum DESC;
■〇〇(ポスカ、イケスタ等)のXXXXランキング
SELECT
c.contname,
a.usercode,
a.username,
sum(b.pointnum) as pointnum,
c.pinttani
FROM
m_user as a,
d_point as b,
m_contest as c
WHERE
a.usercode=b.usercode AND
b.contnmbr=c.contnmbr AND
c.contnmbr=2
GROUP BY
c.contname,
a.username,
c.pinttani
ORDER BY
pointnum desc
–解説
c.contnmbr=2 ← コンテスト番号が入ります。
ポスカ、イケスタ、イケテンすべて共通の連番です。
タイはSQLでは行えません(たぶん)。PHP側で頑張ってください！
■〇〇(ポスカ、イケスタ等)のトータルランキング
SELECT
a.usercode,
a.username,
sum(b.pointnum) as pointnum
FROM
m_user as a,
d_point as b,
m_contest as c
WHERE
a.usercode=b.usercode AND
c.contcode=1
GROUP BY
a.usercode,
a.username
ORDER BY
pointnum desc
–解説
c.contcode=1 ← 1:ポスカ 2:イケスタ 3:イケテン
SELECT * FROM `m_user` WHERE usercode = ‘001’
INSERT INTO m_user WHERE usercode = ‘001’
INSERT INTO m_client_utn (clientid,utnagent,lastlgin) VALUES (“aaa”,”aaaa”,”aaa”);
INSERT INTO m_client_utn (utnagent) VALUES (“aaa”);
INSERT INTO m_user (utnagent) VALUES (“aaa”);
更新
UPDATE m_user SET utnagent = ‘xx’ WHERE usercode = ‘001’
$sql = “SELECT * FROM m_user”;
$rst = fncDbExec($sql);
$rows = mysql_num_rows($rst);
$col = mysql_fetch_array($,MYSQL_ASSOC);
print_r($col);
exit;