将查询结果作为列的数据

资讯发布在查询结果列表中需要显示发布情况. 发布是在另外一张表里面添加的发布信息.

 

结构:

 

资讯表 :

 

 

	Field	Type	Comment
	id	bigint(20) NOT NULL
	type_id	varchar(50) NOT NULL
	type_name	varchar(50) NOT NULL

 

发布表:

 

 

	Field	Type	Comment
	id	int(11) NOT NULL
	fid	bigint(20) NOT NULL
	cms_category_id	int(11) NOT NULL
	release_date	datetime NOT NULL
	order_num	int(5) NOT NULL

 

fid 对应的 信息表里面的信息id

 

这样 ，搜索的SQL可以使用这样的查询：

 

select * from (
        SELECT information_content_item.id,information_content_item.article_title,
        (case(select count(*) from cms_item where cms_item.fid = information_content_item.id and cms_item.cms_category_id = #cmsTypeId#) when 0 then '未发布' else '已发布' end )  as releaseStatus,
        cms_item.release_date,cms_item.releaser,cms_item.order_num
        FROM information_content_item left join cms_item on information_content_item.id = cms_item.fid and cms_item.cms_category_id = #cmsTypeId#

        AND information_content_item.status = 1
        ) as CmsContentItem

        order by id desc limit #start#,#rowsPerPage#