sql to linq求助
我已经写好一半多了，就是那个跟哪个连接我晕啊linq：
var leftjoin = from p in (CATOS.LOG_SHIP_MOVE.Include("TB_BERTHPLANS").Include("TB_VSLS").Where(t => PTNRs.Contains(t.PTNR_CODE) && (t.BMOVE_TYPE == "DE" || t.BMOVE_TYPE == "ES")))
group p by p.SHIP_LOG_NO into t
join A in CATOS.TB_BERTHPLANS on A.VSL_CD equals CATOS.TB_VSL.VSL_CD into B
join B in CATOS.TB_BERTHPLANS on (B.VSL_CD + B.CALL_YEAR + B.CALL_SEQ) equals t.SHIP_LOG_NO
这两句还是有问题，不知道哪跟哪，晕啊
select new
{
SHIP_LOG_NO=t.Select(m=>m.SHIP_LOG_NO),
DE_F = t.Sum(m=>m.BMOVE_TYPE + m.FE == "DEF" ? 1:0),
DE_E = t.Sum(m=>m.BMOVE_TYPE + m.FE == "DEF" ? 1:0),
ES_F = t.Sum(n=>n.BMOVE_TYPE + n.FE == "ESF" ? 1:0),
DS_E = t.Sum(n => n.BMOVE_TYPE + n.FE == "ESF" ? 1:0),
RESTOW = t.Sum(m => m.BMOVE_TYPE == "TB" ? 1 : (m.BMOVE_TYPE == "BT" ? 1 : (m.BMOVE_TYPE == "BB" ? 1 : 0)))
};
原来的SQL：
SELECT B.VSL_NM,
NVL(B.IN_VOY, 'NULL') || '/' || NVL(B.OUT_VOY, 'NULL') AS VESSEL_VOYAGE,
B.IN_LANE || '/' || B.OUT_LANE AS SERVICE_LANE,
B.ATD,
T.*
FROM (select T.SHIP_LOG_NO,
SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'DEF' THEN 1 ELSE 0 END) AS DE_F,
SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'DEE' THEN 1 ELSE 0 END) AS DE_E,
SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'ESF' THEN 1 ELSE 0 END) AS ES_F,
SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'ESE' THEN 1 ELSE 0 END) AS ES_E,
SUM(CASE T.BMOVE_TYPE WHEN 'TB' THEN 1 WHEN 'BT' THEN 1WHEN 'BB' THEN 1 ELSE 0 END) AS RESTOW
from pcatos.log_ship_move t
where T.PTNR_CODE IN ('MSC')
AND (T.BMOVE_TYPE = 'DE' OR T.BMOVE_TYPE = 'ES')
GROUP BY T.SHIP_LOG_NO) T
INNER JOIN (SELECT A.VSL_CD,
A.CALL_YEAR,
A.CALL_SEQ,
A.ATD,
A.IN_VOY,
A.OUT_VOY,
A.IN_LANE,
A.OUT_LANE,
VV.VSL_NM
FROM PCATOS.TB_BERTHPLAN A
LEFT JOIN PCATOS.TB_VSL VV ON A.VSL_CD = VV.VSL_CD) B ON B.VSL_CD ||
B.CALL_YEAR ||
B.CALL_SEQ =
T.SHIP_LOG_NO
WHERE B.ATD BETWEEN
TO_DATE('2012-09-20 00:00:01', 'YYYY-MM-DD HH24:MI;SS') AND
TO_DATE('2012-09-21 23:59:59', 'YYYY-MM-DD HH24:MI;SS')
LINQ --------------------编程问答-------------------- 这边 t和ajoin之后把结果集保存到了 临时表b中 下面接着怎么能又 给一张同样的临时表b？
还有你对应的集合和下面的sql语句的表貌似有区别 下面new的匿名对象参数赋值给遇对应的变量
--------------------编程问答-------------------- 今天写的还是有点问题：
var leftjoin = from p in (CATOS.LOG_SHIP_MOVE.Include("TB_BERTHPLANS").Include("TB_VSLS").Where(t => PTNRs.Contains(t.PTNR_CODE) && (t.BMOVE_TYPE == "DE" || t.BMOVE_TYPE == "ES")))
group p by p.SHIP_LOG_NO into t
join A in CATOS.TB_BERTHPLANS
on (A.VSL_CD + A.CALL_YEAR + A.CALL_SEQ) equals t.SHIP_LOG_NO
join B in PCATOS.TB_VSL on A.VSL_CD equals CATOS.B.VSL_CD into f
这两句还是有问题，不知道哪跟哪，晕啊
select new
{
SHIP_LOG_NO=t.Select(m=>m.SHIP_LOG_NO),
DE_F = t.Sum(m=>m.BMOVE_TYPE + m.FE == "DEF" ? 1:0),
DE_E = t.Sum(m=>m.BMOVE_TYPE + m.FE == "DEF" ? 1:0),
ES_F = t.Sum(n=>n.BMOVE_TYPE + n.FE == "ESF" ? 1:0),
DS_E = t.Sum(n => n.BMOVE_TYPE + n.FE == "ESF" ? 1:0),
RESTOW = t.Sum(m => m.BMOVE_TYPE == "TB" ? 1 : (m.BMOVE_TYPE == "BT" ? 1 : (m.BMOVE_TYPE == "BB" ? 1 : 0)))
};
var leftjoin = from p in (CATOS.LOG_SHIP_MOVE.Include("TB_VSLS").Where(t => PTNRs.Contains(t.PTNR_CODE) && (t.BMOVE_TYPE == "DE" || t.BMOVE_TYPE == "ES")))
group p by p.SHIP_LOG_NO into t
from A in CATOS.TB_BERTHPLANS
join vv in CATOS.TB_VSLS on A.VSL_CD equals vv.VSL_CD into B
//??join B on k.SHIP_LOG_NO equals (B.VSL_CD + B.CALL_YEAR + B.CALL_SEQ) into temp
//from tt in t.DefaultIfEmpty()
from rr in B.DefaultIfEmpty()
//from test in temp.DefaultIfEmpty()
select new
{
SHIP_LOG_NO = t.Select(m => m.SHIP_LOG_NO),
DE_F = t.Sum(m => m.BMOVE_TYPE + m.FE == "DEF" ? 1 : 0),
DE_E = t.Sum(m => m.BMOVE_TYPE + m.FE == "DEF" ? 1 : 0),
ES_F = t.Sum(n => n.BMOVE_TYPE + n.FE == "ESF" ? 1 : 0),
ES_E = t.Sum(n => n.BMOVE_TYPE + n.FE == "ESF" ? 1 : 0),
RESTOW = t.Sum(m => m.BMOVE_TYPE == "TB" ? 1 : (m.BMOVE_TYPE == "BT" ? 1 : (m.BMOVE_TYPE == "BB" ? 1 : 0)))
};
--------------------编程问答-------------------- 给大家做参考，写出来了：
var sqljoin = from k in
(from t in (CATOS.LOG_SHIP_MOVE.Where(t => PTNRs.Contains(t.PTNR_CODE) && (t.BMOVE_TYPE == "DE" || t.BMOVE_TYPE == "ES")))
group t by t.SHIP_LOG_NO into tt
select new
{
//SHIP_LOG_NO = tt.Select(m => m.SHIP_LOG_NO),
SHIP_LOG_NO=tt.Key,
DE_F = tt.Sum(m => m.BMOVE_TYPE + m.FE == "DEF" ? 1 : 0),
DE_E = tt.Sum(m => m.BMOVE_TYPE + m.FE == "DEF" ? 1 : 0),
ES_F = tt.Sum(n => n.BMOVE_TYPE + n.FE == "ESF" ? 1 : 0),
ES_E = tt.Sum(n => n.BMOVE_TYPE + n.FE == "ESF" ? 1 : 0),
RESTOW = tt.Sum(m => m.BMOVE_TYPE == "TB" ? 1 : (m.BMOVE_TYPE == "BT" ? 1 : (m.BMOVE_TYPE == "BB" ? 1 : 0)))
})
join f in ( from A in CATOS.TB_BERTHPLANS.Include("TB_VSL")
//join vv in CATOS.TB_VSLS on A.VSL_CD equals vv.VSL_CD into B
//from bb in B.DefaultIfEmpty()
select new
{
yy=A.VSL_CD+A.CALL_YEAR+A.CALL_SEQ,
A.VSL_CD,
A.CALL_YEAR,
A.CALL_SEQ,
A.ATD,
A.IN_VOY,
A.OUT_VOY,
A.IN_LANE,
A.OUT_LANE,
// bb.VSL_NM
A.TB_VSL.VSL_NM
}) on k.SHIP_LOG_NO equals f.yy
//from ss in temp2.DefaultIfEmpty().Where(q => (q.ATD >= startDate && q.ATD <= endDate))
where f.ATD >= startDate && f.ATD <= endDate
select new
{
f.VSL_NM,
VESSEL_VOYAGE=f.IN_VOY+"/"+f.OUT_VOY,
SERVICE_LANE=f.IN_LANE+"/"+f.OUT_LANE,
f.ATD,
//bb.ATD,
k.SHIP_LOG_NO,
k.DE_F,
k.DE_E,
k.ES_F,
k.ES_E,
k.RESTOW
// f.IN_LANE
};
return new AjaxStoreResult(sqljoin);
--------------------编程问答-------------------- 原来的SQL:
SELECT B.VSL_NM,
NVL(B.IN_VOY, 'NULL') || '/' || NVL(B.OUT_VOY, 'NULL') AS VESSEL_VOYAGE,
B.IN_LANE || '/' || B.OUT_LANE AS SERVICE_LANE,
B.ATD,
T.*
FROM (select T.SHIP_LOG_NO,
SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'DEF' THEN 1 ELSE 0 END) AS DE_F,
SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'DEE' THEN 1 ELSE 0 END) AS DE_E,
SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'ESF' THEN 1 ELSE 0 END) AS ES_F,
SUM(CASE T.BMOVE_TYPE || T.FE WHEN 'ESE' THEN 1 ELSE 0 END) AS ES_E,
SUM(CASE T.BMOVE_TYPE WHEN 'TB' THEN 1 WHEN 'BT' THEN 1WHEN 'BB' THEN 1 ELSE 0 END) AS RESTOW
from pcatos.log_ship_move t
where T.PTNR_CODE IN ('MSC')
AND (T.BMOVE_TYPE = 'DE' OR T.BMOVE_TYPE = 'ES')
GROUP BY T.SHIP_LOG_NO) T
INNER JOIN (SELECT A.VSL_CD,
A.CALL_YEAR,
A.CALL_SEQ,
A.ATD,
A.IN_VOY,
A.OUT_VOY,
A.IN_LANE,
A.OUT_LANE,
VV.VSL_NM
FROM PCATOS.TB_BERTHPLAN A
LEFT JOIN PCATOS.TB_VSL VV ON A.VSL_CD = VV.VSL_CD) B ON B.VSL_CD ||
B.CALL_YEAR ||
B.CALL_SEQ =
T.SHIP_LOG_NO
WHERE B.ATD BETWEEN
TO_DATE('2012-09-20 00:00:01', 'YYYY-MM-DD HH24:MI;SS') AND
TO_DATE('2012-09-21 23:59:59', 'YYYY-MM-DD HH24:MI;SS')
--------------------编程问答-------------------- 额，楼主牛人，晕了 --------------------编程问答--------------------
你自己弄出来了 我来混分 --------------------编程问答-------------------- 呵呵· 竟然晕了， 一步一步慢慢来，详细分化 --------------------编程问答-------------------- 大家慢慢写吧，其实我也是一点都不会，被逼写出来的，大家相互勉励
补充：.NET技术 , LINQ
