sql--CONVERT、FOR XML PATH解決實際問題

原文: sql--CONVERT、FOR XML PATH解決實際問題 需求：每個平台分類下的門店，每個門店的名稱、圖檔路徑、評分，每個門店下的四個産品的名稱、圖檔路徑、評分

思路：

一開始門店動态化好寫，用Ajax就行了。但是每個門店下面的産品，每個去請求一次查詢有點不太現實。

一開始準備用每個門店的Id去發起請求，被自己蠢哭了。

在換個思路用sql語句吧

同時把這麼多資料一把查出來。

 就是把這兩張表拼成一張表，把資料作為一行資料在用符号分割，前台在取出來。

WITH    td
          AS ( SELECT   Hishop_Stores.StoreId ,
                        Hishop_Stores.StoreName ,
                        Hishop_Products.FirstraceScore ,
                        Hishop_Products.StoreId AS id ,
                        Hishop_Products.ProductName ,
                        Hishop_Products.ImageUrl1
               FROM     ( SELECT    StoreId ,
                                    StoreName ,
                                                                        StoreImages,
                                    FirstraceScore
                          FROM      Hishop_Stores
                        ) AS Hishop_Stores
                        LEFT JOIN ( SELECT  StoreId ,
                                            ProductName ,
                                            FirstraceScore ,
                                            ImageUrl1
                                    FROM    Hishop_Products
                                    WHERE   ProductName IS NOT NULL
                                  ) AS Hishop_Products ON Hishop_Stores.StoreId = Hishop_Products.StoreId
             )
    SELECT  B.StoreId ,
            B.StoreName ,
            B.FirstraceScore ,
                        B.StoreImages,
            ( SELECT    CONVERT(VARCHAR(100), td.ProductName) + '|'
                        + CONVERT(VARCHAR(100), ISNULL(td.FirstraceScore, 0))
                        + '|' + CONVERT(VARCHAR(100), ISNULL(td.ImageUrl1, '無'))
                        + '='
              FROM      td
              WHERE     td.StoreId = B.StoreId
              ORDER BY  td.FirstraceScore DESC
            FOR
              XML PATH('')
            ) AS ProductList
    FROM    Hishop_Stores B
        where ShopTypeId=10
    GROUP BY StoreId ,
            StoreName ,
                        StoreImages,
            B.FirstraceScore;
    --ORDER BY B.FirstraceScore DESC;      

結果：

OK

其中用到了CONVERT來轉換格式，FOR XML PATH('')來實作行轉列。

最後Ajax

$(function () {
       var shopTypeId = getParam('shopTypeId');
        $.ajax({
            type: "post", url: "/API/StoreProductAJAX.ashx", data: { action: 'storelist',shopTypeId:shopTypeId },
              async: false,
            success: function (data)
            {
                           
                 for (var i = 0; i < data.length; i++)
                 {
                     var msg = data[i];
                     var score = msg.FirstraceScore;
                     var name = msg.StoreName;
                     var list = msg.ProductList;
                     
                     var strs = new Array(); //定義一數組
                     var strsnew = new Array(); //定義一數組
                     var strlist = new Array();
                     strs=list.split("="); //字元分割
                      for (ii = 0; ii < strs.length; ii++)
                      {
                          strsnew = strs[ii].split("|"); //字元分割
                           for (j = 0; j < strsnew.length; j++)
                            {
                               strlist.push(strsnew[j]);
                              
                            }
                      //document.write(strs[i] + "<br/>"); //分割後的字元輸出
                     }
                     //document.write( strlist+ "<br/>"); //分割後的字元輸出
                     //alert(strsnew);
                     //if (name == "")
                     //{
                     //    continue;
                     //}

                     if (score == 0)
                     {
                         if (strlist[0] == "")
                         {
                             $("#store").append($(' <div class="list-lump border-t" id="' + msg.StoreId + '"><dl class="border-b"><dt><em><img src="'+msg.StoreImages+'" onerror="this.src=\'/Storage/master/depot/img-1.jpg\'"></em></dt><dd><span class="list-n1">' + msg.StoreName + '</span><a href="/vShop/StoreProductList?storeId=' + msg.StoreId + '">進店逛逛</a></dd></dl><ul class="clearfix border-b" id="product"> </li></ul></div>'));
                              continue;
                         }
。。。。      

這邊感覺自己寫的有點蠢  不知道怎麼去分割輸出比較好