- php基础编程 - dz的这个查询太恐怖了而且基本是用的最多的一条查询 - 喜悦国际村

喜悦国际村 » php基础编程 » dz的这个查询太恐怖了而且基本是用的最多的一条查询

喜悦证交所已经关闭

上一主题

下一主题

kernel
中级会员
Rank: 3

一般会员

UID 26080
精华 0
积分 338
帖子 231
金钱 338 喜悦币
威望 0
人脉 0
阅读权限 30
注册 2004-2-10
状态离线

dz的这个查询太恐怖了而且基本是用的最多的一条查询

SELECT p.*, a.aid AS aaid, a.creditsrequire, a.filetype, a.filename, a.attachment, a.filesize, a.downloads, m.uid, m.username, m.gender, m.status, m.regdate, m.lastvisit, m.postnum, m.credit, m.email, m.site, m.icq, m.oicq, m.yahoo, m.msn, m.location, m.avatar, m.signature, m.customstatus, m.showemail
FROM $table_posts p LEFT JOIN $table_members m ON m.username=p.author LEFT JOIN $table_attachments a ON p.aid<>'0' AND p.aid=a.aid WHERE p.tid='$tid' ORDER BY dateline LIMIT $start_limit, $ppp

d2.2f看帖子时候的查询

log more

2006-5-18 02:39 PM

cutejing
新手上路
Rank: 1

老会员

UID 28423
精华 0
积分 5
帖子 503
金钱 5 喜悦币
威望 0
人脉 0
阅读权限 10
注册 2004-5-10
来自流浪的人无定居所
状态离线

[推荐阅读] 求“数字类型，且小数位数不能超过4位”的正则

不就是 2个left join吗给些更加恐怖的你看

CODE:

[Copy to clipboard]

<?php
$query = "SELECT DISTINCT products.productid as productid,products.cost as cost,
sum( if (left (b.refno, 2) = 'CG' and c.location='$locid' AND b.quantity > 0, b.quantity , 0 )) as total_in,
sum( if (left (b.refno, 2) = 'CG' and c.orderdate <= '$limitdate', b.quantity,
if (left (b.refno, 2) = 'CJ' and c.orderdate <= '$limitdate', b.quantity,
if (left (b.refno, 2) = 'CK' and c.orderdate <= '$limitdate', 0 - b.quantity,
if (left (b.refno, 2) = 'OP' and c.orderdate <= '$limitdate', 0 - b.quantity,
if (left (b.refno, 2) = 'CC' and c.orderdate <= '$limitdate', 0 - b.quantity, 0 )))))) as assetquantity
$sumtotalmoney
FROM products,assetmovedetail b ,assetmove c
WHERE b.refno=c.refno and products.productid=b.productid and products.status<>'deleted'
GROUP BY b.productid
HAVING total_in > 0
ORDER BY products.productnumber";
?>

做报表的基本上都是这些
写倒容易
出错
让你去修改那才郁闷

the balance between love and like

2006-5-18 02:54 PM

php5
金牌会员
Rank: 6

中级会员

UID 62897
精华 0
积分 1263
帖子 1112
金钱 1263 喜悦币
威望 0
人脉 0
阅读权限 70
注册 2005-1-3
来自福建
状态离线

QUOTE:

原帖由 cutejing 于 2006-5-18 02:54 PM 发表
不就是 2个left join吗给些更加恐怖的你看

$query = "SELECT DISTINCT products.productid as productid,products.cost as cost,
sum( if (left (b.refno, 2) = 'CG' and c.location='$locid' AND b.q ...

不见得这么写有何高明的地方，不过敢用在PHP里面也确实“恐怖”

2006-5-18 03:00 PM