PbootCMS中SQL标签的全面解析与实战应用

{pboot:sql sql="SELECT pb_content.*, pb_content_sort.name FROM pb_content_sort,pb_content"}
<a href="[sql:id]">[sql:title] - [sql:name]</a>
{/pboot:sql}

{pboot:sql sql="SELECT ay_content.*, ay_content_sort.<?php echo 'mcode';?> FROM ay_content_sort,ay_content"}
<a href="[sql:id]">[sql:title] [sql:<?php echo 'mcode';?>]</a>
{/pboot:sql}

{pboot:sql sql="SELECT * FROM ay_content WHERE scode IN ('1','3','5') ORDER BY date DESC LIMIT 10"}
<div class="item">
    <h3>[sql:title]</h3>
    <p>[sql:description]</p>
    <a href="[sql:url]">查看详情</a>
</div>
{/pboot:sql}

{pboot:sql sql="SELECT * FROM `products` WHERE FIND_IN_SET('水果', `ext_cplx`)"}
<div class="product">
    <h2>[sql:title]</h2>
    <p>[sql:description]</p>
</div>
{/pboot:sql}

{pboot:sql sql="SELECT 
    (SELECT COUNT(id) FROM ay_content) AS content_total,
    (SELECT COUNT(id) FROM ay_message) AS message_total,
    (SELECT COUNT(id) FROM ay_product) AS product_total"}
<div class="stats">
    <p>内容总数: [sql:content_total]</p>
    <p>留言总数: [sql:message_total]</p>
    <p>产品总数: [sql:product_total]</p>
</div>
{/pboot:sql}

{pboot:sql sql="SELECT scode, COUNT(id) AS count FROM ay_content GROUP BY scode"}
<div>栏目ID: [sql:scode] 有 [sql:count] 篇文章</div>
{/pboot:sql}

{pboot:sql sql="SELECT 
    c.title AS content_title,
    s.name AS sort_name,
    COUNT(m.id) AS message_count
FROM ay_content c
LEFT JOIN ay_content_sort s ON c.scode = s.scode
LEFT JOIN ay_message m ON m.content_id = c.id
GROUP BY c.id"}
<div class="dashboard-item">
    <h3>[sql:content_title]</h3>
    <p>栏目: [sql:sort_name]</p>
    <p>相关留言: [sql:message_count]条</p>
</div>
{/pboot:sql}

{pboot:sql sql="SELECT * FROM ay_content 
WHERE scode IN (SELECT scode FROM user_favorite WHERE user_id = 123) 
ORDER BY visits DESC LIMIT 5"}
<div class="recommend-item">
    <a href="[sql:url]">[sql:title]</a>
    <span>热度: [sql:visits]</span>
</div>
{/pboot:sql}