一、资讯
1、查资讯自定义字段

view sourceprint?1 SELECT i.*,n.*,c.type AS c_type,c.name AS c_name,c.displayorder AS c_displayorder,c.customfieldtext AS c_customfieldtext,c.isdefault AS c_isdefault,c.isshare AS c_isshare FROM supe_spaceitems i, supe_spacenews n, supe_customfields c WHERE i.itemid = n.itemid AND n.customfieldid = c.customfieldid AND n.customfieldid !=0

通过supe_spaceitems,supe_spacenews,supe_customfields三表联查来取得数据。

2、查资讯列表，同时查对应的图片附件

view sourceprint?1 SELECT * FROM supe_spaceitems i, supe_attachments a WHERE i.picid = a.aid

查资讯列表和内容，同时查对应的图片附件

view sourceprint?1 SELECT i.*, n.message, a.* FROM supe_spaceitems i, supe_spacenews n, supe_attachments a WHERE i.picid = a.aid AND i.itemid=n.itemid

3、资讯列表，同时查对应的图片附件（列出仅含图片的文章）

view sourceprint?1 SELECT * FROM supe_spaceitems i, supe_attachments a WHERE i.picid = a.aid LIMIT 0,10

4、资讯列表和内容，同时查对应的图片附件

view sourceprint?1 SELECT i.*, n.message, a.* FROM supe_spaceitems i, supe_spacenews n, supe_attachments a WHERE i.picid = a.aid AND i.itemid=n.itemid LIMIT 0,10

5、利用left join联合查询，得到所有文章（不含图片的输出NULL），比嵌套查询效率更高。

view sourceprint?1 SELECT i . * , n.message, a . *

2 FROM supe_spaceitems i

3 INNER JOIN supe_spacenews n ON i.itemid = n.itemid

4 LEFT JOIN supe_attachments a ON i.picid = a.aid

5 ORDER BY i.itemid DESC

6 LIMIT 0 , 10

二、模型
1、调用模型中图片，标题，内容数据

view sourceprint?1 SELECT * FROM supe_[模型英文ID]message m,supe_[模型英文ID]items i WHERE m.itemid=i.itemid

supe_[模型英文ID]message 与 supe_[模型英文ID]items 是存储模型信息的主表，添加的字段也会添加在 supe_[模型英文ID]message 里面，将这两个表联起来就可以查询出想要的大部分数据。

2.模型分类：

view sourceprint?1 select g.* from supe_channels c right join supe_categories g on c.nameid=g.type where c.upnameid!="news" and c.nameid="test"

三、论坛
1、查询论坛中今天发帖总数与论坛总帖数

view sourceprint?1 SELECT sum(posts) AS posts , sum(todayposts) AS todayposts FROM cdb_forums

2、查询论坛主题，在设置主题分类时，显示出主题分类名称

view sourceprint?1 SELECT * FROM 数据库名.cdb_threads t, 数据库名.cdb_threadtypes y WHERE t.typeid = y.typeid

3、查询论坛的主题，并显示版块名称：

view sourceprint?1 SELECT * FROM 数据库名.cdb_threads t, 数据库名.cdb_forums f WHERE t.fid = f.fid

4、如何调用版块简介

view sourceprint?1 SELECT * FROM 数据库名.cdb_forums s,数据库名.cdb_forumfields f WHERE s.fid=f.fid

5、从SS取Discuz!含有图片附件的最新主题

view sourceprint?1 SELECT * , a.attachment AS attachment FROM cdb_threads t, cdb_attachments a, cdb_posts p WHERE t.tid = a.tid AND a.isimage =1 AND p.tid = t.tid AND p.first =1 GROUP BY t.tid ORDER BY t.dateline DESC

6、读取论坛 某个用户所收藏的主题：

view sourceprint?1 SELECT * FROM cdb_favorites f, cdb_members m, cdb_threads t WHERE f.uid = m.uid AND f.tid = t.tid

7、调用论坛中每个主题调用一张图片的SQL:

view sourceprint?1 SELECT a. * , t. * , count( DISTINCT a.tid )

2 FROM cdb_attachments a, cdb_threads t

3 WHERE a.tid = (

4 SELECT t.tid

5 FROM cdb_threads t

6 WHERE t.attachment =2

7 ORDER BY dateline DESC )

8 GROUP BY a.tid

8、查询论坛的分类信息
论坛的分类信息是要四表联查的，并且不能一次查出来，分两次。第一次是将主题表与主题类型有联查（数据显示一次）;第二次是查询分类信息的各个选项的值，查询出来后进行循环显示

view sourceprint?1 SELECT * FROM cdb_threads t, cdb_threadtypes y WHERE t.sortid = y.typeid AND t.tid=4
view sourceprint?1 SELECT * FROM cdb_typeoptionvars v,cdb_typeoptions o WHERE o.optionid=v.optionid AND v.tid=4 AND v.sortid=1

9、从SS取Discuz!含有图片附件的最新主题

view sourceprint?1 SELECT * , a.attachment AS attachment FROM cdb_threads t, cdb_attachments a, cdb_posts p WHERE t.tid = a.tid AND a.isimage =1 AND p.tid = t.tid AND p.first =1 GROUP BY t.tid ORDER BY t.dateline DESC LIMIT 0,10