最近几个月遇到问题汇总及解决方案

一、linq中的几种连接

1，linq中的左连接

from a in resultList
join b in basePriceList on new { a.GroupId, a.FromTime } equals new {  b.GroupId, FromTime = b.FromDate } into ab
from ba in ab.DefaultIfEmpty(new ModelDailyGroupPrice())
select new ModelDailyPriceDetail
{
    CityName = a.CityName,
    BasePrice = Math.Round(ba.Price, 0),
    ...

2，linq中的cross join

连接查询语句：

from c in channelList
from s in storeList
select new ModelDetail
{
    ChannelId = c,
    CityId = s.CityId,
    StoreId = s.Id,
    ...

查询操作符语句：

channelList.SelectMany(c=> storeList.Select(s=> new ModelDetail
{
    ChannelId = c,
    CityId = s.CityId,
    StoreId = s.Id
}) )

3，linq中的group by

var ordinaryOrderCountList = 
    (from r in (from r in ordinaryOrderCount
            join gc in tempGroupIdList on r.CarType equals gc.CarTypeId
            select new
            {
               CheckDate = r.Date,
               ManagerId = r.ManageId,
               CarType = r.CarType,
               OrderCount = r.TotalCount,
               GroupId = gc.GroupId
     })
    group r by new { r.CheckDate, r.ManagerId, r.GroupId } into gr
    let OrderCount = gr.Sum(m => m.OrderCount)
    select new ModelManagerCarGroupRate
    {
          CheckDate = gr.Key.CheckDate,
          ManagerId = gr.Key.ManagerId,
          CarGroupId = gr.Key.GroupId,
          OrderCount = OrderCount,
    }).ToList();

二、查询数据库中重复项

select CarType,count(1) from [ECPriceGroupCarType] A where Flag=1  group by CarType

重复项大于一

三、数组字符串与list之间的转换

1，list转string

var Ids = string.Join(",",list.Select(m=>m.Id));

2,string 转list

//公司封装了转换方法
var list= (from a in Ids.Split(',') select TypeConvert.ObjToInt(a)).ToList();


//.net的String参数StringSplitOptions.RemoveEmptyEntries
List<string> list= Ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(a => a.ToString()).ToList();


List<int> list= Ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(Int32.Parse).ToList();

四、switch 多个case执行程序一样()

 switch(status){
  case '1': x=1.0;    y=1.5;    break;
  case '2':
  case '3':
  case '4': x=3.5;    y=0.5;    break;
  case '5':
  case '6':
  case '7': x=2.6;    y=4.5;    break;
  default:    x=2.0;    y=2.3;
 }

五、js获取页面table中TextBox的值

eg：js获取下图table中所有有色textbox的数值

js代码：

$('#future').find('tbody tr').each(function () {
        model.push({
            WeekendDate: $(this).find('[data="week"]').text(),
            CityId: cityId,
            AbbrTypeID: abbrType,
            TargetCarCount: $(this).find('[data="carCount"]').val(),
            ThisYearDeRaisePre: $(this).find('[data="preDEraise"]').val(),
            TargetDEPercent: $(this).find('[data="targetDEper"]').val(),
            Actions: $(this).find('[data="actions"]').val(),

        })
});

页面中需要配合的地方：

<script type="text/html" id="tempFuture">
   {{each futureList as data index}}
       <tr class="project">
          <td data="week">{{data.strWeekendDate}}</td>
          <td>{{data.ThisYearDE}}</td>
          <td>{{data.LastYearDE}}</td>
          <td>{{data.CurrentARES}}%</td>
          <td><input data="preDEraise" class="easyui-textbox" name="preDEraise" style="width:65px;background-color:antiquewhite" value="{{data.ThisYearDeRaisePre}}" /></td>
          <td>{{data.NeedDEAdv}}</td>
          <td><input data="carCount" class="easyui-textbox" name="carCount" style="width:65px;background-color:antiquewhite" value="{{data.TargetCarCount}}" /></td>
          <td>{{data.LastYearFleet}}</td>
          <td>{{data.TargetCarCountRaisePer}}%</td>
          <td>{{data.LastYearDEpercent}}%</td>
          <td><input data="targetDEper" class="easyui-textbox" name="targetDEper" style="width:65px;background-color:antiquewhite" value="{{data.TargetDEPercent}}" /></td>
          <td>{{data.ActuelDropDEPer}}%</td>
          <td>{{data.ActuelRefuseOrder}}</td>
          <td><input data="actions" class="easyui-textbox" name="actions" style="width:65px;background-color:antiquewhite" value="{{data.Actions}}" /></td>
       </tr>
   {{/each}}
</script>

六、一个表中同一个地区有多条时间不同的数据,取最新一条数据

SELECT *
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY fuel_city ORDER BY from_date DESC) rowIndex
    FROM dbo.ehai_fuel_surcharge
) a
WHERE a.rowIndex = 1;