Demand: stores under each platform classification, name, picture path and score of each store, name, picture path and score of four products under each store

Train of thought:

At the beginning, stores are dynamic and easy to write, just use Ajax. But it's a bit unrealistic to ask for a query for the products under each store.

At the beginning, I was ready to use the Id of each store to make a request, and I was so stupid that I cried.

Use sql in another way

At the same time, find out so many data.

It is to put these two tables together into a table, divide the data as a row of data with symbols, and take it out at the front desk.

 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, 'nothing'))
                        + '='
              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;

Result:

OK

CONVERT is used to transform the format, and FOR XML PATH('') is used to realize row to column conversion.

Last 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(); //Define an array
                     var strsnew = new Array(); //Define an array
                     var strlist = new Array();
                     strs=list.split("="); //Character segmentation
                      for (ii = 0; ii < strs.length; ii++)
                      {
                          strsnew = strs[ii].split("|"); //Character segmentation
                           for (j = 0; j < strsnew.length; j++)
                            {
                               strlist.push(strsnew[j]);
                              
                            }
                      //document.write(strs[i] + "<br/>"); //Split character output
                     }
                     //document.write( strlist+ "<br/>"); //Split character output
                     //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 + '">Shop around</a></dd></dl><ul class="clearfix border-b" id="product"> </li></ul></div>'));
                              continue;
                         }
. . . . 

I think what I wrote here is a bit stupid. I don't know how to split the output