Sql--convert, for XML path solves real-world problems

Requirements: Store under each platform category, name of each store, picture path, rating, name of four products under each store, picture path, rating

Ideas:

At the beginning of the store dynamic and good writing, with Ajax on the line. But each store below the product, each to request a query a little less realistic.

At the beginning of the plan to use the ID of each store to initiate the request, was stupid to cry.

In a different way of thinking with the SQL statement bar

At the same time, so much data to find out.

is to put these two tables into a table, the data as a row of data in the symbol segmentation, the foreground is taken out.

  withTD as(SELECTHishop_stores.storeid, Hishop_stores.storename, Hishop_products.fir Stracescore, Hishop_products.storeid asID, Hishop_products.productname, HISHOP_PRODUCTS.IMAGEURL1  from(SELECTStoreId, StoreName, Storeimages, Firstracescore fromhishop_stores) asHishop_stores Left JOIN(SELECTStoreId, ProductName, Firstr Acescore, ImageUrl1 fromhishop_productsWHEREProductName is  not NULL                                  )  asHishop_products onHishop_stores.storeid=Hishop_products.storeid)SELECTB.storeid, B.storename, B.firstracescore, B.storeimages, ( SELECT    CONVERT(VARCHAR( -), TD. ProductName)+ '|'                        + CONVERT(VARCHAR( -),ISNULL(TD. Firstracescore,0))                        + '|' + CONVERT(VARCHAR( -),ISNULL(TD. IMAGEURL1,'No'))                        + '='               fromTDWHERETd. StoreId=B.storeidORDER  byTd. FirstracescoreDESC             forXML PATH ("')            )  asProductList fromhishop_stores BwhereShoptypeid=Ten    GROUP  byStoreId, StoreName, Storeimages, B.firstracescore; --ORDER by B.firstracescore DESC;

Results:

Ok

It uses convert to convert the format, for XML PATH (") to implement row-to-column.

Last Ajax

$(function () {       varShoptypeid = GetParam (' Shoptypeid ')); $.ajax ({type:"Post", url: "/api/storeproductajax.ashx", data: {action: ' storelist ', Shoptypeid:shoptypeid}, Async:false, Success:function(data) { for(vari = 0; i < data.length; i++)                 {                     varmsg =Data[i]; varScore =Msg.                     Firstracescore; varName =Msg.                     StoreName; varList =Msg.                                          ProductList; varSTRs =NewArray ();//define an array                     varStrsnew =NewArray ();//define an array                     varStrlist =NewArray (); 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></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; }
。。。。 

I feel a little stupid here, and I don't know how to split the output better.

Sql--convert, for XML path solves real-world problems